Querying Remote Data Sources

So far, the samples in this chapter have referenced local data sources or data in another Access file. Access 2000 makes it easy to work with remote data sources as well, such as Microsoft SQL Server or Oracle. This section will briefly explore this topic. You'll find a more in-depth treatment in Chapter 12.

Querying Linked ODBC Sources

You can query a linked ODBC source just as you query a linked Access data source from another file. Figure 4-27 shows the Database window with three linked tables from the Pubs database that ships with SQL Server. Follow these steps to link to a table in a remote database:

1. Choose Get External Data from the Access File menu and then click Link Tables.
2. In the Link dialog box, select ODBC Databases from the Files Of Type drop-down list.
3. Select a DSN from the Machine Data Source. (You can create a new DSN if the one you need is not available.)
4. Complete the link by selecting a table and clicking OK.

Since nearly every remote data source supports ODBC, this way of working with remote data provides universal access with a familiar Access front end.

Figure 4-27. This Database window shows three linked tables—dbo_authors, dbo_titleauthor, and dbo_titles. You can use ODBC drivers to link to tables from any data source with an ODBC driver.

Figure 4-28 shows, in its top window, the three linked tables from Figure 427 in an Access 2000 query Design view. After you link to a remote table, you can treat it as if it were a local table. Because tables in remote databases can be very large, some special considerations apply when you design queries for them. The first section in this chapter highlights some techniques to consider. The size of the tables and the type of physical connection to the remote data source will help determine the best query designs. The bottom window in Figure 4-28 shows the query from the top window in Datasheet view.

Figure 4-28. A simple query that relies on three tables from a remote data source. Notice in the top window that you can build queries to linked remote tables as if they were local tables. The bottom window shows the query in Datasheet view.

Querying in Access Data Projects

Access 2000 introduces a new project type called the Access Project, which bypasses the Jet database engine as it links directly to SQL Server 6.5 or 7.0 and the Microsoft Data Engine. The Microsoft Data Engine is the new version of SQL Server for the Windows 9x environment. In this environment, you can form a direct link to a remote database and work with it as if it were a local database.

The top window in Figure 4-29 shows the Database window of an Access Project. Notice that it looks like the Database window of an .mdb database, but its data source is the Pubs database. (You can change the database to which the Database window links by choosing Connection from the File menu.)

The bottom window in Figure 4-29 is the Data Link Properties dialog box for the project. The server for this project is cab22000, the name of a connected SQL Server 7.0 database server. To establish a working connection to a server, you must specify the logon information. This example uses Microsoft Windows NT integrated security. After the connection is established, you can open any database on the server that your logon status permits. Click Test Connection to verify the validity of the link to a specific database.

Figure 4-29. The top window is the Database window for an Access Project; the bottom window is the project's Data Link Properties dialog box.

Figure 4-30 shows the Design view of a query in an Access Project. Its query design grid resembles the one for an .mdb database, but it is part of the Microsoft Da Vinci visual database design tools. You can right-click to add tables or other queries as record sources for a new query design, and you can drag and drop fields between tables to build relationships. While there is no explicit SQL view, you can toggle the visibility of a SQL pane in Design view using a button on the Database toolbar. The first button on this toolbar still lets you toggle back and forth between the Design view and Datasheet view. The query design grid orientation changes from vertical to horizontal, but the layout is similar. You build views by entering constants and expressions in the criteria cells to the right of the field names in the design grid.

Figure 4-30. The query Design view for an Access Project. Notice that it displays the SQL for a query in a separate pane rather than in a separate view.

Programmatic Querying of Remote Databases

Many developers prefer to work directly with a programmatic interface rather than a graphical user interface. Access 2000 offers tighter compatibility between queries for local and remote data sources at the programmatic level than at the graphical user interface level because you can use ADO and SQL for both local and remote data sources. The biggest difference between local and remote data access is that for remote data access you must specify the OLE DB parameters for the OLE DB provider with the data that you want to query. You might also encounter SQL dialect issues when you migrate your applications from one remote data source to another because each remote database engine can require slightly different logon parameters and can use unique SQL extensions to accommodate its special features.

To connect to a Microsoft SQL Server data source, you use a special OLE DB driver called SQLOLEDB, which is optimized for Microsoft SQL Server, instead of the generic ODBC driver, MSDASQL, which is for working with ODBC databases. The MSDASQL driver predates SQLOLEDB, but it remains a good general-purpose OLE DB driver for situations in which there might not be a specific OLE DB driver. The parameters for these two drivers are slightly different. (Chapter 3 shows how to set the parameters for MSDASQL.)

The following sample shows how straightforward it is to programmatically query a remote SQL Server 7 data source. Notice that the procedure uses the SQLOLEDB driver. The query is against the Pubs database on the cab2200 server. After opening the connection based on the SQLOLEDB driver and its parameter settings, the procedure creates a Command object and sets its CommandText and CommandType properties. Then it executes the command, assigns the return set to a Recordset object, and prints the return set.

 Sub SQLOleDBQuery() Dim cnn1 As ADODB.Connection Dim cmd1 As ADODB.Command Dim rst1 As ADODB.Recordset 'Establish a connection to the database. 'Specify the server (cab2200) as the data source and 'catalog as the database.     Set cnn1 = New ADODB.Connection     With cnn1         .Provider = "sqloledb"         .ConnectionString = "data source=cab2200;" & _             "user id = sa;initial catalog=pubs"         .Open     End With 'Set up a command object.     Set cmd1 = New ADODB.Command     With cmd1         .ActiveConnection = cnn1         .CommandText = "SELECT titles.title, " & _             "authors.au_lname, authors.au_fname " & _             "FROM titles INNER JOIN (authors INNER JOIN " & _             "titleauthor ON authors.au_id = " & _             "titleauthor.au_id) ON " & _             "titles.title_id = titleauthor.title_id"         .CommandType = adCmdText     End With 'Open and print a recordset based on the executed 'query statement.     Set rst1 = cmd1.Execute     Debug.Print rst1.GetString End Sub 

This query yields results identical to those of the queries in Figures 4-28 and 4-30. I took the SQL in this sample directly from the SQL view of the query in Figure 4-28. Notice that it is similar but not identical in arrangement to the SQL in Figure 4-30 for the Access Project. However, you can substitute the SQL from Figure 4-30 into the above procedure without changing the return set. This confirms that the .mdb programmatic approach and the Access Project query approach can yield equivalent results despite some stylistic differences in the SQL representations for selected cases.