Working with Tables in Another Database

Access lets you open only one database at a time unless you write code to open another table with a VBA function or subprocedure, or specify a linked server. However, you can use Jet SQL's IN predicate with a make-table, append, update, or delete query to create or modify tables in another database. ANSI SQL doesn't support the IN reserved word as a modifier for SELECT...INTO statements.

Jet SQL

Following is a sample SQL statement to create a copy of Northwind.mdb's Customers table in another Jet database:

 SELECT *    INTO Customers    IN 'c:\Databases\Illwind.mdb' FROM Customers 

You receive an error message if the Customers table exists in the target database or if the path or file name is invalid.

The project designer uses SQL Server three-part names to specify tables in another database of the SQL Server instance to which you're connected. Three part names use the Database.Schema.Table format; for most RDBMSs, the Schema element is the database owner name dbo for SQL Server. For example, the three-part name for the Customers table of the NorthwindCS database is NorthwindCS.dbo.Customers.

Note

Three-part names are a shortened version of the four-part names you use to refer to tables in a linked database. The missing fourth element is the ServerName prefix that you would need to add to specify a table in a linked database.

To review linking tables from Jet databases or SQL Server databases on other machines, see "Linking Remote Servers," p. 842.

SQL Server's default database is master, which the setup program creates during the installation process. You can use the master database as a temporary destination for tables you copy by SELECT...INTO statements.

T-SQL

Following is a simple T-SQL statement for a stored procedure that creates a copy of NorthwindCS's Customers table in the master database:

 SELECT dbo.Customers.*    INTO master.dbo.Customers FROM dbo.Customers 

You receive an error message if the Customers table exists in the target database or if you don't have CREATE TABLE permission for the target database. You also receive an error if you don't add the dbo. prefix to both instances of Customers.

After you create the table in the other database, you can create a view, function, or stored procedure with tables in both databases. To create a view between the Customers table in the master database and the Orders table in the NorthwindCS database, do the following:

1. Create a new view or function in the project designer and add the Customers and Orders tables from NorthwindCS.

2. Mark the check boxes for a couple of fields from each table CustomerID, CompanyName, OrderID, and OrderDate for this example.

3. Open the SQL pane and add the prefix master. to each instance of dbo.Customers.

4. Click Check SQL Syntax to verify your T-SQL modifications. The Customers field list adds an arrow and (master) to its title bar, and the join line loses its key and [if] symbols (see Figure 21.12).

   Figure 21.12. JOINs between tables in different databases don't display symbols representing relationships.

   

You lose the benefit of referential integrity enforcement between tables in different databases, because the relationships between tables are defined only within a single database. Views across multiple databases aren't updatable in Datasheet view, but you can write functions or stored procedures to perform INSERT, UPDATE, and DELETE operations. Production stored procedures that update tables in multiple databases require a substantial amount of additional code to protect tables against violation of referential integrity rules.

Note

Transactions across linked SQL Server databases are managed by the Distributed Transaction Coordinator (DTC), which all versions of SQL Server install during the setup process. Operations on multiple databases running on a single SQL Server instance use a single OLE DB connection and don't employ DTC.