Accessing External Data with Microsoft Access

Introduction

Microsoft® Access 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 the traditionally PC-based databases. These include 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 the external data. By splitting the application (forms, reports, queries, 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/Outlook_ desktop information manager Folders and Address Books
  
  
• 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 the OS/2® operating system) 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 is 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 on the data source that 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 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 quotes around the string value. If it was a date, you would need to delimit the date with a # 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 when using 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 your 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

1. 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")
   

2. 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).”

3. 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:

   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.

4. 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

1. 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.

2. 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 filename, so the filename 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 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:

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 the Control Panel for the 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 a 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 that 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

1. 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 that it points to the directory \\FOXSERV\PUBLIC\AP, use the following:

   CurrentDatabase.Tabledefs![Attached FoxPro Table]. 
   Connect = "FoxPro 2.5;DATABASE=\\FOXSERV\PUBLIC\AP"
   

2. 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

1. 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")
   

2. 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")
   

3. 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)
   

4. 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 important, 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):

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:

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:

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 filename (for example, LASTNAME.NDX). A source database type precedes the entries. 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 filename 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 filename 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:

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:

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 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 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/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/Outlook. The procedures for accessing the data in Microsoft Exchange/Outlook are similar to the procedures for the other external data sources. Special considerations and examples for using Microsoft Exchange/Outlook data are provided in the following sections.

Connection information

When specifying connection information for Microsoft Exchange/Outlook data, use the following:

Microsoft Exchange/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 ISIAM, 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/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. This includes 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/Internet. The procedures for accessing the data on the intranet/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:

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 creating a schema.ini file, which is kept in the same directory as the local HTML file from 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 filename. 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:

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 a 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

1. From the File Manager group containing the Control Panel, double-click the Control Panel icon.
   
   
2. From the Control Panel window, double-click the ODBC icon.
   
   
3. Choose the Add button.
   
   
4. Follow the on-screen instructions. If you need help in completing any of the dialog boxes, choose 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:

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 the 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:

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:

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 Microsoft 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:

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:

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:

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 Microsoft Exchange entries as follows:

HTML

The TEXT folder includes initialization setting for the MSTEXT35.dll driver used for external access to HTML tables and lists. Typical settings 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:

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 include 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:

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.

© 1997 Microsoft Corporation. All rights reserved.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.

Microsoft, FoxPro, OS/2, Visual Basic, Windows, and Windows NT are registered trademarks and Outlook is a trademark of Microsoft Corporation.

Other product and company names listed herein may be the trademarks of their respective owners.