Access Cookbook

Reference Data from More than One SQL Server Database in an ADP

Problem

You’d like to have your ADP connect to multiple SQL Server databases at one time. However, the Data Link dialog allows room for only one SQL Server database.

Solution

Although at first glance this seems to be a problem, the solution is readily at hand with SQL Server’s three-part naming convention. You are probably already familiar with the OwnerName . ObjectName syntax for referring to SQL Server objects, which is needed when users other than the owner (or creator) of that object wish to use the object. The three-part naming syntax is:

               DatabaseName.OwnerName.ObjectName

To refer to another SQL Server database in your ADP, follow these steps:

1. Create a new project and link it to the Northwind database. You can look at the list of tables and see only the tables from Northwind.

2. Create a new form. Type the following statement into the RecordSource property of the form:

   SELECT * FROM pubs.dbo.authors

3. You will then see the Field List for the authors table in the pubs database. Figure 14-18 displays the Field List from the sample form, frmPubsAuthorsSQL, and shows that the form is now bound to data in the pubs database, not the Northwind database.

   

   Figure 14-18. The Field List from frmPubsAuthorsSQL

4. Alternately, you can create a view in the Northwind database that selects data from the pubs.authors table:

   CREATE VIEW ...