Distributed Queries in MS Access
(Page 1 of 4 )
Many businesses store their data in a variety of databases, and these are not always of the same type. If you ever have to query for data in several different database tables, this article can help. It shows a simple exercise in querying tables in heterogeneous database servers.
Introduction
Large organizations store their data in several different databases, and more often they resort to storing on heterogeneous data servers by choice, as it suits their business practice. However they also have a need to generate reports based on a combination of data coming from these varied sources. Sometimes the data of the enterprise is stored in a fragmented fashion at different locations on the same or different types of data base servers, by vertical or horizontal partitioning, and here again there arises a need to aggregate data from more than one source for generating a report. Sometimes security concerns dictate storing sensitive information on more secure data servers, and the less important ones on servers which generally lack the security features of the higher end products. In these different scenarios resorting to distributed queries becomes a necessity.
Distributed queries query more than a single database in the same server, or query databases on multiple database servers. They are homogeneous distributed queries when the database servers are of the same type (all MS SQL or all Oracle, for example), and are heterogeneous when they access several different kinds of database servers (MS SQL Server, Oracle, IBM Informix). These queries could be Select, Insert, or Update, as well as delete queries. Microsoft SQL Server supports distributed queries with Ole DB as a conduit to external data, whereas you can use ODBC for external data access in MS Access through linked servers.
This tutorial describes an exercise in querying tables in heterogeneous database servers, a MS Access database and an SQL database.
The distributed data
One of MS Access's strong points is its capability to import data originating from a large number of different kinds of data sources, text files, spread sheet files, and so on. It can establish a live link to the tables by creating linked servers. A step-by-step procedure for bringing in external data using the ODBC interface was described in great detail in a previous tutorial; here, only some key screen shots of this process will be shown.
It is assumed that MS Access is installed (this tutorial used the following version: MS Access 2002 (10.2627)-SP1 on Windows XP Professional platform) on the desktop. Also a SQL 2000 (Version 8.0) Database server XPHTEK is also installed on the same machine. It could be on the network as well, but in this case it is on the same computer. Since the data stays on the servers it is further assumed that the servers are up and running.
Next: Creating Linked Servers >>
More Microsoft Access Articles
More By Jayaram Krishnaswamy
