So far, you've learned how to use Jet databases in a variety of ways. Chapter 2, "Understanding the Jet 3.0 Data Access Object" shows you how to open and manipulate Jet databases, and Chapter 3, "Using Visual Basic's Data Control"
shows you how to use the Data control and bound Text controls on a form to reduce the amount of code needed to open and navigate through a Jet database. The .mdb database file format you've learned about up to this point is the native, default database
file format used by the Jet database engine; this is the database file format familiar to users of Microsoft Access. The Jet database engine, however, is capable of directly accessing data in a variety of database file formats, including spreadsheet and
text files.
This chapter first discusses the reasons why it is sometimes advantageous to connect to an external database and then explains what other desktop databases you can connect to and how Visual Basic and the Jet database engine implement the connection to
other desktop databases. Also, this chapter explains the two basic techniques for making a connection to another database and discusses the details of making connections to specific database types.
External data is simply any data stored in a file external to a particular .mdb database file with which you may be working. External data may be stored in a table in another .mdb database file, or (more typically) it may be stored in a dBASE,
Paradox, FoxPro, or other database file format, or even in an Excel or Lotus spreadsheet format.
If you're making an outright conversion from one database management system to Microsoft Access, and are using Visual Basic with the Jet database engine to create front-end or decision-support applications for the new database system, you'll probably
end up importing all your existing data into an Access .mdb file format. In many cases, however, importing data into an .mdb database is undesirable, because you need to utilize data files that are still being added to, edited, or otherwise used by the
original database management system.
The primary advantage of connecting to an external database is that you can manipulate the data—add, edit, or delete records—without changing the data file's original format. The external data source can then continue to be used by the
original software system that created it. The ability to connect to external data sources may be especially important if you're developing a decision-support application that uses financial data stored in a spreadsheet format, or that uses text files
downloaded from a mainframe computer. Frequently, you may just need to create a user-friendly and robust front-end for data retrieval, using data that is primarily created and maintained by some other DBMS.
Connecting to external data sources enables you to create applications that use data from a variety of sources, without requiring separate import or data-translation steps. The following section describes the various database and other file formats
that the Jet database engine can connect to directly.
The Jet database engine uses installable ISAM drivers to extend its database manipulating capabilities to include a variety of external data formats. ISAM stands for indexed sequential access method; ISAM databases, however, are not
required to have indexes. The Jet database engine's installable ISAM drivers are actually a group of DLLs that contain the code needed to manipulate a particular external data source. Table 6.1 lists the external data sources supported by the Jet database
engine for which Visual Basic 4.0 includes installable ISAM drivers. The DLLs used by each ISAM driver are the same DLLs used for single-tier ODBC database connections.
| Database Format | Supported Version |
| Access | 1.0, 1.1, 2.0, 7.0 |
| Paradox | 3.x, 4.x, 5.x |
| dBASE | III, IV, 5.x |
| FoxPro | 2.0, 2.5, 2.6, 3.0, DBC |
| Lotus 1-2-3 | WKS, WK1, WK3 |
| Excel | 3.0, 4.0, 5.0, 7.0 (Excel 95) |
| Text | Any delimited or fixed-width text data files |
| Btrieve | 5.1x (Supported in 16-bit Visual Basic 4.0 only) |
Connections to Btrieve external data sources are supported only in 16-bit Visual Basic 4.0. Microsoft does not supply a 32-bit driver for connecting to Btrieve external data sources. If you need to connect to Btrieve data files with Visual Basic, you'll need to obtain a third-party driver from a company such as Intersolv, Inc. Most likely, you'll need to set up your connection to Btrieve external data through an ODBC connection (which also requires a third-party driver). Connecting to ODBC data sources is described in Chapter 19, "Understanding the Open Database Connectivity API."
In Table 6.1, notice that Access databases, themselves, are handled through an installable ISAM driver—the Jet database engine handles all direct connections to databases through its installable ISAM drivers, including its own native .mdb file
format. If you don't specify a particular database format in the connect string argument when you open a database, the Jet database engine assumes that you want to connect to an .mdb database and uses that ISAM driver by default. The Jet database engine
automatically determines which .mdb file format you're connecting to (Access 1.0, 1.1, 2.0 or 7); all of the .mdb formats are handled through the same ISAM driver. Following sections in this chapter explain the details of specifying the particular database
format that you want to use.
Before learning the specifics of what Visual Basic code to use to connect to an external database, it's useful to understand the underlying structure of how the Jet database engine makes use of installable ISAM database drivers. It's also useful to
understand some of the issues that may affect your specific coding choices when creating connections to external databases. The next few sections explain how the Jet database engine configures the ISAM database drivers as well as the general concepts
behind specifying an external database connection. Some general issues relating to external database connections are also discussed.
As you read the rest of this chapter, you should keep in mind that most desktop database systems use a database model different from the .mdb format used by the Jet database engine. Jet databases use a container-object model. Typically, a Jet database
is stored in a single .mdb disk file. The .mdb database file may contain none, one, or several separate data tables which may or may not be related to one another (there is usually some relationship among the tables in a Jet database). The Jet database
engine is able to assist you in preserving referential integrity and enforcing data validation rules because all of the data tables are stored in a single container—the database container itself stores information about relationships among tables. As
a result, the Jet database engine is able to retrieve this information (through the Relations collection of the Database object). You can set a database's properties so that Jet automatically generates errors if your application (or your application's
user) attempts to perform operations that violate referential integrity. Chapter 2 describes how to have the Jet database engine assist you in enforcing referential integrity rules.
In contrast, most other database systems, such as dBASE or Paradox, don't use Jet's container-object model. Instead, they store each data table in a separate disk file without using a container file. Typically, the table files that make up a particular
database's group of tables are stored together in a distinct disk folder—the Jet database engine regards that disk folder to be the container for the database's tables. Because the external data files are not stored in an overall container object,
there is usually no way for the external database to store or pass information about table relations to the Jet database engine. As a result, the Jet database engine won't be able to help you enforce referential integrity rules—it may be possible for
your application to violate the referential integrity rules for the external database tables you connect to. You, the programmer, must take on the responsibility of establishing the referential integrity rules for any external databases whose contents you
alter.
When the Setup program installs Visual Basic 4.0, it copies all the DLL and supporting files to your hard disk, provided you have selected Complete installation or specified that you want to include installable ISAM support in a Custom installation.
After copying the files to your hard disk, Visual Basic's Setup program writes several default configuration values for the installable ISAM engines into your Windows Registry database.
Specifically, Visual Basic 4.0 Setup creates a Jet\3.0 subkey in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft key of the Registry database. The Jet\3.0 subkey contains additional subkeys—Engines and ISAM Formats. These additional subkeys contain
configuration information specific to a particular database engine or ISAM format. Here are the respective full names for the two Registry keys:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.0\Engines HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.0\ISAM Formats
Figure 6.1 shows the default Registry entries for the xBase database engine (the xBase driver is for dBASE and FoxPro database files), as displayed by the Registry Editor. Figure 6.2 shows the default ISAM format Registry entries for dBASE III files,
also displayed by the Registry Editor.
Figure 6.1. The default Registry entries for the Jet 3.0 xBase engine.
Figure 6.2. The default Registry entries for the dBASE III ISAM format.
As you know, Visual Basic 4.0 can be used to develop both 16-bit Windows 3.1x applications and Windows 95 32-bit applications. For 16-bit applications, connections to external databases are handled through the Jet database engine version 2.5.
For 32-bit applications, connections to external databases are handled through the Jet database engine version 3.0. (Both Jet database engine versions are supplied with Visual Basic 4.0 Professional Edition.) To maintain compatibility with Windows 3.1+,
the configuration settings for the 16-bit Jet database engine 2.5 and the ISAM formats are entered into the Vb.ini initialization file in an ISAM section. The settings written into the Vb.ini file are similar to those in the Windows Registry for the Jet
3.0 database engine.
Your 32-bit Visual Basic applications use the default values in the Windows Registry that were established by the Visual Basic Setup program. If you distribute applications using the Setup Wizard supplied with Visual Basic 4.0, the Setup Wizard
automatically includes the default Jet 3.0 and ISAM format settings in your distribution files and writes these default entries into the target computer's Windows Registry file during the installation of your application.
For most applications, the default external database engine and ISAM format settings will suffice. If, however, your application needs configuration information that is different from the Jet database engine and ISAM format defaults, you must create a
Jet\3.0 key in the Windows Registry for your application. Your custom settings key must be in the HKEY_LOCAL_MACHINE\SOFTWARE hive of the Registry. Hive is the term used to describe what might be considered a folder or subfolder of the Registry. For
example, if your application is named CustomApp and requires engine driver settings different from the default values (for example, for changing the collating sequence or century settings for a dBASE database), you create the following Registry hive:
HKEY_LOCAL_MACHINE\SOFTWARE\CustomApp\Jet\3.0\Engines\Xbase
In this Registry hive, you create entries for the Century setting, the CollatingSequence setting, or other values. (The available settings for the engine drivers vary, depending on the specific driver; available settings for each engine are listed in
the "Connecting to Specific Databases" section, later in this chapter.) For a 16-bit Visual Basic application using the Jet 2.5 database engine, you create an [ISAMtype ISAM] section in your Appname.ini initialization file.
The default entries created in the Windows Registry for the external database engines and the ISAM formats are referred to as shadow settings. When you create your own application settings for the Jet database engine drivers or the ISAM formats in the Windows Registry, only the values you set are changed—all other settings obtain their values from the shadow settings.
If your application needs settings for the ISAM format of a dBASE III database that are different than the default values (such as whether the user should be shown a dialog asking for index filenames), you create a Registry hive similar to this one:
HKEY_LOCAL_MACHINE\SOFTWARE\CustomApp\Jet\3.0\ISAM Formats\dBASE III
In this Registry hive, you create an entry for the IndexDialog setting. (Available settings for the ISAM formats vary, depending on the specific format; ISAM format settings for each available ISAM format are listed later in this chapter.) You'll
seldom need to change the ISAM format settings; if any changes are required, you're more likely to have to make alterations to the engine settings.
Any time your application changes settings in the Windows Registry or its Appname.ini file, you must exit and restart the Jet database engine. You can exit the Jet database engine with the following statement:
Set DBEngine = Nothing
The Jet database engine object is reinitialized the next time it is accessed, rereading its initialization information from the Registry.
The next section describes the essential information you'll need to specify a connection to an external database.
No matter which specific coding technique you choose for connecting to an external data source, you must always supply three pieces of information in order to establish the external database connection (specific coding techniques are discussed in the
"Connecting to Specific Databases" section):
The connection string is probably the most important element in establishing the connection to an external database because the connection string tells the Jet database engine which ISAM format to use for the external data. In turn, Jet uses the ISAM
format settings in the Windows Registry to determine which external database engine to use for the specified database. For example, the default ISAM format settings for dBASE III tell the Jet database object to use the xBase driver engine. Jet then looks
up the specific engine DLL filename, location, and additional operating parameters in the xBase folder of the Jet\3.0\Engines key of the Registry database. If your application has its own Registry key for Jet\3.0\Engines and Jet\3.0\ISAM Formats, the Jet
database engine uses the settings found in those keys; otherwise, Jet uses the shadow settings, as explained in the preceding section of this chapter.
Remember that the Jet database engine almost always considers the disk directory of an external database table to be that database's container. When you specify the database name for an external database connection, you're really telling the Jet database engine what the database's container is. You specify the actual external data table's filename as the record source or source table name.
The connection string always has the following syntax:
databasetype;[parameters;]
For an external database connection, the databasetype is always a string specifying one of the available ISAM formats, followed by a semicolon (;). Table 6.2 lists the available ISAM formats. Use the string in the Database Type column of the
table, exactly as it appears in the table, as the databasetype portion of the connect string.
The parameters portion of the connection string is optional; it represents any additional parameters or arguments needed to connect to the external database. Each parameter in the list is separated from the others by a semicolon (;). The
parameter you're most likely to use (especially if you attach the external table to a Jet database, as described later in this chapter) is the DATABASE= parameter, which specifies the database name (that is, the directory path) of the external database.
For certain ISAM databases, usually Excel and Lotus files, you do include the filename of the database in the DATABASE= parameter.
The second parameter you're most likely to use is the PWD= parameter to supply a password for secured external database tables. Using the PWD= and DATABASE= connection parameters is described in the next section of this chapter. Other connect string
parameters available are similar to those used for connecting to ODBC data. Refer to Chapter 19 for more information about connect strings.
| Database Type | Engine | Comments |
| dBase 5.0; | xBase | All xBase drivers require an .inf file to use index files. |
| dBase III; | xBase | |
| dBase IV; | xBase | |
| Excel 3.0; | Excel | Database name string must include the filename for all Excel engine databases. |
| Excel 4.0; | Excel | |
| Excel 5.0; | Excel | |
| FoxPro 2.0; | xBase | Requires creation of an .inf file to use indexes. |
| FoxPro 2.5; | xBase | |
| FoxPro 2.6; | xBase | |
| FoxPro 3.0; | xBase | |
| FoxPro DBC; | xBase | |
| Jet 2.x; | Jet 2.x | Provides capability to connect to external tables in Access 1.0, 1.1, and 2.0 databases. |
| Lotus WK1; | Lotus | Database name string must include the filename for all Lotus engine databases. |
| Lotus WK3; | Lotus | |
| Lotus WK4; | Lotus | |
| Paradox 3.X; | Paradox | External Paradox databases accessed over a network may require changing or entering the ParadoxNetPath setting in the Registry for the Paradox engine. |
| Paradox 4.X; | Paradox | |
| Paradox 5.X; | Paradox | |
| Text | Text | Connection to a text file as an external database may require creation of a Schema.ini file for the text database file. |
Specific information on connecting to the various types of external databases is provided in later sections of this chapter. The following section describes some issues you should take into account when using external databases.
Connecting to external databases through the Jet database engine is relatively straightforward. There are, however, a few areas that require special caution or consideration. The next few paragraphs discuss some specific topics you should be aware of
when programming external database connections.
The Jet database engine came into existence as the result of making the database manipulation services built into Microsoft Access available as an object library that can be used by other applications. As a result, the Jet database engine object
contains several objects and methods that are only relevant to .mdb databases. When you attach or open an external database, some of the Jet database objects and methods are not supported for use on the external data files. Certain transaction methods may
not be supported, depending on whether or not the external database supports transaction processing.
Table 6.3 lists the objects and methods not supported in the Jet database engine for external database tables that are not part of an .mdb database file.
| Unsupported Object/Method | Type | Comments |
| BeginTrans | method | Supported only if the external database supports transaction processing. |
| CreateDatabase | method | |
| CreateField | method | |
| CreateQueryDef | method | |
| CommitTrans | method | Supported only if the external database supports transaction processing. |
| CompactDatabase | method | |
| Container | object | |
| Document | object | |
| QueryDef | object | |
| Relation | object | |
| RepairDatabase | method | |
| Rollback | method | Supported only if the external database supports transaction processing. |
If an object is unsupported, none of that object's methods are available, either. A couple of the unsupported objects may have important consequences for your application. In Table 6.3, notice that the Relation object is not supported.
In native Jet .mdb databases, each database contains a Relations collection of Relation objects. Each Relation object specifies a relationship between two tables—one-to-many, many-to-many, inner or outer join, and so on. Altogether, the relationships
in the database's Relations collection establish the referential integrity rules for that database. This is how the Jet database engine is able to assist you in enforcing and maintaining referential integrity.
Because the Relation object is unsupported in external databases, the Jet database engine isn't able to store information about the external database's referential integrity rules and is therefore unable to determine when or if referential integrity is
violated. As a result, you must write all of the code required to enforce referential integrity in external databases yourself.
The Jet database engine supports a repertoire of field data types broader than that of most of the external database drivers currently supplied with Visual Basic 4.0. When using external data in Lotus, Excel, or text files, no data type mapping occurs.
When using dBASE or FoxPro external data sources, data types are mapped as shown in Table 6.4. For Paradox external data sources, data types are mapped as shown in Table 6.5.
| Database Format | xBase Data Type | Jet Database Type |
| dBASE, FoxPro | Character | String |
| dBASE, FoxPro | Numeric, Float | Double |
| dBASE, FoxPro | Logical | Boolean |
| dBASE, FoxPro | Date | Date/Time |
| dBASE, FoxPro | Memo | Memo |
| FoxPro | General | OLE Object |
| dBASE | OLE | OLE Object |
The information in Table 6.4 is true for all dBASE and FoxPro ISAM formats supported by the Jet database engine. Refer to Tables 6.1 and 6.2 for a list of the supported external databases and ISAM formats. Table 6.5 lists the data type
mapping for all supported Paradox ISAM formats.
| Paradox Version | Paradox Data Type | Jet Database Type |
| All | Alphanumeric | Text |
| All | Number | Double |
| All | Short number | Integer |
| All | Currency | Currency |
| All | Date | Date/Time |
| 4.x, 5.x | Memo | Memo |
| 5.x | Logical | Boolean |
| 5.x | Integer | Long |
| 5.x | Timestamp | Date/Time |
| 5.x | Binary | Byte |
| 5.x | BCD | Double |
| 5.x | Time | Date/Time |
| 5.x | Autoinc | Counter |
The Paradox field data types Graphic, Binary, and Formatted Memo are not supported by the Jet database engine and won't appear when you display external Paradox tables with the Jet database engine.
To access external databases on a network, you must (of course) have access to the server and/or share on which the external database tables are stored. The specific requirements for gaining access to a network server or share depend on your specific
network software and security schema. Part V of this book, "Multiuser and Client/Server Database Front-Ends" describes many of the issues involved in using Visual Basic and DAO in a multiuser network environment.
When opening or attaching external databases, the external database's security features (if any) are in effect. You must supply any passwords needed to gain access to the table. If the external database is one which—like Paradox—has the
capability to assign passwords with varying read-write permissions, your application is limited to the permissions granted by the password you supply. You specify the password for an external database table by including the PWD= parameter in the external
database's connection string. Refer to the preceding discussion on making the database connection for more information about connection strings. The sections at the end of this chapter, which discuss specific external databases, show examples of using the
PWD= parameter in the connection string.
In a case-sensitive search, the string "Joe" is not the same as the string "joe." In native Jet databases, you can configure each search operation individually, depending on whether or not you want that particular search to be
case-sensitive. This is not true for many external databases, such as dBASE, FoxPro, and Paradox formats. For these types of external databases, the case-sensitivity of searching operations depends on the setting for the database's collating order.
The typical default collating sequence for these databases is "ASCII." (Collating sequence is the name for the order in which text data is sorted, and it affects the results of search operations.) An ASCII collating sequence results in
case-sensitive searching.
You can specify a particular collating sequence for an external database by changing the value of the CollatingSequence entry for in the Jet\3.0\ISAM Formats key of the Windows Registry for the particular ISAM format of your external database. For
example, if you want to change the collating sequence for a Paradox 4.x external database table used by an application named PdxFrontEnd, you alter the CollatingSequence value in the following Registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\PdxFrontEnd\Jet\3.0\ISAM Formats\Paradox 4.x
To change the default CollatingSequence for all Paradox 4.x external databases, you change the CollatingSequence entry in the following Registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.0\ISAM Formats\Paradox 4.x
Table 6.6 lists the CollatingSequence values for Paradox, FoxPro, and dBASE ISAM formats, and whether or not they result in case-sensitive searches.
| CollatingSequence Value | Used With | Search Type |
| Ascii | dBASE, FoxPro, | Case-sensitive |
| Paradox | ||
| International | dBASE, FoxPro, | Not case-sensitive |
| Paradox | ||
| Norwegian-Danish | Paradox | Not case-sensitive |
| Swedish-Finnish | Paradox | Not case-sensitive |
If you make a search across several tables from different database types (such as with a multi-table query), the case-sensitivity of the search depends on the collating sequence of the database that contains the query. Also, you can't store query definitions in external databases, which means that searches that involve native Jet format tables use the collating sequence of the Jet database that contains the query.
You have two basic techniques to choose from when connecting to an external database:
There are several advantages to attaching an external database to a Jet database rather than opening the database directly. For one thing, after you have attached the external database, it appears and behaves (for the most part) like any other table in
the Jet database. Information for connecting to the external database is stored with the table definition in the Jet database, making it possible to easily open the table with a Data control or the OpenRecordset method. Although you can also use the Data
control with external database tables that you open directly, the coding to directly open an external table must always explicitly state all of the connection information. Attaching an external table directly to a Jet database may reduce the amount of code
that you have to write.
Secondly, and perhaps more importantly, attaching the external table to a Jet database yields greater overall performance for your application. You'll get the best performance from the Jet database engine when connected to external database tables if
the external table is attached to a Jet database rather than opened directly.
This chapter, and the Visual Basic online help, uses the term attached table to refer to an external database for which you have created a table definition in a Jet database. When working in Microsoft Access, such tables are more frequently referred to as linked tables.
The following sections describe the specific coding techniques to use for both attaching an external database and for opening the external database directly.
To attach an external database to a Jet database, you perform these basic steps:
After you have attached the external table to the Jet database, it remains attached until you delete the attached database's table definition. You can now use the attached table as you would any table in a Jet database. For example, you could now bind
the external table to a Data control on a form the same way you would for any other table in a Jet database, as described in Chapter 3, "Using Visual Basic's Data Control."
The following procedure demonstrates the code needed to attach an external database table (a Paradox file, in this case) to a Jet database:
Sub AttachExternalParadoxTable()
Dim JetDB As Database
Dim extTable As TableDef
'open the Jet database to which external table is to be attached
Set JetDB = DBEngine.Workspaces(0).OpenDatabase("C:\data\attachments.mdb")
'create a table definition for the attached external table
Set extTable = JetDB.CreateTableDef("External Pdx Table")
'set the connection string and source table name
extTable.Connect = "Paradox 5.x;DATABASE=C:\PDOXDATA"
extTable.SourceTableName = "publshr.db"
'add the table definition to the database's collection of table definitions
JetDB.TableDefs.Append extTable
JetDB.Close
End Sub
The next sample procedure shows how to use all of the CreateTableDef method's arguments to create a new table definition for the attached table and specify the connection information at the same time:
Sub AttachExternalParadoxTable()
Dim JetDB As Database
Dim extTable As TableDef
'open the Jet database to which external table is to be attached
Set JetDB = DBEngine.Workspaces(0).OpenDatabase("C:\data\attachments.mdb")
'create a table definition for the attached external table
Set extTable = JetDB.CreateTableDef( _
Name:="External Pdx Table", _
Attributes:=0, _
SourceTableName:="publshr.db", _
Connect:="Paradox 5.x;DATABASE=C:\PDOXDATA;PWD=pdoxsecure")
'add the table definition to the database's collection of table definitions
JetDB.TableDefs.Append extTable
JetDB.Close
End Sub
In the preceding example, notice that the Connect argument to the CreateTableDef method includes a PWD= parameter, as well as a DATABASE= parameter. This second example assumes that the external Paradox table is password-protected, and that the
password is "pdoxsecure."
To use a Data control with an attached database, use the procedures and techniques described in Chapter 3. When specifying the Connect property for the Data control (either in code or manually in the Properties dialog), use Access, or leave it blank. The Jet engine transparently uses the connection information for the attached table to select the correct database type and ISAM format for the external data.
If an external database attached to a Jet database is moved to a different directory and/or disk drive, the connection information stored in the Jet database for the external database table longer is valid—future attempts to open the attached
table result in a variety of errors.
To solve this problem, you must refresh the connection information for the attached database. To refresh the connection information for an attached database table, you must perform two steps:
As an example, assume that the Paradox table used in the preceding two code examples is moved from the C:\Pdoxdata directory to a directory named D:\Pdx5Data. To refresh this external table's link to the Jet database, you use the following code
statements (JetDB is an object variable set to point to the opened Jet database that contains the attached table):
With JetDB.TableDefs("External Pdx Table")
.Connect = "Paradox 5.x;DATABASE=C:\PDX5DATA"
.TableDefs.RefreshLink
End With
The preceding code fragment first changes the Connect string to reflect the new connection information and then executes the RefreshLink method to actually update the connection.
Similarly, you need to update an attached database's SourceTableName property if the external database file has been renamed. As when updating an attached table's connection information, you simply assign the new table name to the SourceTableName
property of the attached table's TableDef object and then execute the RefreshLink method.
You may occasionally need to remove an attached table from the Jet database you have attached it to. To remove an attached table from a Jet database, use the Delete method of the TableDefs collection, the same way you would for any other table in a Jet
database. The following code fragment removes an attached table (named "External Pdx Table") from a Jet database (JetDB is an object variable set to point to the opened Jet database that contains the attached table):
JetDB.TableDefs.Delete "External Pdx Table"
When you delete the TableDef object for an attached database, only the table definition and connection information is deleted from the Jet database. The external database's disk files remain unaffected.
To directly open an external database, you perform two basic steps:
The following procedure demonstrates the code necessary to directly open a Paradox 5.0 database table and create a Recordset over it (ExtrnDB is a global object variable of the Database type, and ExtrnRSet is a global object variable of the Recordset
type):
Sub OpenExternalDatabase()
With DBEngine.Workspaces(0)
'open a database, specify database name (path) and ISAM type
Set ExtrnDB = .OpenDatabase(Name:="C:\PDOXDATA", _
Exclusive:=False, _
ReadOnly:=False, _
Connect:="Paradox 5.x;")
End With
'open a Recordset on the database, specify disk file name of external table
Set ExtrnRSet = ExtrnDB.OpenRecordset("publshr.db")
End Sub
All of the arguments for the OpenDatabase method are used in the preceding example. The external database's name is the full path in which the external database tables are stored. The database is not opened for exclusive use, rather it is opened for
editing and appending records. Notice that the Connect argument only specifies the ISAM format for this external database—the DATABASE= parameter isn't needed in the connection string because it is already supplied in the Name argument of the
OpenDatabase method. If this external database required a password, you would add the PWD= parameter to the Connect argument of the OpenDatabase method.
Only the required arguments of the OpenRecordset method are used to create the external table's Recordset object. As a result, the opened Recordset is a Dynaset-type; you can use the Type argument of the OpenRecordset method to create Table- and
Snapshot-type Recordsets, as well. Once you have created a Recordset object based on the external database, you may manipulate the Recordset the same as you would any other Recordset object.
For attached database tables, you simply set up the Data control as if the attached external table were a native Jet database table. If necessary, you can use external databases with a Data control, even if the database isn't attached to a Jet
database.
To open an external database directly with the Data control, you must follow these two steps:
You may also set the Data control's Connect, DatabaseName, and RecordSource properties in the Properties dialog of Visual Basic's design mode. Follow the procedures presented in Chapter 3 for setting these properties manually.
The following procedure shows an example of how to open an external database with the Data control (Data1 is a Data control on the current form):
Sub OpenExtrnDataControl() Data1.Connect = "Paradox 5.x;" Data1.DatabaseName = "C:\PDOXDATA" Data1.RecordSource = "publshr.db" Data1.Refresh End Sub
In the preceding example, as soon as the Refresh method is finished executing, the Data control is initialized with a Dynaset-type Recordset from the specified Paradox database. If you needed to specify a password for access to this Paradox table, you
add the PWD= parameter to the string assigned to the Connect property. With the Data control, the RecordSource property contains the actual disk file name for the external database.
Different database management systems have different features and capabilities. One of the main reasons to use external databases is so that the database can still be used by the software system that originally created it. As a result, the Jet engine
does not—in fact, cannot—perform operations on external databases that are not supported by the original database format. The range of operations that you can perform with an external database may be limited or otherwise restricted. Issues such
as database security, indexing, sorting and searching results, and so on may all be affected by the particular features or limitations of an external database's type.
You can govern certain behaviors of the Jet database engine in dealing with external databases by changing settings in the Jet\3.0\Engines and Jet\3.0\ISAM Formats keys of the Windows Registry for that specific external database. The next few sections
describe what the various Engines and ISAM Formats settings are for each external database type and also explain any special requirements you should observe when using those external database types.
The Registry settings you will most likely to need to change are in the Jet\3.0\Engines key of the Windows Registry. The Jet\3.0\Engines settings allow you to set options that govern how the connection is managed and established, as well as enabling you to set things such as the collating sequence for the external database engine. The Jet\3.0\ISAM Formats settings, on the other hand, primarily consist of information such as file-filter strings for file open dialogs, whether or not the database uses a one-table-per-file format, whether or not to display a dialog asking for index files, and so on. In general, you should not (and need not) change the Jet\3.0\ISAM Formats settings for an external database. For this reason, the following sections only include information on the Jet\3.0\Engines settings for specific external databases.
The Jet database engine considers any table not part of a specific .mdb file to be an external table—even if that table is contained in another Jet database. Use the techniques described earlier in this chapter to attach or open an external Jet
table.
When connecting to a table in another Jet database table, don't specify an ISAM type in the connection string.
You may want to attach or open an external Jet database table to look up common information, such as a department code, state abbreviation, country abbreviation, and so on. (It's usually a good idea to store lookup or validation tables used by several
different databases in a separate .mdb file and then attach the needed tables to the databases that use them; this simplifies updating the lookup/validation data.)
You may also want to connect to an external Jet database in Access 1.0, 1.1, or 2.0 format, without upgrading to the Jet 3.0 format, because these databases are still in use by the software systems that originally created them.
The settings for the Jet database engine are in the \Jet\3.0\Engines\Jet Registry hive. Table 6.7 lists the available Engines settings for the Jet database engine.
| Entry | Default Value | Purpose |
| Win32 | Determined at setup | String: Name and location of engine DLL |
| PageTimeout | 5000 (5 secs) | Integer: Interval, in milliseconds, between when data that isn't read-locked is cached and when the cached data is invalidated. |
| LockRetry | 20 | Integer: Number of times attempts to access a locked page are repeated before returning a lock conflict message. |
| MaxBufferSize | no limit | Integer: Size, in KB, of the engine's internal cache; the value you specify must be >= 18. |
| Threads | 3 | Integer: The number of background threads that the engine may create. |
| UserCommitSync | True | Boolean: Whether or not the system waits for a commit operation to finish. True tells the system to wait for a commit operation to finish; False commits asynchronously. |
| ImplicitCommitSync | False | Boolean: Whether or not the system waits for a commit to finish. True tells the system to wait for a commit operation to finish; False tells the system to proceed asynchronously. |
| ExclusiveAsyncDelay | 2000 (2 secs) | Integer: Length of wait, in milliseconds, before asynchronously flushing data in a database opened for exclusive access. |
| SharedAsyncDelay | 0 | Integer: Length of wait, in milliseconds, before asynchronously flushing a shared database. |
| SystemDB | Determined at setup | String: Full path and filename of the system database. |
Paradox stores the data for Memo fields in a separate file from the other data in the table. The memo database file must be in the same directory as the main database file in order to make the memo data available. If the memo file is missing, Jet
cannot open the Paradox table.
Paradox stores primary key index information in a separate file, which has the same name as the database file, with a .px file type. For example, if you have a Paradox table named Publshr.db that has a primary key index, the index file is named
Publshr.px. All Paradox tables that you open with the Jet database engine must have a primary key index, and the .px file must reside in the same directory that contains the .db database file. If the .px file is deleted or moved to a different directory,
you won't be able to open the Paradox table. Paradox primary index files can only be created through the original Paradox software that created the database table; if a Paradox table has a .px index file, the Jet database engine is capable of automatically
opening and updating the existing index file.
You can open Paradox database tables that don't have primary key indexes only if you open the Paradox table for exclusive use. The Jet database engine prohibits you from altering records in a Paradox database table that does not have a primary key index.
As this edition of the Database Developer's Guide was being written, Borland had just announced beta shipment of Paradox for Windows 7.0. Although it seems likely that the Paradox 7.0 file format will be the same format used in Paradox 5.0, it is not certain. If the file formats remain the same, you can connect to Paradox 7.0 external tables using the Paradox 5.x ISAM format; otherwise, you must obtain a third-party driver from a company such as Intersolv.
When connecting to Paradox tables that are stored on a network server and shared by more than one user, you must set the ParadoxNetPath entry in the Engines\Paradox hive of the Windows Registry. The ParadoxNetPath entry contains the
full path to the Paradox.net file or the Pdoxusrs.net file. The Paradox.net file is used in Paradox versions 3.x, and the Pdoxusrs.net file is used in Paradox versions 4.x and 5.x. Paradox uses the .net files to keep track of record
and file locks.
The ParadoxNetPath entry must be the same for all users sharing a particular Paradox database directory. When using mapped drives, each user's drive letter and directory name must be the same for the Paradox .net file. For example, if one user's .net file is stored in F:\Pdox45, then all users' .net files must be in F:\Pdox45. Uniform Naming Convention (UNC) format (\\Servername\Sharename) is the preferred method of designating the location of the Paradox .net file.
The settings for the Paradox database engine are in the \Jet\3.0\Engines\Paradox Registry hive. Table 6.8 lists the available Engines settings for the Paradox database engine.
| Entry | Default Value | Purpose |
| Win32 | Determined at setup | String: Name and location of engine DLL |
| PageTimeout | 600 (60 secs) | Integer: Interval, in 100 millisecond increments, between the time when data is cached internally and the time it is invalidated. |
| CollatingSequence | ASCII | String: Collating sequence for all Paradox tables opened by Jet. Permissible values are ASCII, International, Norwegian-Danish, and Swedish-Finnish. |
| DataCodePage | OEM | String: Determines how text pages are stored. Permissible values are OEM and ANSI. The OEM setting permits OEM to ANSI conversions and ANSI to OEM conversions. The ANSI setting prohibits conversions. |
| ParadoxUserName | Jet user's name | String: The name displayed by Paradox when reporting incompatible locks. This entry is not set for computers that aren't networked. If you include this entry, you must also specify the ParadoxNetPath and ParadoxNetStyle. |
| ParadoxNetPath | Depends on system configuration | String: Full path to directory containing the Paradox.net or Pdoxusrs.net file. Not present if computer isn't networked. |
| ParadoxNetStyle | 3.x | String: Specifies the network sharing and locking style for Paradox tables. Not present if computer isn't on a network. This entry must be consistent for all users sharing a particular database directory. Permissible entries are 3.x and 4.x. Paradox 5 databases use the 4.x net style. |
The CollatingSequence setting must match the setting used when the Paradox table was originally created.
Both dBASE and FoxPro external databases use essentially the same database file format and structure. As a result, Jet is able to handle all of the supported dBASE and FoxPro external databases through a single database engine—the xBase engine.
The following paragraphs describe the considerations you need to observe when working with xBase external data.
FoxPro 3.0 tables stored in a FoxPro 3.0 database container are not supported by the Jet xBase engine. You cannot open or attach an external database table if it is stored in a FoxPro 3.0 container.
In most database systems, deleted records are physically removed from the database file. In xBase databases, however, deleted records are flagged to indicate that they are deleted, but they remain physically in the database file until the file is
"packed." Packing an xBase database file removes the records marked as deleted. It is not possible to pack an xBase database file with Jet database methods—xBase database files can only be packed by the software that created the xBase data
file.
Whether or not deleted records are included in Jet Recordsets is determined by the Deleted entry in the xBase engine settings in the Windows Registry. Setting the Deleted entry to True (a value of 1) causes Jet to exclude deleted xBase records from the
Recordset. Setting Deleted to False (a value of 0) causes Jet to include all records, even those flagged as deleted, in the Recordset. The Deleted setting values correspond to the dBASE and FoxPro SET DELETED ON and SET DELETED OFF.
When you delete a record in an xBase external file attached to a Jet database (or opened as an external database in Jet), the record is flagged as deleted and is then dropped from the Recordset if Deleted is True. (Table 6.9 lists all of the available
Registry settings for the xBase engine.)
dBASE databases use separate files to store their indexes, as do FoxPro databases. dBASE index files all end with the file extension .ndx or .mdx. FoxPro index files all end with the file extension .idx or .cdx. When you open or attach an external
xBase table, you can tell the Jet engine to use one or more of the available index files to improve performance. In fact, if you modify records in the xBase data files, you must tell Jet which index files to use in order to keep the external xBase indexes
up to date.
All versions of dBASE and FoxPro that support Memo field types store the memo data in a file separate from the primary database file. For all xBase databases with memo fields, the memo database file must be in the same directory as the main database file, or Jet won't be able to open the xBase data file.
Jet uses a special configuration file to get information about which external index files belong to a particular xBase database file. The index information file must have the same name as the external database you open or attach, and must end with the
file extension .inf. If you use Microsoft Access to interactively attach an xBase table, Microsoft Access prompts you to identify the index files and unique indexes for the xBase table and then creates the .inf index information file for you. Otherwise,
you must manually create the index .inf file using a text editor such as Windows Notepad. The .inf file should reside in the same directory as the external xBase database file, unless you open the xBase table as a read-only share, in which case you can
specify the location of the .inf file by setting the INFPath entry in the Windows Registry settings for the xBase engine.
Each entry in the index .inf file contains an index identifier followed by an equals sign (=) and an index filename. The index identifiers for dBASE III files take the form NDXn, where n is a unique number for that entry. dBASE IV index
identifiers take the form NDXn or MDXn. For FoxPro index files, the index identifier takes either the form CDXn or IDXn (it doesn't matter which you use). For all versions of both dBASE and FoxPro indexes, the unique index
identifiers have the form UIDXn.
The Jet xBase engine does not require the presence of an index information file. If you don't create an .inf file, Jet is still able to open the xBase data file, but Jet does not keep the index files current. This might cause serious problems—if the index files are not kept current, the results of subsequent sorts and searches in the xBase data using the out-of-date index files are unpredictable and erroneous. It is your responsibility to ensure that you specify all index files that must be kept current in an .inf file.
The following listing shows a sample .inf file for a dBASE III file that has a unique index for a field named PUBID and has non-unique indexes for a NAME field and ZIP field:
[dBASE III] NDX1=Name.ndx NDX2=Zip.ndx UIDX1=Pubid.ndx
If the dBASE III database file is named Publshr.dbf, then the preceding .inf file must be named Publshr.inf; otherwise, Jet won't be able to associate the .inf file with the correct database.
The next listing shows a sample .inf file for a FoxPro 2.0 database that also has a unique index for a field named PUBID and non-unique indexes for NAME and ZIP:
[FoxPro 2.0] CDX1=Name.cdx CDX2=Zip.cdx UIDX1=Pubid.cdx
As with dBASE, if the FoxPro 2.0 database file is named Publshr.dbf, then the preceding .inf file must be named Publshr.inf; otherwise, Jet won't be able to associate the .inf file with the correct database.
Clipper, made by Nantucket software, is an xBase-compatible database system. Clipper enables developers to create programs written in a dialect of the original dBASE programming language and then compile those programs into stand-alone executable files. Clipper applications use a proprietary xBase index format; index files for xBase databases have the .ntx file extension. Although you can open Clipper xBase files (usually as a dBASE III ISAM format) with the Jet engine, Jet is not able to maintain Clipper's .ntx index files. If you need to manipulate Clipper xBase files and keep their indexes current, you must use a third-party Jet engine driver from a company such as Intersolv. Third-party xBase drivers may require you to configure the external database source as an ODBC connection. Refer to Chapter 19 for more information on ODBC databases.
The settings for the xBase database engine are in the \Jet\3.0\Engines\xBase Registry hive. Table 6.9 lists the available Engines settings for the xBase database engine.
| Entry | Default Value | Purpose |
| Win32 | Determined at setup | String: Name and location of engine DLL |
| NetworkAccess | On | Boolean: Indicates file locking preference. If Off (False), files are opened for exclusive access, regardless of whether you choose exclusive access in the OpenDatabase or OpenRecordset method. |
| PageTimeout | 600 (60 secs) | Integer: Interval, in 100 millisecond increments, between the time when data is cached internally and the time it is invalidated. |
| INFPath | Not set | String: Full path to the .inf file directory. Jet first looks for .inf files in the database directory and then looks in the directory specified by this setting. |
| CollatingSequence | ASCII | String: Collating sequence for all xBase tables opened by Jet. Permissible values are ASCII or International. |
| DataCodePage | OEM | String: Determines how text pages are stored. Permissible values are OEM and ANSI. The OEM setting permits OEM to ANSI conversions and ANSI to OEM conversions. The ANSI setting prohibits conversions. |
| Deleted | Off | Boolean: Indicates whether records marked for deletion are included in the Jet Recordset. False (Off) includes all xBase records, including those flagged as deleted. True (On) excludes records marked as deleted. |
| Century | Off | Boolean: Selects the formatting for the century component of dates when date-to-string functions are used in index expressions. True (On) corresponds to the xBase SET CENTURY ON command; False (Off) corresponds to the xBase SET CENTURY OFF command. |
| Date | American | String: Selects the date formatting style when date-to-string functions are used in index expressions. Permissible settings are American, ANSI, British, French, DMY, German, Italian, Japan, MMDY, USA, and YMD. |
| Mark | Varies | Integer: Decimal value of ASCII character used as a date separator. Default value depends on the Date setting: "/" for American, British, French, Japan, MDY, DMY, or YMD; "." for ANSI or German; "-" for USA or Italian. |
| Exact | Off | Boolean: Determines type of string comparisons and corresponds to the xBase SET EXACT command. Permissible values are False (Off) or True (On). |
Although Microsoft Jet uses different engines for connecting to external data in Lotus and Excel worksheet files, there are many similarities between the two. In both Lotus and Excel, you can access an entire worksheet, a named range of cells within a
worksheet, or an unnamed range of cells specified with row and column coordinates. For the Lotus and Excel file formats that support multiple worksheets in the same file, you can access specific worksheets in the multi-worksheet file. (Lotus traditionally
uses the term spreadsheet to refer to what is known in Excel as a worksheet; this discussion uses the term worksheet for both.)
When specifying the database name for Lotus or Excel files, you must include the full path and the name of the Lotus or Excel file, including the file extension—unlike the database name for Paradox, dBASE, or FoxPro databases, where the
database name is merely the directory path in which the external files are stored. The following code fragment shows an example of opening an Excel workbook; notice that the Name argument, which establishes the database's name, includes the actual Excel
workbook's filename and extension:
With DBEngine.Workspaces(0)
'open a database, specify database name (path) and ISAM type
Set ExtrnDB = .OpenDatabase(Name:="C:\XLDATA\Orders.xls", _
Exclusive:=False, _
ReadOnly:=False, _
Connect:="Excel 5.0;")
End With
After opening the Lotus or Excel file, you may attach it to a Jet database or create a Recordset directly from the Lotus or Excel file—the same as you would for any external database file. Whichever you choose to do, you must specify the record
source for the OpenRecordSet method or for the CreateTableDef method.
To use an entire Lotus WKS, Lotus WK1, Excel 3.0, or Excel 4.0 worksheet as the record source, specify the full directory path, filename, and extension. You must separate the filename and extension with a pound sign (#). The following code fragment
shows the OpenRecordSet method used to create a Recordset from an Excel 3.0 workbook named Orders.xls:
Set ExtrnRSet = ExtrnDB.OpenRecordset("C:\XLDATA\Orders#xls")
Lotus WKS, Lotus WK1, Excel 3.0, and Excel 4.0 files each contain only a single worksheet. You cannot select individual worksheets in these file formats, although you can specify a named range of cells or an unnamed range specified by row and column coordinates.
To reference a single sheet in a Lotus WK3 or Lotus WK4 file, use the sheet name followed by a colon (:)—for example, Sheet1:. To reference a single sheet in an Excel 5.0 or Excel 7.0 file, also use the sheet name, but follow it with a dollar sign
($)—for example, Sheet1$.
Microsoft Jet does not recognize the exclamation mark (!) as a sheet name separator for Excel workbook files. Use the dollar sign ($) separator, instead.
To use a named range of cells as the record source, you must have first named the range of cells in the Lotus or Excel worksheet. Refer to your Lotus or Excel documentation for instructions on creating named ranges. Simply specify the name of the data
range as the record source. Neither the Lotus nor the Excel engines permit you to reference individual fields and records in a data table. The following code fragment shows a named range ("DataRange") used as the source table name with the
CreateTableDef method:
Set extTable = JetDB.CreateTableDef( _
Name:="External Excel Table", _
Attributes:=0, _
SourceTableName:="DataRange", _
Connect:="Excel 5.0;DATABASE=C:\XLDATA\Orders.xls")
To reference an arbitrary range in either a Lotus or Excel worksheet, you define the range by specifying the row and column coordinates for the top-left corner of the range and for the bottom-right corner of the range. In Lotus, you separate the range
coordinates with a pair of periods: B1..C12. In Excel, you separate the range coordinates with a colon: B1:C12. In a file with multiple worksheets, combine the worksheet specification with the range definition to identify the record source or source table
name. In a Lotus file, for example, you use this notation: Sheet1:B1..C12. For an Excel workbook, you use Sheet1$B1:C12.
You should be aware of the following points regarding Lotus and Excel files opened with the Jet database engine:
The settings for the Lotus database engine are in the \Jet\3.0\Engines\Lotus Registry hive; settings for the Excel database engine are in the \Jet\3.0\Engines\Excel Registry hive. Both the Lotus and Excel engines have the same range of available
Engines settings, as listed in Table 6.10.
| Entry | Default Value | Purpose |
| Win32 | Determined at setup | String: Name and location of engineDLL. |
| TypeGuessRows | 8 | Integer: The number of rows to be checked when trying to determine the data type for columns. |
| ImportMixedTypes | Text | String: Determines whether mixed data types are imported as text or cast to the predominate data type in the column. Permissible values are Text or MajorityType. |
| AppendBlankRows | 0 | Integer: The number of blank rows to appended to the end of the worksheet before new data is added. Permissible values for this setting are integers between 0 and 16. |
| FirstRowHasNames | Yes | Boolean: Indicates whether the first row of the worksheet contains field names. Permissible values are Yes (True) and No (False). |
The Jet database engine provides the ability to open text files as an external database to make it easier for you to import or export data. In general, you should never try to use a text file as permanent external data source.
Text files are always opened for exclusive use. The Jet engine can read both delimited and fixed-width text files. Any single file must use a consistent format and/or delimiter. In delimited text files, two consecutive delimiters denote a Null
value. As an example, if your field delimiter is a comma (,) then two consecutive commas (,,) indicate a Null value in that field. In fixed-width text files, Null values are indicated by a field occupied solely by space characters.
In general, text files are limited to a total of 64K bytes in each row. Individual fields in the row may be up to 64K bytes in length, provided the total size of all fields does not exceed the 64K row limit. Text files may not have more than 256
fields, and field names may not exceed 64 characters in length.
For fixed-width files, you may want to use a Schema.ini file to specify the format of the data in the file. The following listing shows a sample Schema.ini file containing entries for a text filename Stocks4.csv:
[stocks4.csv] ColNameHeader=True Format=CSVDelimited MaxScanRows=25 CharacterSet=ANSI Col1=KEY Char Width 5 Col2=DATE Date(dd-mmm-yy) Width 9 Col3=HIGH Float Width 6 Col4=LOW Float Width 6 Col5=CLOSE Float Width 6 Col6=VOLUME Char Width 9
The Schema.ini file may contain one than more schema definitions—just start each new definition section with the file or table name enclosed in square brackets. The Schema.ini file determines the text file format for files you open or attach, and
for files you export. Search the Visual Basic online help for more information about Schema.ini settings.
The settings for the Text database engine are in the \Jet\3.0\Engines\Text Registry hive. Table 6.11 lists the available Engines settings for the Text database engine.
| Entry | Default Value | Purpose |
| Win32 | Determined at setup | String: Name and location of the engine DLL. |
| MaxScanRows | 19 | Integer: Number of rows to scan when guessing column types. If this value is 0, the entire file is searched. |
| FirstRowHasNames | Yes | Boolean: Indicates whether the first row of the text file contains the column names. |
| CharacterSet | OEM | String: Indicates whether the text file is written using an OEM or ANSI character set. Permissible values are OEM or ANSI. |
| Format | CSVDelimited | String: Specifies the delimiter character for text files. Permissible values are TabDelimited, CSVDelimited, or Delimited, where c represents the delimiter character. You may use any delimiter character except double quotes ("). |
| Extensions | "txt, csc, tab, asc" | String: A file filter string specifying the file extensions to look for when browsing for text data files. |
| ExportCurrencySymbols | Yes | Boolean: Indicates whether to include currency symbols when currency fields are exported. |
This chapter explained some of the reasons you might need or want to open or attach an external database table, and then explained which external database formats the Microsoft Jet database engine supports. Next, this chapter explained the fundamental
operation behind external database connections, including the various Windows 95 Registry settings used to configure the Jet engine drivers for external data tables. Some of the special issues involved in connecting to external data were then discussed,
including data mapping, case-sensitive searching, and Jet objects and methods not supported on external databases.
Next, you learned how to attach an external database to a Jet database, and you learned how to directly open external data files and create Recordsets from them. You also learned how to open an external database with a Data control. This chapter
concluded with a discussion of special topics related to particular databases and enumerated the various engine configuration settings for each supported engine type.
The next chapter, "Running Crosstab and Action Queries" deals with advanced SQL techniques you use to transform time-series and similar data from row-column to column-row format. It also shows you how to write SQL statements in Visual Basic
4.0 code to append, update, and delete records from database tables.
