Main
[pic][pic]
September 29, 1997 For the latest information, see
© 1997 Microsoft Corporation. All rights reserved. Microsoft, FoxPro, Outlook, Visual Basic, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
Accessing External Data with Microsoft Access
By Beth Scott
Table of Contents
Introduction 2
Data Access Choices 2
External Data Sources 3
Network Access Requirements 3
Performance Guidelines 3
Case Sensitivity 4
Unsupported Objects and Methods 4
Programming Considerations 5
Transactions 6
Planning for Portability 6
External Data Access Fundamentals 6
Linking an External Table 6
Source Database Types 10
Data Source Names 11
Specifying a Password 11
Refreshing and Removing Links 12
Accessing FoxPro and dBASE Data 14
Accessing Paradox Data 17
Accessing Microsoft Exchange and Microsoft Outlook Data 19
Accessing HTML Table and List Data 22
Accessing External Databases that Use the Microsoft Jet Database Engine 24
Accessing ODBC Data 25
Initialization Settings 31
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 personal 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 Visual Basic® for Applications (VBA). It is also possible to open external databases directly from VBA.
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, 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
Visual Basic for Applications provides two choices for accessing external data sources. You can either:
• Link the tables to a Microsoft Access database.
• 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 will be significantly slower when using the data.
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 Microsoft Jet database engine (Microsoft Access and Microsoft Visual Basic)
• Microsoft Exchange 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 setting under the HKEY_LOCAL_MACHINE
\SOFTWARE\Microsoft\Jet\3.0\Engines\Paradox registry key is set to International, Norwegian-Danish, or Swedish-Finnish.
• For FoxPro or dBASE data, if the CollatingSequence setting under the HKEY_LOCAL_MACHINE
\SOFTWARE\MICROSOFT\Jet\3.0\Engines\Xbase registry key 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 records)
• 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 the Seek method 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 to use Seek is to improve performance. It's used 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 record. Both these methods work against linked tables. The following code shows a parameter query.
Set MyDB = DBEngine.Workspaces(0).Databases(0)
' Open existing QueryDef object.
Set MyQuery = MyDB.QueryDefs("ParamQuery")
' Set parameters.
MyQuery.Parameters("Order Date") = "10/11/96"
MyQuery.Parameters("Ship Date") = "11/4/96"
' Open Recordset object.
Set MyRecordset = MyQuery.OpenRecordset()
The other alternative is to create an 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 an 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 were 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 an 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 the Seek method.
• Be sure you use the counter data type appropriately. 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 Visual Basic for Applications 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 valid 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 1996 (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 folder \AP, use the following:
MyTableDef.Connect = "FoxPro 2.5;DATABASE=\\FoxPro\Data\AP"
MyTableDef.SourceTableName = "Accounts"
. 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 the preceding steps 1 through 4 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 object.
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 object 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 folder \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")
3. 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 object 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 the following section, "Database Names."
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 connection string, the format for expressing this argument is described in "Connection Strings," later in this paper.
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 folder 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
• 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:
""
Connection Strings
The connection string is a string expression that provides information used to open an external data source. With the OpenDatabase method, for example, the connection string sets the type, path, or data source name for an external database.
The first component of a connection string is a source database type (source), as described in "Source Database Types," in the following section. 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," later in this paper), or user account information such as user name and password.
Important When specifying a connection string, use a semicolon to separate components. Do not include any spaces on either side of the semicolon.
More Information
For additional information about connection 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\ISAM Formats registry key. 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 connection string by using the DSN identifier. Each data source name corresponds to a subkey under the \HKEY_CURRENT_USER\Software\ODBC\ODBC.INI registry key. Data source name subkeys under the ODBC.INI key 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 key by using the ODBC option in the Control Panel for Microsoft Windows®, the ODBC Administrator application (if you're using Windows version 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.
Connection String Examples
The following examples illustrate how to set connection strings by using the CreateTableDef method, the OpenDatabase method, and the Connect property:
' Setting the connection string by using the OpenDatabase method.
Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase _
("\\Pdox\Data\AP", False, False, "Paradox 3.X")
' Setting the connection string by 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 connection string by 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 connection string using the PWD identifier, as shown in the following example:
' Example of 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 connection 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
1. Change the path by resetting the database name in the TableDef object's Connect property. For example, to change the path for a linked FoxPro table so it points to the folder \\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 folder 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)
5. 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 folder:
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 versions 2.0, 2.x, or 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 folder 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 setting in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.0\Engines\Xbase registry key to 01 (the default). If you set the Deleted setting to Off, all records are included in any Recordset objects you create, even deleted records. For additional information about setting the Deleted setting, 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 Visual Basic for Applications 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 folder as the associated table. If, however, the table is on a read-only share, you can specify a different path using the INFPath setting in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.0\Engines\Xbase registry key. 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 in order 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 folder 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 object.
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 object 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 object 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 object.
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 object 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 dBASE III database named AP.
Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase _
("\\DBASE\Data\AP", False, False, "dBASE III")
' Open a Recordset object 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 folder 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 setting in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.0\Engines
\Paradox registry key to the path for either the file (for Paradox 3.x) or the file (for Paradox 4.x). For example, if the file is on drive Q: in the Wrkgrp folder, set ParadoxNetPath as follows:
ParadoxNetPath=Q:\WRKGRP
Important The ParadoxNetPath, including the drive letter, must be consistent for all users sharing a particular database (folder). For example, if the file is in the \Wrkgrp folder on drive Q: for one user of the database, it must be in the \Wrkgrp folder 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 object.
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 object 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 object on the Accounts table.
Set MySet = CurrentDatabase.OpenRecordset("Accounts")
Accessing Microsoft Exchange and Microsoft 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 or Outlook. The procedures for accessing the data in Microsoft Exchange or Outlook are similar to the procedures for the other external data sources. Special considerations and examples for using Microsoft Exchange or Outlook data are provided in the following sections.
Connection Information
When specifying connection information for Microsoft Exchange or Outlook data, use the following:
|For this |Use this |
|Source database type |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 |
| |installable ISAM driver created system tables ( usually the current .mdb) |
Microsoft Exchange and Microsoft 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 installable ISAM driver, 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 or Microsoft 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 connection 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.SourceTableName = "Barbara"
' Append a TableDef object to create a link.
CurrentDatabase.TableDefs.Append MyTableDef
Important When building the connection 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 installable ISAM driver 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; otherwise, 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 installable ISAM driver. 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 installable ISAM driver 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 or Internet. The procedures for accessing the data on the intranet or 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 tags if they exist |
Working with HTML Files
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 ( and ), you can embed one or more tables within an HTML file. You can use the Microsoft Jet HTML installable ISAM driver 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 Universal 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=" _
& ""
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 installable ISAM driver interprets these as "the first unnamed table in the file", "the second unnamed table in the file", and so on.
Field Names and Data Types
As the HTML installable ISAM driver reads the data in the HTML file, it chooses the data type for a field by interpreting the contents of the cells. For example, if most of the values in a field are numeric, the HTML installable ISAM driver 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 field are text, the HTML installable ISAM driver interprets the data as Text (with a field size of 255).
In fields that contain a combination of data types, the HTML installable ISAM driver will convert all of the data to text if any one of the records contain text data.
If you don't want the HTML installable ISAM driver to choose data types, you can force fields of data to have specific data types by creating a Schema.ini file, which is kept in the same folder as the local HTML file form which data is being imported. The Schema.ini file contains information about each field of data that is imported using the HTML installable ISAM driver. 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 indicates 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 installable ISAM driver assumes that the first record 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 record of the table contains field names, include "HDR=Yes" in the connection string.
Although the HTML specification supports a table header tag (), the HTML installable ISAM driver doesn't assume that this tag indicates a field name. The HTML installable ISAM driver will treat all data within the and tags as normal table data. The reason for this is that you can embed tags within the table, not just place them in the first record.
Accessing External Databases that Use the Microsoft Jet Database Engine
In addition to the other data sources described earlier in this paper, you can also use Visual Basic for Applications 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 object.
Set MyTableDef = CurrentDatabase.CreateTableDef("Attached Jet Table")
' Set the connection information.
MyTableDef.Connect = ";DATABASE=\\Access\Data\AP.mdb"
MyTableDef.SourceTableName = "Accounts"
' Append the TableDef object 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 object 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 subkey under the \HKEY_CURRENT_USER\Software\ODBC\ODBC.INI registry key. Each data source name subkey under the ODBC.INI key specifies connection information for the external data source including network location, drivers, and attributes.
To add a data source name to your ODBC.INI key, you can use the ODBC option in the Windows Control Panel, the ODBC Administrator application (if you're using Microsoft Windows version 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. Click the Add button.
4. Follow the on-screen instructions. 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" & vbCr
Attribs = Attribs & "OemToAnsi=No" & vbCr
Attribs = Attribs & "Server=PtLudlow" & vbCr
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:
Attribs= Attribs & Network=DBNMP3 & vbCr
Attribs= Attribs & Address="\\PTLUDLOW\PIPE\ALT\QUERY" & vbCr
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 version 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 version 3.0). For additional information, search Help for "ODBC drivers."
More Information
For additional information about driver requirements for SQL Server, see the Help file Drvssrvr.hlp, which you can find in the \Windows\System folder.
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 the network or server load prevents 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 connection 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 |
|Single-bit binary data. | |
|SQL_TINYINT |Number (FieldSize set to Integer) |
|Whole number between 0 and 255, inclusive. | |
|SQL_SMALLINT |Number (FieldSize set to Integer) |
|Whole number between 215 – 1 (that is, 32,767) and – 215 | |
|(that is, – 32768), inclusive | |
|SQL_INTEGER |Number (FieldSize set to Long Integer) |
|Whole number between 231 – 1 (that is, 2,147,483,647) and – | |
|231 (that is, – 2,147,483,648), inclusive. | |
|SQL_REAL |Number (FieldSize set to Single) |
|Floating point number with 7-digit precision. Range is | |
|approximately 3.4E – 38 to 3.4E + 38. | |
|SQL_FLOAT, SQL_DOUBLE |Number (FieldSize set to Double) |
|Floating point number with 15-digit precision. Range is | |
|approximately 1.7E – 308 to 1.7E + 308. | |
|SQL_TIMESTAMP, SQL_DATE |DateTime |
|Date and time data. | |
|SQL_TIME |Text |
|Time data. | |
|SQL_CHAR | |
|Character string. |Depends on string length: |
|For string length £ 255, |Text (FieldSize property set to string length)|
| |Memo |
|For length > 255, | |
|SQL_VARCHAR |Same as SQL_CHAR |
|Variable-length character string with a maximum length of | |
|255. | |
|SQL_BINARY |Depends on length: |
|Fixed-length binary data. | |
|For length £ 255, |Binary |
|For length > 255, |(FieldSize property set to length) |
| |OLE Object |
|SQL_VARBINARY |Same as SQL_BINARY |
|Variable-length binary data with a maximum length of 255 | |
|SQL_LONGVARBINARY |OLE Object |
|Variable-length binary data with a maximum length that is | |
|source-dependent. | |
|SQL_LONGVARCHAR |Memo |
|Variable-length character data with a maximum length that is| |
|source-dependent. | |
|SQL_DECIMAL | |
|Signed, exact, numeric value with a precision (p) and a | |
|scale (s). If s = 0 | |
|For p £ 4, |Number (FieldSize set to Integer) |
|For 5 £ p £ 9, |Number (FieldSize set to Long Integer) |
|For 10 £ p £ 15, |Number (FieldSize set to Double) |
|If 0 < s £ 4: | |
|For p £ 15, |Number (FieldSize 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
In order 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 retrieve, and select only the fields 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 click Data Entry on 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 retrieve a single large chunk of data (many records) than to retrieve many individual records, 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 field arguments, use heterogeneous joins only on indexed fields, 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 predicate.
• WHERE clauses containing operations that can't be processed remotely, such as user-defined functions that involve remote fields. (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 records will be processed locally.)
• Joins over aggregation or the DISTINCT predicate.
• 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 predicate.
• 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 Help file Drvssrvr.hlp, which you can find in the \Windows\System folder. 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 object.
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 object 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=MyID;PWD=whocares;DSN=SQLSERV")
' Open a Recordset object 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 subkeys below the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.0\Engines key. The Engines key is added regardless of which external data source you intend to access. The subkeys 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 key includes initialization settings for the Jet database engine. Typical settings for the entries in this key 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:
|Stt |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 next.) |
|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. |
| |The number of attempts the Jet database engine makes to get a commit lock|
| |is directly related to the LockRetry entry. For each attempt made at |
| |getting a commit lock, it will try LockRetry times to get a lock. For |
| |example, if CommitLockRetry is 20 and LockRetry is 20, the Jet database |
| |engine will attempt to get a commit lock as many as 20 times, and for |
| |each of those times it can try to get a lock as many as 20 times, for a |
| |total of 400 attempts at locking. The CommitLockRetry default is 20. |
|MaxBufferSize |The size of the Jet database engine's internal cache, measured in |
| |kilobytes (K). MaxBufferSize must be between 18 and 4096, inclusive. The |
| |default is 512. |
|ReadAheadPages |The number of pages to read ahead when doing sequential scans. The |
| |default is 16. |
Under the Engines key are subkeys for each installable ISAM driver. Each subkey 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 subkey under the Engines key contains a setting that 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 subkey includes initialization settings for the Msxbse35.dll driver, used for external access to FoxPro and dBASE. Typical settings for the entries under this subkey 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 settings as follows:
|Entry |Description |
|NetworkAccess |An On or Off indicator for file-locking preference. If NetworkAccess 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 folder. The Jet database engine first |
| |looks for an .inf file in the folder containing the table. If the .inf |
| |file is not in the database folder, it looks in the INFPath. If there is|
| |no INFPath, it uses whatever index files (.cdx or .mdx) it finds in the |
| |database folder. 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: |
| |"/" (American, MDY) |
| |"." (ANSI) |
| |"/" (British, French, DMY) |
| |"." (German) |
| |"-" (Italian) |
| |"/" (Japan, YMD) |
| |"-" (USA) |
|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 installable ISAM DRIVER. |
Paradox
The Paradox subkey includes initialization settings for the Mspdox35.dll driver, used for external access to Paradox. Typical settings for the entries under this subkey are shown in the following example:
PageTimeout 600
ParadoxUserName
ParadoxNetPath
ParadoxNetStyle 4.X
CollatingSequence Ascii
SupportsLongNames 00
Microsoft Access uses the Paradox settings 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 DRIVER 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. Not a default|
| |registry entry. |
| |Important If you indicate a ParadoxUserName, you must also specify a |
| |ParadoxNetPath and a ParadoxNetStyle or you'll receive an error when |
| |trying to access external Paradox data. |
|ParadoxNetPath |The full path to the folder containing the file (for Paradox|
| |version 3.x) or the file (for Paradox version 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 (folder). Not a default registry entry. |
| |Important If you indicate a ParadoxNetPath, you must also specify a |
| |ParadoxUserName and a ParadoxNetStyle or you'll receive an error when |
| |trying to access external Paradox data. |
|ParadoxNetStyle |The network access style to use when accessing Paradox data. Possible |
| |values are 3.x and 4.x |
| |(Note that Paradox 3.x users cannot set this to 4.x or the driver will |
| |use the wrong locking method.) |
| |This entry is not added if the computer is not on a network. This entry |
| |should correspond to whatever version of Paradox the users in the group |
| |are using. It must be consistent for all users sharing a particular |
| |database (folder). The default is 3.x. |
| |Important If you indicate a ParadoxNetStyle, you must also specify a |
| |ParadoxUserName and a ParadoxNetPath or you'll receive an error when |
| |trying to access external Paradox data. |
|CollatingSequence |The collating sequence for all Paradox tables created or opened using |
| |Microsoft Access. Possible values are ASCII, International, |
| |Norwegian-Danish, and Swedish-Finnish. The default is ASCII. |
| |Important Note that the CollatingSequence entry must match the |
| |collating sequence used when the Paradox data was built. |
|SupportsLongNames |On or Off switch indicating support of long file names. Default setting |
| |is Off for this installable ISAM DRIVER. |
Microsoft Exchange
The Exchange subkey 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 settings as follows:
|Entry |Description |
|SupportsLongNames |On or Off switch indicating support of long file names. Default setting |
| |is Off for this installable ISAM DRIVER. |
HTML
The Text subkey includes initialization setting for the Mstext35.dll driver, used for external access to HTML tables and lists. Typical setting for the entries under this subkey are shown in the following example:
MaxScanRows 25
SupportsLongNames 00
FirstRowHasNames 01
Microsoft Access uses the Text settings as follows:
|Entry |Description |
|MaxScanRows |The number or records that are scanned to determine the data type of |
| |each field. |
|SupportsLongNames |On or Off switch indicating support of long file names. Default |
| |setting is Off for this installable ISAM DRIVER. |
|FirstRowHasNames |On or Off indicator for column header information. On (01) indicates |
| |that the first record has field 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 key. This key will include initialization settings for the ODBC.DLL driver, used for external access to ODBC data sources. Typical settings for the entries under this key 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 settings 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 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 fields. Values are 0 (use a custom INSERT that inserts only |
| |non-NULL values) and 1 (use a prepared INSERT). The default is 0. |
| |Important Using a prepared INSERT can cause NULLs to overwrite |
| |server defaults and can cause triggers to execute on fields that were |
| |not inserted explicitly. |
|PreparedUpdate |An indicator of whether to use a prepared UPDATE that updates data in |
| |all fields. Values are 0 (use a custom UPDATE that sets only fields |
| |that have changed) and 1 (use a prepared UPDATE). The default is 0. |
| |Important Using a prepared UPDATE can cause triggers to execute on |
| |unchanged fields. |
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 key of the registry.
• The path indicated for the associated driver under the Engines key 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 folder indicated by the TEMP environment variable, typically the \Windows\Temp folder. If your system has not defined a TEMP environment variable, the Microsoft Windows root folder 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.
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- access 2 0 95 security faq
- microsoft access guide
- microsoft office access msc lims
- microsoft access overview
- visual basic error codes and messages
- database design and creation
- fix corrupt access database roger s access library
- dbvs ms access 2000 formas datu bāzes tehnoloģijas
- s i l a b u s universitas pendidikan indonesia
Related searches
- one main loan payment online
- one main financial offer number
- one main financial customer complaints
- one main financial loan qualifications
- one main financial pre approval
- one main loan
- main one financial
- 4 main types of businesses
- main goal of financial management
- one main financial secured loan requirements
- one main financial collateral process
- one main financial loan terms