Establishing Access to External Data

As mentioned earlier, this chapter discusses two ways to access external data: linking external tables to your Microsoft Access database and opening external tables directly. To link a table, you can use either the Microsoft Access user interface or Visual Basic code; you must use Visual Basic to open tables directly. The procedure for linking a table in code is similar to the procedure for opening a table directly. The following table summarizes the key differences.

When you use Visual Basic to link a table or to open it directly, you use connection strings, which are string expressions that pass the values necessary to establish a connection to an external data source.

See Also   For more information on connection strings, see "Specifying Connection Information" later in this chapter.

Linking External Tables

You can use either the Microsoft Access user interface or Visual Basic code to link external tables.

   To link an external table by using the Microsoft Access user interface

1   Open the Microsoft Access database to which you are linking the table.

2   On the File menu, point to Get External Data, and then click Link Tables.

The Link dialog box appears.

3   In the Files Of Type box, select the file type.

4   Select the drive and folder where the external table is located and then double-click its icon.

Note   You can also modify links to external tables after the links are created. To do so, point to Add-ins on the Tools menu, and click Linked Table Manager.

   To link an external table by using Visual Basic code

1   Open the database that will contain the link. If it is the current database, use the CurrentDb function to return an object variable that represents the current database. If it isn't the current database, use the OpenDatabase method to open the database you want.

2   Using the CreateTableDef method, create a new TableDef object.

3   Set the Connect property of the TableDef object to a connection string that specifies the type of data source you're connecting to and the path to the file that contains the table. If the external data source is protected with security, you can also use the connection string to specify user name and password information required to access the data source.

4   Set the SourceTableName property of the TableDef object to the name of the table whose data you want to access.

5   Append the TableDef object to the TableDefs collection to create a link.

See Also   For more information on the syntax and format of connection strings, see "Specifying Connection Information" later in this chapter, or search the Help index for "Connect property."

The following code links a FoxPro table to a Microsoft Access database. It assumes you want to link the Q1Sales table in a FoxPro version 3.0 database named Region1 on the server and shared folder \\Sales\Regional.

Sub LinkFoxProTable()
   Dim dbs As Database
   Dim tdfSales As TableDef
   Dim rst As Recordset
   ' Open the Microsoft Access database.
   Set dbs = CurrentDb
   ' Create a TableDef object.
   Set tdfSales = dbs.CreateTableDef("Western Region Sales")
   ' Set the connection string to specify the source database type and the
   ' path to the file that contains the table you want to link.
   tdfSales.Connect = "FoxPro 3.0;DATABASE=\\Sales\Regional\Region1"
   ' Set the SourceTableName property to the name of the table you want to access.
   tdfSales.SourceTableName = "Q1Sales"
   ' Append the TableDef object to the TableDefs collection to create a link.
   dbs.TableDefs.Append tdfSales
   ' Create a Recordset object from the linked table.
   Set rst = dbs.OpenRecordset("Western Region Sales")
End Sub

Important   Use a semicolon (;) to separate arguments specified in the Connect property. Don't include any spaces on either side of the semicolon.

Instead of specifying the connection string and source table name by setting properties of the TableDef object, you can specify them by using the connect and source arguments of the CreateTableDef method, as shown in the following example:

Sub LinkFoxProTable2()
   Dim dbs As Database
   Dim tdfSales As TableDef
   ' Open the Microsoft Access database.
   Set dbs = CurrentDb
   ' Specify the connection string and table name as arguments of the
   ' CreateTableDef method.
   Set tdfSales = dbs.CreateTableDef("Western Region Sales", 0, "Q1Sales", _
      "FoxPro 3.0;DATABASE=\\Sales\Regional\Region1;")
   ' Append the TableDef object to the TableDefs collection to create a link.
   dbs.TableDefs.Append tdfSales
End Sub

Maintaining Links to External Tables

