External Data Access Requirements

Before you can connect your application to an external data source, you must make sure that the external data is accessible to your application's users and that your application is properly designed to handle external data source security challenges. You must also make sure that the installable ISAM or ODBC driver is correctly registered for the data source you want to access and that your application interacts correctly with case-sensitive data sources. Finally, you must check that your code doesn't use objects or methods that are specific to data sources that use Microsoft Jet when accessing data sources that don't use Microsoft Jet.

Accessing Data Over a Secured Network

As with any other resource on a network, your Microsoft Access application must have access to the server and the shared folder where the external data resides. You must determine if your application needs read-only or read/write permissions on the shared folder, and you should assign permissions according to your application's needs.

In addition, you must make sure that your application has proper access to the data through the data source's security system. You can provide password information to the data source directly through Data Access Objects (DAO) when you make your connection.

Windows Registry Settings for Installable ISAM Drivers

Each installable ISAM driver has a corresponding Windows Registry setting that configures the driver upon initialization. During the installation process for the installable ISAM drivers, the Microsoft Access Setup program creates keys in the Windows Registry for the following data sources:

 Microsoft FoxPro, versions 2.0, 2.5, 2.6, 3.0, and DBC

 dBASE III, dBASE IV, and dBASE version 5.0

 Paradox, versions 3.x, 4.x, and 5.x

 Microsoft Excel version 3.0, 4.0, 5.0, and 8.0 worksheets

 Lotus 1-2-3 WK1, WK3, and WKS spreadsheets

 Delimited and fixed-width text files in tabular format

 Tabular data in HTML files

These settings are located in the \HKEY_LOCAL_MACHINE\SOFTWARE \Microsoft\Jet\3.5\Engines key and the \HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft\Jet\3.5\ISAM Formats key of the Windows Registry.

Also during the installation process, the Setup program assigns a set of default values to each installable ISAM. Your application can alter these values to change the behavior of certain aspects of each driver. For example, you can select a collating sequence for the FoxPro installable ISAM that changes the case-sensitive behavior of the driver, as discussed in the following section.

Accessing Data from Case-Sensitive Data Sources

Unlike databases that use Microsoft Jet, some desktop database products and database servers are case-sensitive by default; for example, Microsoft FoxPro, dBASE, and Paradox. You must design your application to support searching, querying, and other data access operations on external, case-sensitive data sources. In addition, you can configure some database servers that are accessible through ODBC as case-sensitive servers.

If you need to search on a case-sensitive data source, you can design your queries by using OR clauses to capture all possible combinations (all uppercase, all lowercase, or each word capitalized). However, products that are typically case-sensitive are never case-sensitive in three instances:

 If you're connecting to an Xbase database, such as Microsoft FoxPro or dBASE, check the value of the CollatingSequence setting in the \HKEY_LOCAL_MACHINE \SOFTWARE\Microsoft\Jet\3.5\Engines\Xbase key of the Windows Registry. If this value is set to Ascii, all operations are case-sensitive; if it's set to International, no operations are case-sensitive.

 If you're connecting to a Paradox database, check the value of the CollatingSequence setting in the \HKEY_LOCAL_MACHINE\SOFTWARE \Microsoft\Jet\3.5\Engines\Paradox key of the Windows Registry. If this value is set to Ascii, all operations are case-sensitive; if it's set to International, Norwegian-Danish, or Swedish-Finnish, no operations are case-sensitive. The value of the CollatingSequence setting for Paradox must match the collating sequence of the .db file that you want to open. If you have a .db file that was created using the Ascii collating sequence, setting the value of the CollatingSequence setting in the Windows Registry to International won't change the case sensitivity of operations.

 If you're connecting to a database server through ODBC, the server's configuration determines whether it's case-sensitive. Check with your database server administrator to determine whether or not your server is configured as case-sensitive.

Note   If a query is based on more than one type of data source, the case sensitivity depends on the collating sequence of the database in which the query is stored.

Unsupported Objects and Methods

DAO objects and methods intended for use only on databases that use Microsoft Jet aren't supported for use with external data sources that don't use Microsoft Jet. Unsupported objects and methods include:

 CompactDatabase method

 Container object

 CreateDatabase method

 CreateField method (You can use this method on new tables, but you can't use it to expand tables with existing fields.)

 CreateQueryDef method

 Document object

 QueryDef object

 Relation object

 RepairDatabase method

Transaction Support

Although some methods and objects aren't supported for external data sources that don't use Microsoft Jet, some data sources do support transactions. The following table lists the transaction methods that are available on supported data sources.

See Also   For more information on transactions and these transaction methods, see "Microsoft Jet Transactions" in Chapter 9, "Working with Records and Fields" or search the Help index for the name of the method.

© 1996 Microsoft Corporation. All rights reserved.