Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources]

Opening an External Table

There are times when you will want to openrather than link toan external table. Linking provides ease of use when dealing with external tables. After you link to a table, you treat it just like any other Access table. The disadvantage of linking is that it uses ODBC. ODBC is not the most efficient means of interacting with a database for which you have a native OLE DB provider. Therefore, you might want to programmatically open an external table without creating a link to it. Opening an external table is a two-step process:

Establish a connection to the external data source.

Point a Recordset object at the result of executing a SQL statement against the Connection object.

Providing Connection Information

The connection information you provide when you open an external table is similar to the information you provide when you link to the table. The connection information is provided as the ConnectionString argument of the Open method of the Connection object. Here's an example:

cnn.Open "Providersqlodedb;" & _ "Data Source=(local);" & _ _ "Initial Catalog=Pubs;" & _ "User ID=sa;Password=;"

Here, the connection string is to the SQL Server database called Pubs on the local machine.

Opening the Table

You point the Recordset object at the result of executing a Select statement against the Connection object:

Set rst = cnn.EXEcute("Select * from Authors")

Listing 19.7 shows what the entire process looks like in code.

Listing 19.7 Using the OpenDatabase Method

Sub OpenExternalSQL(strDBName As String, strTableName As String) Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset 'Instantiate Connection and Recordset objects Set cnn = New ADODB.Connection Set rst = New ADODB.Recordset 'Use the Open method of the Connection object to establish 'a connection to the SQL Server database cnn.Open "Provider=sqloledb;" & _ "Data Source=(local);" & _ "Initial Catalog=" & strDBName & ";" & _ "User Id=sa;Password=; " 'Use the Execute method of the Connection object to execute 'a Select statement and return the result as a Recordset Set rst = cnn.Execute("Select * from " & strTableName) 'Loop through the resulting recordset, 'printing the value of the first field Do Until rst.EOF Debug.Print rst.Fields(0).Value rst.MoveNext Loop 'Close the Connection cnn.Close End Sub

Listing 19.7 is called with this code:

Call OpenExternalSQL("Pubs","authors")

Notice that you are not appending a table definition here. Instead, you are creating a temporary recordset that refers to the external data. After you open the external table as a recordset, the code traverses through each record of the table, printing the value of the first field. Of course, after you open the recordset, you can manipulate it in any way you want. The table does not show up as a linked table in the Database window. In fact, when the routine completes and the local variable goes out of scope, the recordset no longer exists.

Now that you have seen how you can link to external tables as well as open them, you are ready to take a look at how you can refine both of these processes. This involves learning the Windows registry settings that affect the linking process, learning more about the parameters that are available to you when specifying connection information, learning how to specify passwords, learning how to refresh and remove links, and learning how to create an external table using VBA code.