Links to external tables are convenient because they store all of the connection information you need to access the table in the future. Connection information for linked tables resides in the TableDef object for that table in your Microsoft Access database. If you move or delete the external table, you must update the corresponding TableDef object to reflect this change.

Updating Links to External Tables That Have Moved

If you move a table that you previously linked, you need to refresh the link before you can access the table again. To refresh a link, you must reset the Connect property of the TableDef object to point to the table's new location. Then, use the RefreshLink method of the TableDef object to update the link information.

To see an example of how this works, open the Developer Solutions sample application in the Samples subfolder of your Office folder. In the upper part of the Developer Solutions form, double-click Use Multiple Databases. In the lower part, double-click Link Tables At Startup.

Deleting Links to External Tables

You can also delete the TableDef object associated with an external table. This removes the table's icon from your Microsoft Access database along with the associated linking information. When you delete a TableDef that defines a linked table from your Microsoft Access database, the external table is unaffected.

Opening External Tables

In contrast to linking external tables, when you open an external table directly, you must supply the connection information at the beginning of each session to establish a connection to the data source. None of the information needed to establish a connection to the external data source is stored in your Microsoft Access database.

When you open a table directly, you specify the source and connection information as arguments of the OpenDatabase method instead of setting them separately as properties of a TableDef object. The following code uses the FoxPro table from the preceding examples to show how to open a table directly:

Sub OpenFoxProTable()
   Dim dbs As Database
   Dim rstSales As Recordset
   ' Open an external FoxPro database named Region1.
   Set dbs = OpenDatabase("\\Sales\Regional\Region1", False, False, "FoxPro 3.0;")
   ' Create a Recordset from the Q1Sales table.
   Set rstSales = dbs.OpenRecordset("Q1Sales")
End Sub

Note   Use the dbname argument of the OpenDatabase method to specify the path and file name, and use the source argument of the OpenRecordset method to specify the table name. For external data sources that store one table per file, the dbname argument should contain the full path to the file, and the source argument should contain the table name. For Paradox databases, the table name should not have an extension. However, you can include an extension for dBASE and FoxPro databases. For external data sources that store multiple tables in a file, the dbname argument should contain the full path to the file, including the file name and extension; the source argument should contain the table name.

By default, Microsoft Jet determines which types of Recordset objects are available for the data source you are accessing and creates the type that will result in the best performance. If you open the data source directly, Microsoft Jet opens a table-type Recordset object. If you want more control over the type of Recordset object you open, you can specify a type argument for the OpenRecordset method.

Important   You cannot specify that Microsoft Jet open a table-type Recordset object for ODBC databases and linked installable ISAM tables.

After you've opened the table, you can manipulate the Recordset object you created just as you would any other Recordset object in your Microsoft Access database. Keep in mind, however, that you must reestablish your connection to the external data source and re-create the Recordset object for each session.

See Also   For more information on working with Recordset objects, see Chapter 9, "Working with Records and Fields," or search the Help index for "Recordsets."

Specifying Connection Information

Earlier in this chapter, several examples of connection strings showed how to specify connection information. The following sections contain additional information on connection strings, their syntax, and their format.

Specifying Paths and File Names

When you specify a file name for a database, spreadsheet, text file, or HTML file, you must include the fully qualified path to the file. On a local drive, the path must include the drive letter, all folders and subfolders, and the file name. For example, to specify the FoxPro database Region1 located in the \FoxPro3 folder on the local drive C, use the following path:

C:\FoxPro3\Region1

On a network drive, the path must include the server name, share name, all folders and subfolders, and the file name. For example, to specify the FoxPro database Region1 on the Regional share on the Sales server, use the following path:

\\Sales\Regional\Region1

You can also map a network path to a network drive and indicate the path as the drive letter followed by the file name. For example, to specify the FoxPro database Region1 on the Regional share on the Sales server where the network path \\Sales\Regional has been mapped to the network drive G, use the following path:

