Accessing External Data with MS Access
| Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. |
DAT401
Presented at Tech-Ed 97
On This Page
Introduction
Data Access Choices
External Data Sources
Network Access Requirements
Performance Guidelines
Case Sensitivity
Unsupported Objects and Methods
Programming Considerations
Planning for Portability
External Data Access Fundamentals
Introduction
The Microsoft Access database is extremely flexible in its ability to transparently handle data from a variety of sources. External data falls into two categories: indexed sequential access method (ISAM) and Open Database Connectivity (ODBC). The ISAM data sources are traditionally computer-based databases. These include Microsoft® FoxPro® database management system, Paradox®, Microsoft Access, and others. There are two methods of handling the connection to the external data. You can link the tables either by using the user interface or from Microsoft Access Basic. It is also possible to open external databases directly from Microsoft Access Basic.
When a table from another database is linked it behaves just like a native, local table. You can create queries, forms, and reports that use the external data, combine the external data with the data in Microsoft Access tables, and even view and edit the external data while others are using it in the original application. It makes sense to use this approach as an alternative to importing if the external data you want to access is also being updated by software other than Microsoft Access.
Even if all data resides in Microsoft Access format you might find it advantageous to link to external data. By splitting the application (forms, reports, queries, and temporary tables) from the rest of the data you can ease the support burden of distributing your application.
This paper provides general information about accessing external data, and then provides specific instructions for each of the supported external data sources.
Data Access Choices
Microsoft Access Basic provides two choices for accessing external data sources. You can either link the tables to a Microsoft Access database or open the tables directly.
When you link a table, the table looks and performs in most respects like a Microsoft Access table. It even has its own icon in the Database window, along with the other tables in the database. Because the connection information is stored with the table definition, you can open the table whenever you want, either by using the OpenRecordset method or by double-clicking the table icon in the Database window.
When you open a table directly, you specify the connection information by using the connect argument of the OpenDatabase method each time you open the database. Because the connection information in this case is not stored with a table definition, there is no link between the external table and a Microsoft Access database, and an icon for the table will not appear in the Database window.
In many cases, linking is the faster method for accessing external data, especially when the data is located in an ODBC database. Although your particular situation might require you to open the data directly, it is best to consider linking if possible. If you're using external data in an ODBC database and you are unable to link the data, keep in mind that performance when using the data will be significantly slower.
External Data Sources
Microsoft Access can use data from any of the following external data sources:
Microsoft FoxPro, versions 2.0, 2.x, and 3.0.
dBASE III®, dBASE IV®, and dBASE 5.0.
Paradox, versions 3.x, 4.x, and 5.x.
Databases using the Jet database engine (Microsoft Access and Microsoft Visual Basic® programming system).
Microsoft Exchange Server client-server messaging and groupware and Microsoft Outlook™ desktop information manager folders and address books.
Hypertext Markup Language (HTML) tables and lists.
ODBC databases, including but not limited to Microsoft SQL Server™ client-server database management system, SYBASE® SQL Server, and ORACLE® Server.
Note: Subsequent references in this paper to SQL Server refer to both Microsoft SQL Server (for the Microsoft Windows NT® operating system and for OS/2®) and SYBASE SQL Server.
Network Access Requirements
To access an external table on a network, you must be able to connect to the network and have access to:
The server and share (if applicable) on which the external database is located. Server and share access are established through network permissions. For information about setting up network permissions, see your network manuals.
The external table. Table access permissions are established using security features of the external database. For information about setting up access permissions, see your external database manuals.
More Information
For network access requirements associated with a specific external data source, see the section describing that data source later in this paper.
Performance Guidelines
Although you can use external tables just as you use regular Microsoft Access tables, it's important to keep in mind that they're not actually in your Microsoft Access database. As a result, each time you view data in an external table, Microsoft Access has to retrieve records from another file. This can take time, especially if the external table is in an ODBC database. Performance will be optimal if you link tables instead of directly opening them, and retrieve and view only the data you need (for example, use restrictive queries to limit results, and don't page up and down unnecessarily).
More Information
For specific recommendations for the data source you intend to access, see the section for that data source later in this paper.
Case Sensitivity
Unlike searches on databases that use the Jet database engine, searches on external databases are usually case sensitive. Exceptions are as follows:
For Paradox data, if the CollatingSequence entry under the [Paradox ISAM] heading in the registry entry HKEY_LOCAL_MACHINE \SOFTWARE \MICROSOFT \JET \3.0\ENGINES is set to International, Norwegian-Danish, or Swedish-Finnish.
For FoxPro or dBASE® data, if the CollatingSequence entry under the Xbase heading in the registry entry HKEY_LOCAL_MACHINE \SOFTWARE \MICROSOFT \JET \3.0\ENGINES is set to International.
For ODBC data, if the server is configured to be case insensitive.
In addition, if a search is made across more than one data source type, the case sensitivity depends on the collating sequences of the databases involved.
More Information
For information about setting the CollatingSequence entries in the registry, see "Initialization Settings," later in this paper.
Unsupported Objects and Methods
Data access objects and methods intended for use only on databases that use the Jet database engine are not supported on external databases.
Unsupported objects include:
Container
Document
QueryDef
Relation
Unsupported methods include:
CompactDatabase
CreateDatabase
CreateField (if the table has existing rows)
CreateQueryDef
DeleteQueryDef
ListParameters
ListTables
OpenQueryDef
RepairDatabase
SetDefaultWorkspace
In addition, the following transaction-processing methods are supported only if the external database supports transactions:
BeginTrans
CommitTrans
Rollback
Programming Considerations
Seek
You cannot use Seek on linked tables, even if the linked tables are Microsoft Access tables. You can, however, get around this by first opening up the other database and then opening a table type recordset. Although this does allow you to use Seek on an external table, this will only work with ISAM databases. If you think your code will ever be connecting to an ODBC data source, do not use Seek in your code.
A main reason people use Seek is to improve performance. They use it in place of one of the Find methods. An alternative method that is almost as fast as Seek is to create a recordset that returns the set of data that you are interested in, even if it is just a single row. Both of these methods work against linked tables. A parameter query is shown below:
Set MyDB = DBEngine.Workspaces(0).Databases(0)
' Open existing QueryDef
Set MyQuery = MyDB.QueryDefs("ParamQuery")
' Set parameters.MyQuery.Parameters("Order Date") = 10/11/93"
MyQuery.Parameters("Ship Date") = "11/4/93"
' Open Recordset.
Set MyRecordset = MyQuery.OpenRecordset()
The other alternative is to create a Structured Query Language (SQL) string on the fly. This is a very flexible and powerful technique. This differs from creating a pass-through SQL string. With a pass-through query, you create a SQL string in the syntax of the server it is being sent to. You gain speed but you lose portability. If you create a Microsoft Access SQL string, the portability remains. It is translated to work against whatever data source you are connecting to. The following code shows how you might create the string. Note the need for the single quotation marks around the string value. If it was a date, you would need to delimit the date with a pound sign (#).
dim strT as string dim strName as string 'Get the name from the control on the form strName = Me![LastName] 'Concatenate the parts of the string strT = "SELECT DISTINCTROW Shipto.* FROM Shipto WHERE " strT = strT & "((Shipto.LastName='" & strName & "')); " 'Open up the recordset based on the SQL string Set MyRecordset = MyDB.OpenRecordset(strT)
Transactions
You have to be careful about your use of transactions when dealing with external sources of data. The first consideration is whether the data source supports transactions at all. Even if it does you still might not have the same level of support for transactions that Microsoft Access provides. With ODBC, only a single-level support of transactions is provided, that is, no nesting of transactions. If you nest transactions in code, it is really just the outermost Begin, Commit, and Rollback that are sent to the server.
Planning for Portability
With the flexibility and ease of use that Microsoft Access provides for using external data sources, you could easily find your data being migrated from one data source to another. A common scenario is for your database to be upsized. Microsoft Access would still be used to provide the forms, queries, and reports but the data would be stored in a SQL database. If you plan ahead you can ease the effort of migrating your data.
If you have an idea what database is likely to be used as a data source, check the maximum length of table and field names. Microsoft Access allows more characters than most SQL databases.
Check on the legality of the characters used in your table and field names. Most SQL databases do not allow spaces in object names. Your forms, reports, and control names can use whatever you like for names. It is just the table and field names that you need to be concerned with.
Keep in mind that case sensitivity can be different.
Do not use Seek.
Be careful of the use of the counter data type. Most servers do not have a counter data type. For instance, to get the equivalent functionality from SQL Server you have to use a trigger and provide the code to generate the value.
Plan on any tables you use to be in the same database. In Microsoft Access you might split up your data into different databases. With ODBC, even if the different databases are on the same server they are considered different sources and will be treated as such. This means you will get heterogeneous joins, which can severely impact the performance of you application.
External Data Access Fundamentals
Now that you're familiar with external data access requirements, you're ready to learn the fundamental methods. The following sections illustrate these methods using external FoxPro, Paradox, and SQL Server tables as examples. Specific examples for each of the external data sources are provided later in this paper.
Linking an External Table
The procedure for linking an external table using Microsoft Access Basic involves four simple steps.
To link an external table
Open the Microsoft Access database in which the external table will be linked. You open the database by using the OpenDatabase method of the Workspace object. For example, to open C:\Access\Data\Test.mdb, use the following:
Set CurrentDatabase = DBEngine.Workspaces(0). OpenDatabase("C:\ACCESS\DATA\TEST.MDB")
Create a new TableDef for the linked table. You create a new TableDef by using the CreateTableDef method of the Database object, as shown here:
Set MyTableDef = CurrentDatabase.CreateTableDef ("AttachedFoxPro Table")
**Note**: Because a **TableDef** name can be any legal Microsoft Access table name, you can give the table a more descriptive name than is often allowed in the external database application. For example, if you link an external dBASE table named SLSDATA, you can rename the linked table "Sales Data 1993 (from dBASE)."
Set connection information for the external database and table. One way to set connection information is to use the Connect and SourceTableName properties of the TableDef object. For example, to connect to the external FoxPro Accounts table on the network share \\Foxpro\Data in the directory \Ap, use the following:
MyTableDef.Connect = "FoxPro 2.5;DATABASE= \FOXPRO\DATA\AP" MyTableDef.SourceTableName = "Accounts"
It is also possible to set connection information by using the *source* and *connect* arguments to the **CreateTableDef** method. This is illustrated in the following example:
<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">
Set MyTableDef = CurrentDatabase.CreateTableDef ("AttachedFoxProTable", 0, "Accounts", "FoxPro 2.5; DATABASE=\FOXPRO\DATA\AP")
The connection information in both of these examples includes a source database type (FoxPro 2.5) and a database name (\\\\Foxpro\\Data\\Ap). For details about setting connection information, see "Specifying Connection Information," later in this paper.
Link the table by appending the new TableDef to the TableDefs collection. You append a new TableDef by using the Append method of the TableDefs collection. For example, to append the TableDef created in step 2, use the following:
CurrentDatabase.TableDefs.Append MyTableDef
After you've linked the table using the Append method, you can begin using the linked table as you would any other Microsoft Access table. The link remains active unless you delete the TableDef or move the source table. If you move the source table, you can refresh the link using the RefreshLink method, as described in "Refreshing and Removing Links," later in this paper.
The following example shows how steps 1 through 4 above are integrated into a module that attaches an external FoxPro table:
Dim CurrentDatabase As Database
Dim MyTableDef As TableDef
'Open the Microsoft Access database named TEST.MDB
Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase
("C:\ACCESS\DATA\TEST.MDB")
'Create the TableDef
Set MyTableDef = CurrentDatabase.CreateTableDef("Attached FoxPro Table")
'Set the connection information
MyTableDef.Connect = "FoxPro 2.5;DATABASE=\\FOXPRO\DATA\AP"
MyTableDef.SourceTableName = "Accounts"
'Append the TableDef to create the link
CurrentDatabase.TableDefs.Append MyTableDef
Opening an External Table
The procedure for directly opening an unattached external table is similar to the procedure for linking an external table. When linking you open the Microsoft Access database to which the external table is linked and then create a TableDef for the external table. When opening an external table you open the external database and then open a recordset for the external table.
When deciding between linking and opening, remember that linking is typically the faster method for accessing external data, especially when the data is located in an ODBC database.
To open an external table
Open the external database. You open the external database by using the OpenDatabase method of the Workspace object. For example, to open the external FoxPro database on the network share \\Foxpro\Data in the directory \Ap, use the following:
Set CurrentDatabase = DBEngine.Workspaces(0). OpenDatabase("\FOXPRO\DATA\AP", False, False, "FoxPro 2.5")
The connection information in this example includes a database name (\\\\Foxpro\\Data\\Ap) and a source database type (FoxPro 2.5). For details about setting connection information, see "Specifying Connection Information," later in this paper.
Open a recordset for the external table. You open a recordset for an external table by using the OpenRecordset method of the Database object, and using the external table name as the source argument. For example, to open a recordset for the external FoxPro Accounts table, use the following:
Set MySet = Db.OpenRecordset("Accounts")
The external table name used as the *source* argument of the **OpenRecordset** method (in this example, Accounts), is given as a table name, *not* a file name, so the file name extension is not used. This requirement holds true for all of the external data sources in which tables are stored as individual disk files. Exceptions for FoxPro and dBASE table names are noted in "Accessing FoxPro and dBASE Data," later in this paper.
Because this example calls the **OpenRecordset** method without a *type* argument, a dynaset is created (the default for a **Database** object). You can also open tables and snapshots on external data by using the DB\_OPEN\_TABLE and DB\_OPEN\_SNAPSHOT constants as the **OpenRecordset***type* argument.
**Important**: Note, however, that you *cannot* use DB\_OPEN\_TABLE with ODBC databases.
After you've opened the recordset, you can work with the data as you would work with any other recordset.
The following example combines steps 1 and 2 into a single module that opens an external FoxPro table, and then opens a recordset on that table.
Dim CurrentDatabase As Database
Dim MySet As Recordset
'Open the external FoxPro database named AP
Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase
("\\FOXPRO\DATA\AP", False, False, "FoxPro 2.5")
'Open a Recordset on the Accounts table
Set MySet = CurrentDatabase.OpenRecordset("Accounts")
Specifying Connection Information
The manner in which you specify connection information depends on whether you're linking or opening the external data source:
When linking an external data source, you set either the TableDef SourceTableName and Connect properties or the source and connect arguments to the CreateTableDef method.
When opening an external data source, you set the dbname and source arguments to the OpenDatabase method.
The SourceTableName property and the source argument to the CreateTableDef method are string expressions for the name of the external table to open. For example, the SourceTableName for an external FoxPro table might be something like:
"ACCOUNTS"
The dbname argument to the OpenDatabase method is a string expression for the name of the database to open. The format for expressing the dbname argument is described in "Database Names," below.
The connect argument to the CreateTableDef method, the source argument to the OpenDatabase method, and the Connect property are string expressions for the connection information to use when opening the external data source. Also known as the connect string, the format for expressing this argument is described in "Connect Strings," below.
More Information
For complete syntax, search Help for "CreateTableDef," "OpenDatabase," or "Connect."
Database Names
For the external ISAM databases such as FoxPro and dBASE, the database name is simply the fully specified path to the directory in which the database files are located. When specifying the database name for a database on a local drive, the format is drive**:\**path, as shown in the following example:
C:\FOXPRO\DATA\AP
When specifying the database name for a database on a network drive, you can either:
Indicate a fully qualified network path (if your network redirector supports it) in the format \\server\share\path. For example:
\FOXPRO\DATA\AP
\-or-
Establish a connection to the network drive first, and then specify the path using the network drive letter instead of the server and share name. For example, if you use drive F: to connect to \\Foxpro\Data, you would specify the database path for the Ap database on that share as follows:
F:\AP
For ODBC databases such as SQL Server, the database name is a zero-length string:
""
Connect Strings
The connect string is a string expression that provides information used to open an external data source. With the OpenDatabase method, for example, the connect string sets the type, path, or data source name (DSN) for an external database.
The first component of a connect string is a source database type (source), as described in "Source Database Types," below. The source database type can be followed by other parameters (such as database names and passwords). With the CreateTableDef method, for example, the source database type can be followed by a database name or a password. With ODBC data sources, the source database type can be followed by a database name, a data source name (described in "Data Source Names" below), or user account information such as user name and password.
Important: When specifying a connect string, use a semicolon to separate components. Do not include any spaces on either side of the semicolon.
More Information
For additional information about connect strings, search Help for "CreateTableDef," "OpenDatabase," or "Connect."
Source Database Types
For the external ISAM databases such as FoxPro and dBASE, the source database type is keyed to an entry in the HKEY_LOCAL_MACHINE \SOFTWARE \MICROSOFT \JET \3.0\ENGINES registry setting, in the folder ISAM Formats. For ODBC databases such as SQL Server, the source database type is always ODBC.
Valid source database types are:
dBASE III |
FoxPro 2.0 |
Paradox 3.x |
dBASE IV |
FoxPro 2.5 |
Paradox 4.x |
dBASE 5.0 |
FoxPro 3.x |
Paradox 5.x |
ODBC |
|
|
Data Source Names
When accessing an ODBC database, you must specify a data source name as part of the connect string using the DSN identifier. Each data source name corresponds to an entry under the HKEY_USERS \DEFAULT \SOFTWARE \ODBC \ODBC.INI. Data source name entries in the ODBC.INI folder define important information about the location and type of the external data source, as well as specific network connection information. You can add data source names to your ODBC.INI folder by using the ODBC option in Control Panel for Microsoft Windows® operating system, the ODBC Administrator application (if you're using Windows 3.0), or the RegisterDatabase method of the DBEngine object. A data source name is not required for external ISAM databases.
More Information
For details about setting up and using data source names, see "Accessing ODBC Data," later in this paper.
Connect String Examples
The following examples illustrate how to set connect strings using the CreateTableDef method, the OpenDatabase method, and the Connect property:
'Setting the connect string using the OpenDatabase method
Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase
("\\PDOX\DATA\AP", False, False, "Paradox 3.X")
'Setting the connect string using the CreateTableDef method
Set MyTableDef = CurrentDatabase.CreateTableDef("Attached SQL
Server Table", 0, "Accounts", "ODBC;
DATABASE=AP;UID=Tim;PWD=whoknows;DSN=SQLSERV")
'Setting the connect string using the Connect Property
MyTableDef.Connect = "Paradox 3.X;DATABASE=\\PDOX\DATA\AP"
Specifying a Password
If an external table has been protected with a password, you can access the table by providing a password in the connect string using the PWD identifier, as shown in the following example:
'Example using CreateTableDef method to connect to Paradox data
Set MyTableDef = CurrentDatabase.CreateTableDef("Attached Paradox
Table", 0, "Accounts", "Paradox 3.X;
DATABASE=\\PDOX\DATA\AP;PWD=ExtPdoxPass")
Important: With ODBC databases, if you don't specify a complete connect string as defined by your ODBC driver, you will be prompted for the missing information when you link the external data.
You can save a password as part of an linked table definition by setting the DB_ATTACHSAVEPWD TableDef attribute, as shown in the following example:
MyTableDef.Attributes = DB_ATTACHSAVEPWD
If you use DB_ATTACHSAVEPWD, Microsoft Access saves whatever login information is entered the first time the linked table is opened. When you subsequently open the linked table, Microsoft Access uses the saved password instead of prompting you for one.
Important: If you save the password as part of the table definition, keep in mind that this enables any user of your program to open the external table. If you want to secure the data, you might want to use Jet database engine security. You can disable password storage by using the MSysConf table in your SQL database. For additional information, search Help for "MSysConf."
When specifying connection information, there is no mechanism to specify network share passwords. In cases where a password is required, you must establish a connection to the network drive first so you can specify the password before attempting to access the external data.
Refreshing and Removing Links
If you move a table that you previously linked, you'll need to refresh the link before you can access the table again.
To refresh a link
Change the path by resetting the database name in the TableDef Connect property. For example, to change the path for a linked FoxPro table so it points to the directory \\Foxserv\Public\Ap, use the following:
CurrentDatabase.Tabledefs![Attached FoxPro Table]. Connect = "FoxPro 2.5;DATABASE=\FOXSERV\PUBLIC\AP"
Use the RefreshLink method of the TableDef object. For example, to refresh the link to the linked table in step 1, use the following:
CurrentDatabase.Tabledefs![Attached FoxPro Table]. RefreshLink
To remove a link
Use the Delete method of the TableDefs collection. For example, to remove the link to the linked table named "Attached FoxPro Table," use the following:
CurrentDatabase.Tabledefs.Delete "Attached FoxPro Table"
**Important**: When you delete a linked table, only the link is deleted. The external table itself is unaffected.
Creating an External Table
To create a table in an external database format, use the OpenDatabase and CreateTableDef methods, as described in the following procedure.
To create an external table
Open the external database by using the OpenDatabase method of the Workspace object. For example, to open the external FoxPro database in the directory C:\Data, use the following:
Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase ("C:\DATA", False, False, "FoxPro 2.5")
Create a TableDef for the new table by using the CreateTableDef method of the Database object. For example, to create a table named FOXTBL, use the following:
Set MyTableDef = CurrentDatabase.CreateTableDef("FOXTBL")
Add fields to the new table by using the CreateField method of the TableDef object and the Append method of the Fields collection. For example, to add a text field named Field1, use the following:
MyTableDef.Fields.Append MyTableDef.CreateField("Field1", DB_TEXT, 15)
Create the external data file by using the Append method of the TableDefs collection. For example, to create the Foxtbl.db file for the TableDef created in step 2, use the following:
CurrentDatabase.Tabledefs.Append MyTableDef
The following example combines steps 1 through 4 into a single module that creates the Foxtbl.db file in the C:\Data directory:
Dim CurrentDatabase As Database
Dim MyTableDef As TableDef
Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase("C:\DATA", False,
False,"FoxPro 2.5")
Set MyTableDef = CurrentDatabase.CreateTableDef("FOXTBL")
MyTableDef.Fields.Append MyTableDef.CreateField("Field1", DB_TEXT, 15)
CurrentDatabase.Tabledefs.Append MyTableDef
Improving Performance
In addition to following standard recommendations for database operations, there are a couple of things you can do when connecting to external data sources that will improve performance. Most importantly, if you're connecting to an ODBC data source, be sure to follow the recommendations outlined in "Accessing ODBC Data," later in this paper.
In addition, if you're making only one pass through a snapshot, you'll notice a significant performance improvement if you use the DB_FORWARDONLY option. Keep in mind, however, that the following methods and the BookMark property will not work when using this option, so you'll need to use alternatives (for example, the Update method):
Clone |
Move (with a negative number only) |
FindFirst |
MoveFirst |
FindLast |
MovePrevious |
FindNext |
OpenRecordset |
More Information
For information about using the DB_FORWARDONLY option, search Help for "OpenRecordset."
Accessing FoxPro and dBASE Data
Using the methods and properties described earlier in this paper, you can either link or directly open .DBF files in FoxPro 2.0, FoxPro 2.x, FoxPro 3.x, dBASE III, dBASE IV, or dBASE 5.0 format. The procedures for accessing the data in .DBF files are similar to the procedures for the other external data sources. Special considerations and examples for using FoxPro and dBASE data are provided in the following sections.
Connection Information
When specifying connection information for FoxPro and dBASE data, use the following:
For this: |
Use this: |
|---|---|
Source database type |
One of the following: FoxPro 2.0, FoxPro 2.x, FoxPro 3.x, dBASE III, dBASE IV, dBASE 5.0. |
Database name |
The fully specified path to the directory containing the FoxPro or dBASE files you intend to access. |
Source table name |
The name of the table—the .DBF file name without the extension, or the complete file name with the extension but substituting a pound sign (#) for the dot (.) that precedes the file name extension. |
Data Type Conversions
When you import data from a FoxPro or dBASE file, Microsoft Access translates the FoxPro or dBASE data types into the corresponding Microsoft Access data type, as shown in the following table:
FoxPro/dBASE data type |
Microsoft Access data type |
|---|---|
Character |
Text |
Numeric, Float |
Number (FieldSize property set to Double) |
Logical |
Yes/No |
Date |
Date/Time |
Memo |
Memo |
General (FoxPro only) |
OLE Object |
Deleted Records
In most cases, when working with external data, if you delete a record, the record is deleted from the source table. When working with FoxPro and dBASE tables, however, deleted records are not actually deleted until the table is packed using FoxPro or dBASE commands or utilities. For this reason, the CompactDatabase method of the DBEngine object will not affect linked FoxPro or dBASE databases, and deleted records can appear when a table is closed and reopened again. You can tell Microsoft Access not to include deleted records by setting the Deleted parameter in the XBASE folder in the HKEY_LOCAL_MACHINE \SOFTWARE \MICROSOFT \JET \3.0\ENGINES registry key to 01 (the default). If you set the Deleted parameter to Off, all records are included in any Recordset objects you create, even deleted records. For additional information about setting the Deleted parameter, see "Initialization Settings," later in this paper.
Indexes
When you link or open a FoxPro or dBASE file, you can also tell Microsoft Access to use one or more FoxPro index (.IDX or .CDX) files or dBASE index (.NDX or .MDX) files to improve performance. Microsoft Access keeps track of the indexes in a special information (.INF) file. When you use Microsoft Access Basic to link or open a FoxPro or dBASE file, you must create the .INF file yourself using a text editor. The name of the .INF file must correspond to the name of the external table. For ease of locating associated indexes, it's best to put the .INF file in the same directory as the associated table. If, however, the table is on a read-only share, you can specify a different path using the INFPath entry in the XBASE folder in the HKEY_LOCAL_MACHINE \SOFTWARE \MICROSOFT \JET \3.0\ENGINES registry setting. For additional information, see "Initialization Settings," later in this paper.
Each entry in the .INF file contains an index number (using the identifiers NDXn for dBASE III and dBASE IV, MDXn for dBASE IV, and CDXn or IDXn for FoxPro), and an index file name (for example, Lastname.ndx). The entries are preceded by a source database type. As an example, the .INF file for a dBASE III table named Authors would be named Authors.inf, and might contain the following entries:
[dBASE III] NDX1=AUTHORID.NDX NDX2=LASTNAME.NDX NDX3=ZIP.NDX
To specify a unique index, add the tag or file name for the unique index to the .INF file using the identifier UIDXn. If the table will be opened only from version 2.0 of the Jet database engine, add the entry under the database type heading, as shown in the following example:
[dBASE III] NDX1=AUTHORID.NDX NDX2=LASTNAME.NDX NDX3=ZIP.NDX UIDX1=AUTHORID.NDX
If the table will also be opened only from version 1.x of the Jet database engine, add the tag or file name as a new heading, followed by an entry for any of the other indexes defined for the table. This is shown in the following example:
[dBASE III] NDX1=AUTHORID.NDX NDX2=LASTNAME.NDX NDX3=ZIP.NDX [UIDX1=AUTHORID.NDX] NDX3=ZIP.NDX
Important: If you're opening the data from version 1.x of the Jet database engine, the unique index must be specified using the UIDXn heading, not as a UIDXn entry as shown in the example for version 2.0, or the MSXBSE35.DLL will not let you read the data.
When you use Microsoft Access to update the data in your .DBF file, Microsoft Access also updates the index files to reflect your changes. If you link or open a .DBF file and associate an index (.IDX, .CDX, .NDX, or .MDX) file, Microsoft Access needs the index file to open the linked table. If you delete or move index files or the .INF file, Microsoft Access will not be able to use the index and will not keep the index up to date. If index files are not current, keep in mind that your query results will be unpredictable. For information about how the Jet database engine locates .INF files, see "Initialization Settings," later in this paper.
Memo Fields
FoxPro and dBASE Memo fields are located in files separate from their associated tables. When linking or opening FoxPro or dBASE tables with associated Memo fields, the Memo files must be located in the same directory as the table files.
Linking a FoxPro Table
The following example shows how to link an external FoxPro version 2.5 table, and then display a message box as confirmation. Note that this example uses a pound sign (#) in the SourceTableName property to specify a nonstandard extension (.XYZ).
Dim CurrentDatabase As Database
Dim MyTableDef As TableDef
'Open the Microsoft Access database named TEST.MDB
Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase
("C:\ACCESS\DATA\TEST.MDB")
'Create the TableDef
Set MyTableDef = CurrentDatabase.CreateTableDef("Attached
FoxPro Table")
'Set the connection information
MyTableDef.Connect = "FoxPro 2.5;DATABASE=\\FOXPRO\DATA\AP"
MyTableDef.SourceTableName = "Accounts#XYZ"
'Append the TableDef to create the link
CurrentDatabase.TableDefs.Append MyTableDef
'Display a confirmation message
MsgBox "Finished attaching " & MyTableDef.SourceTableName & ".", 0
Opening a FoxPro Table
The following example shows how to open an external FoxPro version 2.0 table, and then open a recordset on that table.
Dim CurrentDatabase As Database
Dim MySet As Recordset
'Open the external FoxPro database named AP
Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase
("\\FOXPRO\DATA\AP", False, False, "FoxPro 2.0")
'Open a Recordset on the Accounts table
Set MySet = CurrentDatabase.OpenRecordset("Accounts")
Linking a dBASE Table
The following example shows how to link an external dBASE IV table, and then display a message box as confirmation:
Dim CurrentDatabase As Database
Dim MyTableDef As TableDef
'Open the Microsoft Access database named TEST.MDB
Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase
("C:\ACCESS\DATA\TEST.MDB")
'Create the TableDef
Set MyTableDef = CurrentDatabase.CreateTableDef("Attached dBASE Table")
'Set the connection information
MyTableDef.Connect = "dBASE IV;DATABASE=\\DBASE\DATA\AP"
MyTableDef.SourceTableName = "Accounts"
'Append the TableDef to create the link
CurrentDatabase.TableDefs.Append MyTableDef
'Display a confirmation message
MsgBox "Finished attaching " & MyTableDef.SourceTableName & ".", 0
Opening a dBASE Table
The following example shows how to open an external dBASE III table, and then open a recordset on that table.
Dim CurrentDatabase As Database
Dim MySet As Recordset
'Open the external FoxPro database named AP
Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase
("\\DBASE\DATA\AP", False, False, "dBASE III")
'Open a Recordset on the Accounts table
Set MySet = CurrentDatabase.OpenRecordset("Accounts")
Accessing Paradox Data
Using the methods and properties described earlier in this paper, you can either link or directly open tables from Paradox versions 3.x and 4.x. The procedures to access the data in Paradox files are similar to the procedures for the other external data sources. Special considerations and examples for using Paradox data are provided in the following sections.
Connection Information
When specifying connection information for Paradox data, use the following:
For this: |
Use this: |
|---|---|
Source database type |
One of the following: Paradox 3.x, Paradox 4.x, Paradox 5.0. |
Database name |
The fully specified path to the directory containing the Paradox files you intend to access. |
Source table name |
The name of the table (the .DB file name without the extension). |
Data Type Conversions
When you import data from a Paradox file, Microsoft Access translates the Paradox data types into the corresponding Microsoft Access data type, as shown in the following table:
Paradox data type |
Microsoft Access data type |
|---|---|
Alphanumeric |
Text |
Number |
Number (FieldSize property set to Double) |
Short number |
Number (FieldSize property set to Integer) |
Currency |
Number (FieldSize property set to Double) |
Date |
Date/Time |
Memo |
Memo |
OLE |
OLE Object (note that the Jet database engine recognizes the object but will not let you open it) |
Note: Graphic, Binary, and Formatted memo types are not supported.
Indexes and Keys
Paradox stores important information about a table's primary key in an index (.PX) file. If you link or open a Paradox table that has a primary key, Microsoft Access needs the .PX file in order to open the linked table. If you delete or move this file, you won't be able to open the external table.
If you link or open a Paradox table that doesn't have a primary key, you won't be able to update data in the table using Microsoft Access, and you won't be able to open the table in shared mode. If you want to be able to update the table or open the table in shared mode, define a primary key for the table using Paradox commands.
Network Paths
When you link or open a Paradox table that resides on a server and is shared by multiple users, you must set the ParadoxNetPath option in Paradox folder in the HKEY_LOCAL_MACHINE \SOFTWARE \MICROSOFT \JET \3.0\ENGINES registry setting to the path for either the Paradox.net file (for Paradox 3.x) or the Pdoxusrs.net file (for Paradox 4.x). For example, if the Paradox.net file is on drive Q: in the Wrkgrp directory, set ParadoxNetPath as follows:
ParadoxNetPath=Q:\WRKGRP
Important: The ParadoxNetPath, including the drive letter, must be consistent for all users sharing a particular database (directory). For example, if the Paradox.net file is in the \Wrkgrp directory on drive Q: for one user of the database, it must be in the \Wrkgrp directory on drive Q: for all other users of the database.
For additional information about setting ParadoxNetPath, see "Initialization Settings," later in this paper.
Passwords
The Jet database engine will not be able to open a password-protected table if it is already opened by a Paradox user. This is because Paradox does not allow the Jet database engine to decrypt the table after a valid password is provided.
Linking a Paradox Table
The following example shows how to link an external Paradox version 3.x table, and then display a message box as confirmation:
Dim CurrentDatabase As Database
Dim MyTableDef As TableDef
'Open the Microsoft Access database named TEST.MDB
Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase
("C:\ACCESS\DATA\TEST.MDB")
'Create the TableDef
Set MyTableDef = CurrentDatabase.CreateTableDef("Attached Paradox Table")
'Set the connection information
MyTableDef.Connect = "Paradox 3.X;DATABASE=\\PDOX\DATA\AP"
MyTableDef.SourceTableName = "Accounts"
'Append the TableDef to create the link
CurrentDatabase.TableDefs.Append MyTableDef
'Display a confirmation message
MsgBox "Finished attaching " & MyTableDef.SourceTableName & ".", 0
Opening a Paradox Table
The following example shows how to open an external Paradox version 4.x table, and then open a recordset on that table.
Dim CurrentDatabase As Database
Dim MySet As Recordset
'Open the external Paradox database named AP
Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase
("\\PDOX\DATA\AP", False, False, "Paradox 4.X")
'Open a Recordset on the Accounts table
Set MySet = CurrentDatabase.OpenRecordset("Accounts")
Accessing Microsoft Exchange and Outlook Data
Using the methods and properties described earlier in this paper, you can either link or import .pst or .pab files from Microsoft Exchange and Outlook. The procedures for accessing the data in Microsoft Exchange and Outlook are similar to the procedures for the other external data sources. Special considerations and examples for using Microsoft Exchange and Outlook data are provided in the following sections.
Connection Information
When specifying connection information for Microsoft Exchange and Outlook data, use the following:
For this: |
Use this: |
|---|---|
Source database type |
Microsoft Exchange 4.0. |
Table name |
The fully specified path to the MAPI folder or address book (MAPILEVEL). |
Source table name |
TABLETYPE=0 for Folders and TABLETYPE=1 for address books. |
Database name |
DATABASE—where the database specified is the database containing the IISAM created system tables ( usually the current .mdb). |
Microsoft Exchange. and Outlook Connection Strings
To access messages in a Microsoft Exchange mailbox or public folder, you must use the connection string keyword MAPILEVEL=. This keyword specifies the path to the Microsoft Exchange mailbox that is the parent of the folder you want to access. For example, to access the folder named Barbara in the Important folder in the People folder in the mailbox "Mailbox - Dave Jones", use the following connection string:
"Exchange 4.0;MAPILEVEL=Mailbox - Dave Jones|People\Important;"
You can then use the OpenRecordset method and specify the folder named Barbara. You can specify any valid mailbox name to the left of the pipe symbol, but the mailbox name must be exactly as it appears in the left pane of the Microsoft Exchange application window. Any spaces and capitalization in the name must be preserved.
In most cases, Microsoft Exchange users must supply a profile name and password before gaining access to mailboxes. When you use the Microsoft Exchange IISAM, you supply the profile name and password with the PROFILE= and PWD= keywords in the connection string. For example:
"Exchange 4.0;MAPILEVEL=Mailbox - Dave Jones|People\Important;PROFILE=DaveJ;PWD=Cisco"
Linking Microsoft Exchange and Outlook Folders
You can access a folder within a Microsoft Exchange mailbox by creating a link to the folder within a Microsoft Jet database, or by opening the folder file directly by using the OpenDatabase method and opening a recordset on the folder within the mailbox. When you link a folder, the folder is treated as a table. The following code links a Microsoft Exchange folder to a Microsoft Jet database so that you can access it as if it were a table.
Dim CurrentDatabase As Database
Dim MyTableDef As TableDef
Dim str As String
Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase
("c:\ACCESS\DATA\TEST.MDB")
'Build the connect string'
str = "Exhange 4.0;MAPILEVEL=Mailbox - Dave Jones" _
& "|People\Important;TABLETYPE=1;" _
& "DATABASE=c:\ACCESS\DATA\TEST.MDB;" _
& "PROFILE=DaveJ;PWD=Cisco;"
'Create a TableDef Object.
Set MyTableDef = CurrentDatabase.CreateTableDef _
("Linked Exchange Folder")
'Set Connection Information.
MyTableDef.Connect = str
MyTableDef.SrouceTableName = "Barbara"
'Append a TableDef Object to create a link.
CurrentDatabase.TableDefs.Append MyTableDef
Important: When building the connect string to open a folder in a Microsoft Exchange mailbox, you must supply the path and name of an existing Microsoft Jet database as the first argument. The Microsoft Exchange IISAM creates system tables in this database to store information about the structure of the Microsoft Exchange folders being accessed, including all the new fields you've appended to these folders. It is easiest to use the current database each time you want to access data information about fields you added to the folders will be lost, and you must recreate them.
Constraints
In Microsoft Exchange, some folders contain only other folders and no messages. You can't access this type of folder by using the Microsoft Exchange IISAM. It is only possible to access folders containing messages.
You can only access existing messages in a Microsoft Exchange mailbox in read-only mode. You can't update information in a Microsoft Exchange mailbox, but you can append new records (messages) to an existing folder. Additionally, you can't use the Seek method because the Microsoft Exchange IISAM doesn't support indexing.
You can add new fields to existing folders or to newly created folders in a Microsoft Exchange mailbox, but you can't delete fields. You can rename folders but you can't rename fields.
Accessing HTML Table and List Data
Using the methods and properties described earlier in this paper, you can either link or import .html files from the intranet and Internet. The procedures for accessing the data on the intranet and Internet are similar to the procedures for the other external data sources. Special considerations and examples for using HTML data are provided in the following sections.
Connection Information
When specifying connection information for HTML data, use the following:
For this: |
Use this: |
|---|---|
Source database type |
HTML Import. |
Database name |
The fully specified path to the HTML table to be used. |
Source table name |
Table named by <Caption>tags if they exist. |
Working with HTML Files
The Hypertext Markup Language (HTML) is a standard for presenting information over the Internet. HTML files are text files that include the information that users will see, along with tags that specify formatting information about how the information will be presented. For example, you can specify tags for bold, italic, underline, indentation, and many other types of formatting. Internet browsers are able to interpret information within HTML files and display them as pages to users. This is how you can create and view pages on the World Wide Web.
In addition to supporting text and graphics, HTML also supports tabular information. Using HTML table data tags (<TD> and </TD>), you can embed one or more tables within an HTML file. You can use the Microsoft Jet HTML IISAM to open HTML files and access the tabular data in them.
Linking HTML Files
You can access a table within an HTML file by creating a link to the table within a Microsoft Jet database, or by opening the HTML file directly by using the OpenDatabase method and opening a recordset on the table within the file. To specify the location of the file, you must know the Uniform Resource Locator (URL) address of the HTML file on the Internet. The following code links an HTML table to a Microsoft Jet database:
Dim dbsJet As Database
Dim tdfHTML As TableDef
Set dbsJet = OpenDatabase("C:\Jet_Samp.mdb")
' Create a TableDef object.
Set tdfHTML = dbsJet.CreateTabledef _
("Linked HTML Table")
' Set connection information.
TdfHTML.Connect = _
"HTML Import;DATABASE=http:"_
& "//www.usa.myserver.com/files/mypage.html"
tdfHTML.SourceTableName = "Q1SalesData"
' Append a TableDef Object to create a link.
DbsJet.TableDefs.Append tdfHTML
Table Naming Conventions
As the previous example shows, you must reference a table inside an HTML file by a name, for example, Q1SalesData. If a table in an HTML files has a caption, the caption is the table name. If a table doesn't have a caption, and it's the only table in the file, use the title of the HTML document to reference the table. If more than one table exists in the HTML file, and none of these tables has captions, you can reference them sequentially in code as Table1, Table2, and so on. The IISAM interprets these as the first unnamed table in the file, the second unnamed table in the file, and so on.
Column Names and Data Types
As the HTML IISAM reads the data in the HTML file, it chooses the data type for a column by interpreting the contents of the cells. For example, if most of the values in a column are numeric, the HTML IISAM interprets the data as Long or Double, depending on whether most of the numbers are integer or floating point values. Similarly, if any of the values in a column is text, the HTML IISAM interprets the data as Text (with a field size of 255).
In columns that contain a combination of data types, the HTML IISAM will convert all of the data to text if any one of the rows contain text data.
If you don't want the HTML IISAM to choose data types, you can force columns of data to have specific data types by create a Schema.ini file, which is kept in the same directory as the local HTML file form which data is being imported. The Schema.ini file contains information about each column of data that is imported using the HTML IISAM. The format of the Schema.ini file is similar to the Schema.ini file that is used to import data in text files. The only difference is that in the Schema.ini file for HTML files, the section heading indicate the table name, not the file name. For example, in the following HTML sChema.ini file, the table name is Sales.
[Sales] ColNameHeader=False Format=FixedLength MaxScanRows=25 CharacterSet=OEM Col1=columnname Char Width 24 Col2=columnname2 Date Width 9 Col3=columnname7 Float Width 10 Col4=columnname8 Integer Width 10
By default, the HTML IISAM assumes that the first row of the table doesn't contain field names. You can override this by using the HDR= keyword in the connection string. For example, to indicate that the first row of the table contains field names, include "HDR=Yes" in the connection string.
Although the HTML specification supports a table header tag (<TH>), the HTML IISAM doesn't assume that this tag indicates a field name. The HTML IISAM will treat all data within the <TH> and <\TH> tags as normal table data. The reason for this is that you can embed <TH> tags within the table, not just place them in the first row.
Accessing External Databases that Use the Jet Database Engine
In addition to the other data sources described earlier in this paper, you can also use Microsoft Access Basic to view and edit data in other databases that use the Jet database engine. For example, you might want to use a table from another Microsoft Access database stored on a network. With Microsoft Access, you can link tables from other databases that use the Jet database engine and use them as if they were in the open database. This is particularly useful if you want to store your data in one database, while keeping forms, reports, and other objects in a separate database.
Connection Information
You access data in external databases that use the Jet database engine in the same manner as you would access any other external data. When specifying connection information for external databases that use the Jet database engine, use the following:
For this: |
Use this: |
|---|---|
Source database type |
Not applicable. |
Database name |
The fully specified path to the .mdb file. |
Source table name |
The name of the table. |
Linking an External Microsoft Access Table
The following example shows how to link an external Microsoft Access table, and then display a message box as confirmation. Notice that the connection string begins with just a semicolon, rather than a source database type (which is not applicable when connecting to external databases that use the Jet database engine).
Dim CurrentDatabase As Database
Dim MyTableDef As TableDef
'Open the Microsoft Access database named TEST.MDB
Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase
("C:\ACCESS\DATA\TEST.MDB")
'Create the TableDef
Set MyTableDef = CurrentDatabase.CreateTableDef("Attached Jet Table")
'Set the connection information
MyTableDef.Connect = ";DATABASE=\\ACCESS\DATA\AP.MDB"
MyTableDef.SourceTableName = "Accounts"
'Append the TableDef to create the link
CurrentDatabase.TableDefs.Append MyTableDef
'Display a confirmation message
MsgBox "Finished attaching " & MyTableDef.SourceTableName & ".", 0
Opening an External Microsoft Access Table
The following example shows how to open an external Microsoft Access table, and then open a recordset on that table.
Dim CurrentDatabase As Database
Dim MySet As Recordset
'Open the external Microsoft Access database named AP
Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase
("\\ACCESS\DATA\AP.MDB", False, False, "")
'Open a Recordset on the Accounts table
Set MySet = CurrentDatabase.OpenRecordset("Accounts")
Accessing ODBC Data
Using the methods and properties described earlier in this paper, you can access data in ODBC databases such as SQL Server. The procedures for accessing the data in ODBC databases are similar to the procedures for the other external data sources. Special considerations and examples for using ODBC data are provided in the following sections.
Configuration
Before you can access external ODBC data sources using Microsoft Access, you might need to configure the following items on your workstation:
Data source names
Drivers and other associated software
Time-out values
Data Source Names
As described earlier in this paper, each ODBC data source is identified by an folder in the HKEY_USERS \DEFAULT \SOFTWARE \ODBC \ODBC.INI registry entry. Each data source name entry in the ODBC.INI folder specifies connection information for the external data source including network location, drivers, and attributes.
To add a data source name to your ODBC.INI folder, you can use the ODBC option in the Windows Control Panel, the ODBC Administrator application (if you're using Windows 3.0), or the RegisterDatabase method of the DBEngine object.
To add a data source name using the ODBC option
From the File Manager group containing the Control Panel, double-click the Control Panel icon.
In the Control Panel window, double-click the ODBC icon.
Click the Add button.
Follow the instructions that appear on the screen. If you need help in completing any of the dialog boxes, click the Help button.
To add a data source name using the RegisterDatabase method
Use the following syntax:
DBEngine.RegisterDatabase dsn, driver, silent, attributes
The RegisterDatabase arguments are as follows:
Argument |
Description |
|---|---|
dsn |
A string indicating the data source name to add (for example, "SQLTEST"). Often, dsn is the name of the server. |
driver |
A string indicating the ODBC driver to use (for example, "SQL Server"). Note that the driver argument is the name of the driver, not the name of the DLL file (so you'd use "SQL Server," not "SQLSRVR.DLL"). To use RegisterDatabase to add a data source name using a particular driver, you must have already installed ODBC and the indicated driver. |
silent |
A Boolean value that is True if you do not want to display the ODBC driver dialog boxes that prompt for driver-specific information, or False if you want to display the ODBC driver dialog boxes. If silent is True, then attributes must contain all the necessary driver-specific information. |
attributes |
A string indicating the list of keywords to be added to the ODBC.INI file. Each keyword in the list is delimited by a carriage return. If the database is already registered in the ODBC.INI file, the entry is updated. If RegisterDatabase fails, no changes are made to the ODBC.INI file and an error occurs. |
Here is an example of how to use the RegisterDatabase method to add a data source name for an SQL Server on the server \\Ptludlow:
Dim Attribs As String 'Build keywords string. Attribs = "Description=SQL Server on server PtLudlow" & Chr$(13) Attribs = Attribs & "OemToAnsi=No" & Chr$(13) Attribs = Attribs & "Server=PtLudlow" & Chr$(13) Attribs = Attribs & "Database=NWIND" 'Update ODBC.INI. DBEngine.RegisterDatabase "PtLudlow", "SQL Server", True, Attribs
Note: When adding a data source name for a SYBASE SQL Server or a Microsoft SQL Server listening on an alternate pipe, in addition to the Server argument you must also include the Network and Address arguments in the keyword string. For example:
Atrribs= Attribs & Network=DBNMP3 & Chr$(13) Atrribs= Attribs & Address=\PTLUDLOW\PIPE\ALT\QUERY & Chr$(13)
You can confirm that the entry was added by using either the ODBC option in the Windows Control Panel or the ODBC Administrator application (if you're using Windows 3.0).
Drivers and Other Associated Software
Microsoft Access uses ODBC drivers when accessing tables in ODBC databases. Before you can connect to an ODBC database, the appropriate drivers and DLLs must be installed.
You install the appropriate ODBC driver for a database by running the ODBC Setup program. You can also install drivers for SQL Server using the Setup program on the ODBC disk included with Microsoft Access.
Important: The ODBC driver must be Level 1–compliant.
After you install ODBC, you can change ODBC driver information at any time using either the ODBC option in Windows Control Panel or the ODBC Administrator application (if you're using Windows 3.0). For additional information, search Help for "ODBC drivers."
More Information
For additional information about driver requirements for SQL Server, see the online Help file DRVSSRVR.HLP, which you can find in the \Windows\System directory.
Time-out Values
Microsoft Access provides two time-out properties that you can set to control time-out values for connecting to external ODBC databases and for performing queries that access external ODBC databases:
LoginTimeout
Indicates the number of seconds that Microsoft Access should wait for an external ODBC server to respond to a connection request. This feature is especially useful when the default time-out of 20 seconds is too short. Setting a higher time-out value is essential when using local area networks that rely on modems or long-distance bridges, or in situations where network or server load prevent the server from responding in the allotted time. Setting the time-out to 0 indicates that no time-out is to occur.
To set a login time-out value, use the DBEngine LoginTimeout property. For example:
DBEngine.LoginTimeout = 60
If you need to use different login time-out values for different databases, you can change the LoginTimeout property between calls to the **OpenDatabase** method.
QueryTimeout or ODBCTimeout
Indicates the number of seconds that Microsoft Access should wait for an external ODBC server to complete a query. After a query times out, the external server is told to stop processing it and your application will receive an error. At this point you can resubmit the query or inform the user that the transaction cannot be completed.
If your ODBC server supports this functionality, you can use this property to find out when queries have been blocked by heavy use or by locking problems on the external server. The default is 60. Setting a value of 0 indicates that no time-out is to occur.
To set a query time-out value, you use the QueryTimeout property of the Database object or the ODBCTimeout property of the QueryDef object. For example:
CurrentDatabase.QueryTimeout = 120
Connection Information
When specifying connection information for ODBC data, use the following:
For this: |
Use this: |
|---|---|
Data source name |
A data source name from the ODBC.INI file |
Source database type |
ODBC |
Database name |
Zero-length string ("") |
Source table name |
The object identifier for the table |
Note: With ODBC, if the connect string is missing any information, when you first attempt to connect to the external data, you'll receive a prompt for the missing information.
Data Type Conversions
For external ODBC data sources, each native data type corresponds to an ODBC data type. Because there isn't a one-to-one relationship between ODBC data types and Microsoft Access data types (a given data source doesn't necessarily support all of the data types defined for ODBC), understanding how one data type is converted to another requires an understanding of exactly what each ODBC data type represents. The following table, which defines each ODBC data type and provides a corresponding Microsoft Access data type for each, will help:
ODBC data type / Description data type |
Microsoft Access |
|---|---|
SQL_BIT |
Yes/No |
SQL_TINYINT |
Number (FieldSize set to Integer) |
SQL_SMALLINT |
Number (FieldSize set to Integer) |
SQL_INTEGER |
Number (FieldSize set to Long Int) |
SQL_REAL |
Number (FieldSize set to Single) |
SQL_FLOAT, SQL_DOUBLE |
Number (FieldSize set to Double) |
SQL_TIMESTAMP, SQL_DATE |
DateTime |
SQL_TIME |
Text |
SQL_CHAR |
Depends on string length: |
SQL_VARCHAR |
Same as SQL_CHAR |
SQL_BINARY |
Depends on length: |
SQL_VARBINARY |
Same as SQL_BINARY |
SQL_LONGVARBINARY |
OLE Object |
SQL_LONGVARCHAR |
Memo |
SQL_DECIMAL |
|
Signed, exact, numeric value with a precision (p) and a scale (s). If s = 0 |
|
For p <= 4, |
Number (Field Size set to Integer) |
For 5 <= p <= 9, |
Number (Field Size set to Long Int) |
For 10 <= p <= 15, |
Number (Field Size set to Double) |
If 0 < s <= 4: |
|
For p <= 15, |
Number (Field Size set to Double) |
If s > 4 or p > 15 |
Text |
For SQL Server only: |
|
For p = 10 and s = 4, |
Currency |
For p = 19 and s = 4, |
Currency |
SQL_NUMERIC |
Same as SQL_DECIMAL |
Signed, exact, numeric value with a precision (p) and a scale (s). |
|
Indexes
To edit an external SQL table, the table must have a unique index. If the table does not have a unique index, you must define a unique index for the table from within the SQL database before you link it. You can also connect to external SQL database views. For information about how to make SQL views updatable, search Help for "tables: updating."
Limitations
You cannot use the DB_OPEN_TABLE option of the OpenRecordset method to directly open a table in an ODBC database. You can, however, open dynasets and snapshots using the DB_OPEN_DYNASET and DB_OPEN_SNAPSHOT options.
Pass-through Queries
To access server-specific features such as stored procedures, you can use pass-through queries. Pass-through queries are sent directly to the server for processing and are not processed by the Jet database engine. For information about creating pass-through queries, search Help for "pass-through queries."
Performance
If you're connecting to an external table in an ODBC database, you'll achieve the best results by following these guidelines:
Use linked tables instead of directly opened tables whenever possible.
Linked tables are considerably faster, more powerful, and more efficient than directly opened tables. This recommendation is the most important recommendation in this list.
Retrieve and view only the data you need.
Use restricted queries to limit the number of records that you fetch and select only the columns you need, so Microsoft Access can transfer less data over the network. Don't use dynasets if you're not updating. Use ForwardOnly snapshots if you're only scrolling forward. Also, don't page up and down unnecessarily in the data, and avoid jumping to the last record in a large table. The fastest way to add new records to a table is to choose Data Entry from the Records menu.
Use a cache.
Assuming that the data most recently retrieved from the server will probably be requested again while the application is running, and that it is faster to fetch a single large chunk of data (many rows) than to fetch many individual rows, caching improves the performance of an application that retrieves data from a remote server. Microsoft Access forms and data sheets automatically use a cache. For additional information about using a cache, search Help for "cache."
Avoid locking records longer than necessary.
Remember that other users may be trying to use an external table at the same time you are.
Avoid using queries that cause processing to be done locally.
Don't use user-defined functions with remote column arguments, use heterogeneous joins only on indexed columns, and realize if you do that some processing has to be done locally. When accessing external data, the Jet database engine processes data locally only when the operation cannot be performed by the external database. Query operations performed locally include:
WHERE clause restrictions on top of a query with a DISTINCT.
WHERE clauses containing operations that can't be processed remotely, such as user-defined functions that involve remote columns. (Note that, in this case, only the parts of the WHERE clause that cannot be processed remotely will be processed locally.)
Joins between tables from different data sources. (It's important to note, however, that simply having joins between tables from different data sources doesn't mean that all of the processing occurs locally — if restrictions are sent to the server, only relevant rows will be processed locally.)
Joins over aggregation or the DISTINCT clause.
Outer joins containing syntax not supported by the ODBC driver.
DISTINCT clauses containing operations that can't be processed remotely.
ORDER BY expressions (if the remote data source doesn't support them).
ORDER BY clauses containing operations that can't be processed remotely.
Multiple-level GROUP BY clauses, such as those used in reports with multiple grouping levels.
GROUP BY clauses on top of a query with a DISTINCT option.
GROUP BY clauses containing operations that can't be processed remotely.
Crosstab queries that have more than one aggregate or that have an ORDER BY clause that matches the GROUP BY clause.
TOP N or TOP N PERCENT.
Preconditions for SQL Server
Before you can connect to a SQL Server database, the following conditions must be met:
You must have installed catalog stored procedures using INSTCAT.SQL.
You must have the correct ODBC files installed on your system. (The ODBC Setup program should install these correctly.)
You must have a valid data source name for the external data source.
You must be logged on to the network. For example, if you are using Microsoft LAN Manager, run the command net start workstation, and then log on to the network using a valid password. If you have problems logging on to the network, consult your network administrator.
You must have permission to access the server on which SQL Server is running. If you have problems accessing the server, contact your network administrator.
You must have enough connections on the SQL Server. The SQL Server can be configured for a limited number of simultaneous connections. Once that limit is reached, no further connections are permitted until a user releases a connection. Your SQL Server administrator can add more connections if needed. Your program strategy should include logic that closes unneeded connections while not being used—in other words, closes Dynasets and Databases when your user is no longer actively using the workstation. (For additional information, see "Initialization Settings," later in this paper.)
You must have SELECT permission on SQL Server for the object you're trying to access. If you have problems accessing a specific object, contact your SQL Server administrator.
More Information
For additional information about accessing SQL Server databases using Microsoft Access, see the online Help file DRVSSRVR.HLP, which you can find in the \Windows\System directory. For information about setting up and connecting to an external database, contact your primary support provider for the external database.
Linking a Table on an ODBC Data Source
The following example shows how to link a Microsoft SQL Server table, and then display a message box as confirmation:
Dim CurrentDatabase As Database
Dim MyTableDef As TableDef
'Open the Microsoft Access database named TEST.MDB
Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase
("C:\ACCESS\DATA\TEST.MDB")
'Create the TableDef
Set MyTableDef = CurrentDatabase.CreateTableDef("Attached SQL Server Table")
'Set the connection information
MyTableDef.Connect = "ODBC;DATABASE=AR;UID=sa;
PWD=nimda;DSN=SQLTEST"
MyTableDef.SourceTableName = "Accounts"
'Append the TableDef to create the link
CurrentDatabase.TableDefs.Append MyTableDef
'Display a confirmation message
MsgBox "Finished attaching " & MyTableDef.SourceTableName & ".", 0
Opening a Table on an ODBC Data Source
The following example, which should be used only if linking the external data is not possible, shows how to directly open a Microsoft SQL Server table and then open a recordset on that table.
Warning: Directly opening ODBC data (as opposed to linking it) is extremely inefficient and seriously degrades performance. Use linked tables if at all possible.
Dim CurrentDatabase As Database
Dim MySet As Recordset
'Open the external SQL Server database named AP
'***NOTE THAT PERFORMANCE USING THE DATABASE WILL BE SLOW***
Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase("",
False,False, ODBC;DATABASE=AP;UID=Michaela;
PWD=whocares;DSN=SQLSERV")
'Open a Recordset on the Accounts table
Set MySet = CurrentDatabase.OpenRecordset("Accounts")
Initialization Settings
When you install external database drivers, the Setup program writes associated entries to the registry in several folders off the HKEY_LOCAL_MACHINE \SOFTWARE \MICROSOFT \JET \3.0\ENGINES root. The [ENGINES] folder is added regardless of which external data source you intend to access. The other headings are added only if you are using the associated drivers.
Although the Setup program writes intelligent defaults for the registry file entries, your particular environment or preferences might require you to change them. If you find you need to customize your settings, the following examples and tables will be helpful.
Note: When you change your initialization settings, you must exit and then restart Microsoft Access before the new settings take effect.
[ENGINES]
The [ENGINES] registry folder includes initialization settings for the Jet database engine. Typical settings for the entries in this folder are shown in the following example:
PageTimeout 5 LockedPageTimeout 5 LockRetry 20 CommitLockRetry 20 MaxBufferSize 512 ReadAheadPages 16
Microsoft Access uses the entries as follows:
Entry |
Description |
|---|---|
PageTimeout |
The length of time between when data that is not read-locked is placed in an internal cache and when it is invalidated, expressed in 100-millisecond units. The default is 5 (500 milliseconds or .5 second). |
LockedPageTimeout |
The length of time between when data that is read-locked is placed in an internal cache and when it is invalidated, expressed in 100-millisecond units. The default is 5 (500 milliseconds or .5 second). |
LockRetry |
The number of times to repeat attempts to access a locked page. The default is 20. (Note that LockRetry is related to CommitLockRetry, described below.) |
CommitLockRetry |
The number of times the Jet database engine attempts to get a lock on data in order to commit changes to that data. If it fails to get a commit lock, then updates to the data will be unsuccessful. |
MaxBufferSize |
The size of the Jet database engine internal cache, measured in kilobytes (K). MaxBufferSize must be between 18K and 4096K, inclusive. The default is 512K. |
ReadAheadPages |
The number of pages to read ahead when doing sequential scans. The default is 16. |
ENGINES
Under the ENGINES heading are folders for each IISAM. Each entry includes driver paths for all of the external ISAM databases. Typical settings for these entries are shown in the following example:
Paradox C:\WINDOWS\SYSTEM\MSPDOX35.DLL Xbase C:\ WINDOWS\SYSTEM\MSXBSE35.DLL (With Xbase including Dbase and FoxPro)
Each entry under the ENGINES heading indicates the path to an external ISAM database driver. If the entry is incorrect, or if the path for a specific driver is incorrect, you'll receive the message "Cannot find installable ISAM."
Xbase
The Xbase folder includes initialization settings for the MSXBSE35.DLL driver, used for external access to FoxPro and dBASE. Typical settings for the entries under this heading are shown in the following example:
NetworkAccess 01 PageTimeout 0x00000258 (600) INFPath C:\DBASE\SYSTEM CollatingSequence Ascii Deleted 01 Century 00 Date MDY Mark 0x00000000 (0) Exact 00 SupportsLongNames 00
Microsoft Access uses the Xbase entries as follows:
Entry |
Description |
|---|---|
NetworkAccess |
An On or Off indicator for file-locking preference. If NetworkAcccess is Off, tables are opened for exclusive access, regardless of the settings of the OpenDatabase and OpenRecordset exclusive options. The default is On. |
PageTimeout |
The length of time between when data is placed in an internal cache and when it is invalidated. The value is specified in 100-millisecond units. The default is 600 (60 seconds). |
INFPath |
The full path to the .INF file directory. The Jet database engine first looks for an .INF file in the directory containing the table. If the .INF file is not in the database directory, it looks in the INFPath. If there is no INFPath, it uses whatever index files (.CDX or .MDX) it finds in the database directory. This entry is not written by default. |
CollatingSequence |
The collating sequence for all dBASE tables created or opened using Microsoft Access. Possible values are ASCII and International. The default is ASCII. |
Deleted |
An On or Off indicator that determines how records marked for deletion are handled by the Jet database engine. On is equivalent to the dBASE command SET DELETED ON, and indicates never to retrieve or position on a deleted record. Off is equivalent to the dBASE command SET DELETED OFF, and indicates to treat a deleted record like any other record. The default is Off. |
Century |
An On or Off indicator for formatting the century component of dates in cases where date-to-string functions are used in index expressions. On is equivalent to the dBASE command SET CENTURY ON and Off is equivalent to the dBASE command SET CENTURY OFF. The default is Off. |
Date |
The date formatting style to use in cases where date-to-string functions are used in index expressions. The possible settings for this entry, which corresponds to the dBASE SET DATE command, are American, ANSI, British, French, DMY, German, Italian, Japan, MDY, USA, and YMD. The default is American. |
Mark |
The decimal value of the ASCII character to use as a mark character for delimiting date parts. The default depends on the Date setting as follows: |
Exact |
An On or Off indicator for date comparisons. On is equivalent to the dBASE command SET EXACT ON. Off is equivalent to the dBASE command SET EXACT OFF. The default is Off. |
SupportsLongNames |
An On or Off indicator for support of long file names. The default is off for this IISAM |
Paradox
The Paradox folder includes initialization settings for the MSPDOX35.DLL driver, used for external access to Paradox. Typical settings for the entries under this heading are shown in the following example:
PageTimeout 600 ParadoxUserName ParadoxNetPath ParadoxNetStyle 4.X CollatingSequence Ascii SupportsLongNames 00
Microsoft Access uses the Paradox entries as follows:
Entry |
Description |
|---|---|
PageTimeout |
The length of time between when data is placed in an internal cache and when it is invalidated. The value is specified in 100-millisecond units. The default is 600 (60 seconds). |
ParadoxUserName |
The name to be displayed by Paradox if a table is locked by the Paradox ISAM and an interactive user attempts to place an incompatible lock. This entry is not added if the computer is not on a network. The Setup program sets this to the Microsoft Access user name. |
ParadoxNetPath |
The full path to the directory containing the Paradox.net file (for Paradox 3.x) or the Pdoxusrs.net file (for Paradox 4.x). This entry is not added if the computer is not on a network. Usually, you need to change the initial setting (added by the Setup program), which is a best guess at where the file might be. The full ParadoxNetPath (including the drive letter) must be consistent for all users sharing a particular database (directory). |
ParadoxNetStyle |
The network access style to use when accessing Paradox data. Possible values are 3.x and 4.x |
CollatingSequence |
The collating sequence for all Paradox tables created or opened using Microsoft Access. Possible values are ASCII, International, Norwegian-Danish, and Swedish-Finnish. |
SupportsLongNames |
On or Off switch indicating support of long file names. Default setting is Off for this IISAM. |
Microsoft Exchange
The Microsoft Exchange folder includes initialization settings for the MSEXCH35.DLL driver, used for external access to Microsoft Exchange folders and address books. Typical settings for the entries under this heading are shown in the following example:
SupportsLongNames 00
Microsoft Access uses the Exchange entries as follows:
Entry |
Description |
SupportsLongNames |
On or Off switch indicating support of long file names. Default setting is Off for this IISAM. |
HTML
The TEXT folder includes initialization setting for the MSTEXT35.DLL driver, used for external access to HTML tables and lists. Typical setting for the entries under this heading are shown in the following example:
MaxScanRows 25 SupportsLongNames 00 FirstRowHasNames 01
Microsoft Access uses the Text entries as follows:
Entry |
Description |
|---|---|
MaxScanRows |
The number or rows that are scanned to determine the data type of each column. |
SupportsLongNames |
On or Off switch indicating support of long file names. Default setting is Off for this IISAM. |
FirstRowHasNames |
On or Off indicator for column header information. On (01) indicates that the first row has column names. Default is On. |
ODBC
The ODBC registry is not created by the Setup program, but can be added by the user under the HKEY_LOCAL_MACHINE \SOFTWARE \MICROSOFT \JET \3.0\ENGINES entry. This entry will includes initialization settings for the ODBC.DLL driver, used for external access to ODBC data sources. Typical settings for the entries under this heading are shown in the following example:
ODBC TraceSQLMode 0 TraceODBCAPI 0 DisableAsync 0 LoginTimeout 20 QueryTimeout 60 ConnectionTimeout 600 AsyncRetryInterval 500 AttachCaseSensitive 0 SnapshotOnly 0 AttachableObjects 'TABLE','VIEW','SYSTEM TABLE','ALIAS','SYNONYM' TryJetAuth 1 PreparedInsert 0 PreparedUpdate 0
Microsoft Access uses the ODBC entries as follows:
Entry |
Description |
|---|---|
TraceSQLMode |
An indicator of whether the Jet database engine will trace SQL statements sent to an ODBC data source in SQLOUT.TXT. Values are 0 (no) and 1 (yes). The default is 0. This entry is interchangeable with SQLTraceMode. |
TraceODBCAPI |
An indicator of whether to trace ODBC application programming interface (API) calls in ODBCAPI.TXT. Values are 0 (no) and 1 (yes). The default is 0. |
DisableAsync |
An indicator of whether to force synchronous query execution. Values are 0 (use asynchronous query execution if possible) and 1 (force synchronous query execution). The default is 0. |
LoginTimeout |
The number of seconds a login attempt can continue before timing out. The default is 20. |
QueryTimeout |
The number of seconds a query can run (total processing time) before timing out. The default is 60. |
ConnectionTimeout |
The number of seconds a cached connection can remain idle before timing out. The default is 600. |
AsyncRetryInterval |
The number of milliseconds between polls to determine if the server is done processing a query. This entry is used for asynchronous processing only. The default is 500. |
AttachCaseSensitive |
An indicator of whether to match table names exactly when linking. Values are 0 (link the first table matching the specified name, regardless of case) and 1 (link a table only if the name matches exactly). The default is 0. |
SnapshotOnly |
An indicator of whether recordsets are forced to be snapshots. Values are 0 (allow dynasets) and 1 (force snapshots only). The default is 0. |
AttachableObjects |
A list of server object types to which linking will be allowed. The default is 'TABLE', 'VIEW', 'SYSTEM TABLE', 'ALIAS', 'SYNONYM'. |
TryJetAuth |
An indicator of whether to try using the Microsoft Access user name and password to log on to the server before prompting. Values are 0 (no) and 1 (yes). The default is 1. |
PreparedInsert |
An indicator of whether to use a prepared INSERT that inserts data in all columns. Values are 0 (use a custom INSERT that inserts only non-NULL values) and 1 (use a prepared INSERT). The default is 0. Using a prepared INSERT can cause NULLs to overwrite server defaults and can cause triggers to execute on columns that were not inserted explicitly. |
PreparedUpdate |
An indicator of whether to use a prepared UPDATE that updates data in all columns. Values are 0 (use a custom UPDATE that sets only columns that have changed) and 1 (use a prepared UPDATE). The default is 0. Using a prepared UPDATE can cause triggers to execute on unchanged columns. |
Troubleshooting
The following information will help you locate and correct problems with external data access.
Connection Problems
If you're having trouble connecting to an external data source, first make sure that you can connect to the network and that you have access to:
The server and share (if applicable) on which the external database is located.
The external table.
If you already have access to these, check that the connection information you're using conforms to the specifications outlined earlier in this paper in "Specifying Connection Information." If you're getting the message "Unable to find installable ISAM," check that:
The database type specified in the connection information matches the entry tag for the associated driver under the Engines section of the registry.
The path indicated for the associated driver under the Engines section of the Registry is correct.
Other things you might check include the following:
Can you connect using another product or a different user account and password?
Have you exceeded the connection limits on the server?
Does the server have enough space?
Does the connection information match the case sensitivity of the server?
If you've checked the items in the preceding lists but you still can't connect, contact your external database vendor.
Temporary Space
When you query a database, Microsoft Access creates temporary indexes on your local hard disk, even if the database is on an external network device. Temporary space requirements can vary from a few thousand bytes to several megabytes, depending on the size of the external tables being queried.
Temporary space is allocated from the directory indicated by the TEMP environment variable, typically the \Windows\Temp directory. If your system has not defined a TEMP environment variable, the Microsoft Windows root directory is used. If the TEMP environment variable points to an invalid path or if your local hard disk does not have sufficient space for these temporary indexes, your application might behave unpredictably as Windows and Microsoft Access run out of resource space.