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:
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.
Create a new form. Type the following statement into the RecordSource property of the form:
SELECT * FROM pubs.dbo.authors
You will then see the Field List for the
authors
table in thepubs
database. Figure 14-18 displays the Field List from the sample form, frmPubsAuthorsSQL, and shows that the form is now bound to data in thepubs
database, not the Northwind database.Figure 14-18. The Field List from frmPubsAuthorsSQL
Alternately, you can create a view in the Northwind database that selects data from the
pubs.authors
table:CREATE VIEW ...
Get Access Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.