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.
| To link an external table | To open an external table directly |
| Open the Microsoft Access database to which you are linking the table. | Open the external data source. |
| Create a TableDef object for the external table. | Create a Recordset object for the external table. |
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.
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
The Link dialog box appears.
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
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
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.
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.
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.
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.”
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.
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.”
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 Data Source Administrator 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:
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 Data Source Administrator, see the ODBC Data Source Administrator Help. For more information on using the RegisterDatabase method, search the Help index for “RegisterDatabase method.”
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.
| Data source | Source database types |
| dBASE |
|
| Microsoft Excel |
Excel 3.0Excel 4.0Excel 5.0 (used for Microsoft Excel versions 5.0 and 7.0)Excel 8.0 |
| FoxPro |
|
| Lotus 1-2-3 |
|
| ODBC |
|
| Paradox |
|
| Text |
|
| HTML |
|
Important In the connection strings, you must enter the source database type strings exactly as they appear in this table, including spaces and punctuation.
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.”
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.”