Linking Remote Tables

There are two basic ways to work with data stored on a server when using a Microsoft Jet Workspace object. You can:

• Open the database directly using the OpenDatabase method, specifying a connection string to identify the server, database, and connection information.
  
  
• Create a permanent link to tables through a Microsoft Jet database file.

Although opening the database directly sounds faster than linking, that is not the case. When you access data through the OpenDatabase method, every time the table is accessed, Microsoft Jet must retrieve information about tables, such as field names and data types. When you link a table, the engine stores this information permanently in the local Microsoft Jet database. This effectively caches the information, making it quickly accessible each time the table needs to be opened. In effect, you have eliminated a round trip to the server.

When you link the tables to your application’s Microsoft Jet database, the tables appear as if they were native Microsoft Jet tables in your application’s database. Besides convenience, one of the main advantages to linking is that Microsoft Jet caches information about the remote table in the stored link. Each time you open the linked table, information about its location, indexes, and other attributes are quickly accessible to Microsoft Jet, and opening is therefore faster.

Microsoft Jet also supports linking to SQL views on the remote data source, but with some restrictions, as discussed in “Updating Tables with No Unique Index” later in this chapter.

The BookSales and BookSalesOptimized sample applications compute the time it takes to perform various operations against the Pubs SQL Server database. The BookSales application opens the SQL Server database directly each time, using the following code:

' Note that you must alter the ODBC connection string before using
' this code with your application, so that it refers to the Pubs database
' on your SQL Server.

Set dbs = OpenDatabase("", False, False, _
	"ODBC;DSN=Pubs;UID=sa;PWD=;DATABASE=Pubs")

The BookSalesOptimized application opens a local Microsoft Jet database that contains tables linked to the Pubs SQL Server database. This database is Pubs.mdb, located in the JetBook\Samples\BookSalesOptimized subfolder:

' Note that you must re-link tables in Pubs.mdb before running the 
' application so that they point to the Pubs database on your SQL server. 

Set dbs = OpenDatabase("C:\JetBook\Samples\Pubs.mdb")

Linking tables may provide a dramatic performance improvement. Also, small recordsets, such as those in the BookSales and BookSalesOptimized sample applications, perform better than larger recordsets because the speed improvement you see from linked tables only affects the initial opening of the recordset. Note that small recordsets are typical of a well-written client/server application.

Note You can’t use linked tables from an ODBCDirect Workspace object, but ODBCDirect provides other performance advantages. For information on how to use ODBCDirect features, see “The Object Model for ODBCDirect Workspaces” later in this chapter.