Distributed Queries in MS Access - Creating the Distributed Query
(Page 4 of 4 )
This is no different from querying in the Access database normally. If you use the query wizard to create a query, you will see the familiar interface from where you first select the table name from the drop-down and choose the columns you want as shown. The drop-down shows all the linked tables as well as any queries present. In this screen there is already a query created, which can also be seen.
We will use the query designer, however, to create the query as shown in this picture and save it as DistQry. The Order Details belong to the Northwind.mdb database.
The output from this query is shown here.
SQL View of the Distributed Query
An example of the distributed query constructed using any of the methods is used to display its SQL, and the SQL statement appears as shown here. The ones shown in red are contributed by the MS Access database's linked table and the rest are provided by the SQL 2000 Server.
SELECT dbo_Orders.OrderDate,
dbo_Orders.ShipName, [Order Details].Quantity,
dbo_Products.ProductName
FROM dbo_Products INNER JOIN (dbo_Orders INNER JOIN [Order Details]
ON dbo_Orders.OrderID = [Order Details].OrderID)
ON dbo_Products.ProductID = [Order Details].ProductID;
Linked Table Manager
This is a nice utility in MS Access shown here which can not only show the linked tables and their sources, but can also be used to update the links in the event the files get moved. You may have to browse to discover the location to which it was moved.
Summary
Microsoft Access with its support for linking to varied databases is very convenient for testing distributed queries. The example chosen was a simple one, but under realistic conditions when there are relational integrity conditions to be satisfied, especially in data modifying queries, great care is necessary. Although distributed queries are good for limited rows returned by the query, if the data transported is large then there could be resource related problems and network bottlenecks. A large number of important issues like security, transactions, and so on were not discussed, but they are outside the scope of this elementary tutorial.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
