Distributed Queries in MS Access - Creating Linked Servers
(Page 2 of 4 )
Linked SQL Server
Create a MS Access database herein called DistributedQ.mdb. This database does not have any objects of its own. To this database import data by means of linking to tables resident in the Northwind database on the SQL 2000 Server. Follow the procedure described in the previous tutorial. Although the previous tutorial considered bringing in data from an MSDE desktop SQL Server, the process is exactly the same.
You begin by going to File-->Get External Data -->Link Tables.... This opens up a dialog window with the title LINK and you can set up the type of item you want to import. Since MS Access uses ODBC to import data from a SQL Server, go to the bottom of the Files of Type drop-down list and click on ODBC Databases(). This will take you to Select Data Source Window (this same window can be accessed by going to Start -->Control Panel-->Administrative Tools-->Data Sources (ODBC)). You follow the procedure, providing all the requirements, and you will finally reach this screen which completes creation of the ODBC Microsoft SQL Server Setup. You may also test the connectivity by clicking the Test Data Source... button.
When you click OK to this screen, a LINK Table dialog shows up with all the tables in the Northwind database. You can pick and choose, as well as multi-select by holding the Control or shift key. When you click out of the LINK window the tables you have chosen should all appear in the Main window of the DistributedQry application, as shown in this picture.
These are just links to the tables in the Northwind database on the SQL Server as shown below. Any changes made to these tables will be reflected in the linked tables. Also when you try access the link tables, you may be required to provide credentials, both authentication and permissions.
You may review the data on the linked tables by just double-clicking them in the Main window of the DistributedQry.mdb as shown for the dbo_Order Details table. For querying you may assume that the tables are on the database file and use the UI in access to query as well as create other database objects such as forms, reports, and data access pages.
Next: Linked MS Access Server >>
More Microsoft Access Articles
More By Jayaram Krishnaswamy