G:\Region1

To specify the FoxPro database Region1 on the Regional share on the File Transfer Protocol (FTP) server named Sales, use the following path:

ftp://Sales/Regional/Region1

Note   ODBC data sources use a different specifier, called a data source name (DSN), to indicate the path and file name. For all ODBC data sources, use a zero-length string (" ") as the path and file name. For more information on the ODBC DSN, see the following section, "Specifying Data Source Names for ODBC Data Sources."

Specifying Data Source Names for ODBC Data Sources

ODBC data sources use data source names, or DSNs, for specifying the path and file name and connection information. You can add a DSN by using the ODBC Manager in Windows Control Panel or the RegisterDatabase method of the DBEngine object. Data source names are stored in the Windows Registry key \HKEY_CURRENT_USER\Software\ODBC\ODBC.INI.

Each entry in the ODBC.INI key assigns a logical DSN to a set of attributes that includes:

 The name of the ODBC database server

 The name of the database on the server, if multiple databases are supported

 The type of server (for example, SQL Server)

 A description of network and connection information

 Additional information, such as character set conversions

Because connections to ODBC data sources rely on this information, your users must have the appropriate DSN in their Windows Registry before your application attempts to access external data.

See Also   For more information on creating a DSN through the ODBC Manager, see the ODBC Manager Help. For more information on using the RegisterDatabase method, search the Help index for "RegisterDatabase method."

Specifying Source Database Types

The installable ISAM driver for each external data source has an associated database type. When accessing data from an external data source that does not use Microsoft Jet, you must specify the source database type in the connection string so that Microsoft Access knows how to handle the data. It is not necessary to specify the source database type when you are accessing data from other data sources that use Microsoft Jet.

The following table lists the source database types for external data sources supported by Microsoft Access.

Important   In the connection strings, you must enter the source database type strings exactly as they appear in this table, including spaces and punctuation.

Specifying Passwords

If an external table has been protected with a password, you can access the table if you have the correct password information. To access a password-protected table, provide the password in the PWD argument (PWD=password) in the connection string. You must follow the PWD argument with a semicolon. You can either prompt the user for password information and then supply it to the connection string or you can write Visual Basic code that specifies the password, as shown in the following example:

Set tdfRegionOne = dbs.CreateTableDef("First Quarter Sales",0,"Q1Sales", _
   "Paradox 4.x;DATABASE=\\Sales\Regional\Region1;PWD=RollsRoyce;")

Note   You can use the PWD argument to specify database passwords only; you cannot use it to log on to a network. If your application requires network access, you must establish these connections before attempting to access the external data.

You cannot use the PWD argument to decrypt Microsoft Excel worksheets or workbooks; you must unprotect and save the worksheet or workbook in Microsoft Excel before you can open it with the Microsoft Excel installable ISAM driver.

Important   If you store sensitive data in the external data source, you may not want to specify the password in your code. If you do specify passwords in your code, consider either encrypting your database so that others cannot read the password with a text editor or disk utility, or establishing user-level security. For more information on encrypting your database or establishing user-level security, see Chapter 14, "Securing Your Application."

Saving ODBC Passwords Between Sessions

You can save a password as part of a linked ODBC table definition by setting the Attributes property of the TableDef object to dbAttachSavePWD, as shown in the following example:

tdfRegionOne.Attributes = dbAttachSavePWD

The user is prompted once for a password to the external data source. After that, each time a user opens the linked table, Microsoft Access uses the password information that is stored with it.

If your external data is particularly sensitive, you may not want users to be able to save their passwords. To change this behavior, you must create the MSysConf table in your external database to prohibit local storage of logon IDs and passwords. ODBC will automatically prompt users for any missing authentication information when they attempt to open the external database.

See Also   For more information on the MSysConf table, see Chapter 19, "Developing Client/Server Applications."

© 1996 Microsoft Corporation. All rights reserved.