Jeff Webb, Mike McKelvy, Ronald Martinsen, Taylor Maxwell, Michael Regelski September 1995 Special Edition Using Visual Basic 4 - Chapter 8 1-56529-998-1 Computer Programming computer programming Visual Basic OLE database applications ODBC VB VBA API This book is an all-in-one reference that provides extensive coverage of every topic and technique for creating optimized and customized applications with Visual Basic.

Chapter 8

Accessing Other Databases with the Jet Engine

Very often, database systems do not present data to application developers in their "home" or "native" format. Often, your application uses one DBMS (Database Management System) and the information your application needs resides in another DBMS. When designing database systems, there are two likely scenarios that can occur:

Each of these situations demands different techniques that fortunately can be addressed with Visual Basic�without losing a lot of sleep in the process.

In this chapter, you learn the following:

Examining Jet Engine Architecture

The Jet engine was developed by Microsoft to provide all of the database connectivity features for Visual Basic. It originally previewed in Access 1.0. In Visual Basic 4, the Jet engine is comprised of six main Dynamic Link Libraries (*.DLL): MSAJT200.DLL, MSJETERR.DLL, MSJETINT.DLL, VBAJET.DLL, VBDB16.DLL and DOA2516.DLL. A variety of other DLLs are shipped with Visual Basic; these DLLs are libraries used for external database support and ODBC support. There is a separate DLL for each database type. Table 8.1 shows the DLLs used for each database type.

Table 8.1 DLLs Used for Each Database Type

Database Type DLL
FoxPro, dBASE III, dBASE IV XBS200.DLL
Paradox PDX200.DLL
Btrieve BTRV200.DLL
Excel spreadsheet MSXL2016.DLL
Delimited and fixed-length text MSTX2016.DLL
ODBC Database Drivers ODBC.DLL, ODBCCURS.DLL, ODBCINST.DLL

Visual Basic is a Windows executable which, like may Windows applications, is comprised of a core executable (*.EXE) and a set of DLLs (*.DLL). A DLL is a code library that contains a set of API (Application Programming Interface) functions. Visual Basic 4 uses Visual Basic for Applications (VBA) as the language interpreter. VBA is the programming language used across all Microsoft products including Microsoft Excel and Microsoft Word. VBA allows for multienvironment usage of Visual Basic programming modules.

The main Visual Basic DLL is VBA2.DLL, the Visual Basic for Applications DLL. When a Visual Basic program issues a Database API call, it is through a new object-oriented interface. This interface is called the Data Access Object (DAO) and is a set of OLE Automation objects that Visual Basic uses for data access. The Data Access Object is contained in the DLL DAO2516.DLL.

When the Data Access Object is called from a Visual Basic program, many DLLs are used in the process. Figure 8.1 shows the succession of events that occur when your application calls one of these APIs.

Fig. 8.1

DLLs used when accessing the Data Access Object.

The following code fragment is used to illustrate the succession of DLLs that are called when using Visual Basic's Data Access Object. The first line of code declares an object variable of type Database. The second line calls the OpenDatabase method of the DBEngine object.

DIM db as Database
Set db = DbEngine.Workspaces(0).OpenDatabase( "C:\path\mydb.dbf" ,
False , False , "dBase IV" )

The following list explains what happens when the OpenDatabase call is issued from a Visual Basic application.

Visual Basic 4 has a 16-bit version and a 32-bit version of the development environment. Visual Basic allows for the same code base to be used by each version, but the underlying DLLs that are used are different. Table 8.2 is a comparison of the Visual Basic 16 bit and 32-bit DLLs.

Table 8.2 Jet 2.0 16-bit DLLs versus Jet 2.0 32-bit DLLs

Jet Engine 2.0 16-bit Jet Engine 2.0 32-bit Description
VBAJET.DLL VBAJET32.DLL Expression evaluation services for Jet engine
MSAJT200.DLL MSJT3032.DLL Jet database engine
XBS200.DLL MSXB3032.DLL FoxPro/dBASE ISAM driver
PDX200.DLL MSPX3032.DLL Paradox ISAM driver library
BTRV200.DLL
Btrieve ISAM driver library
DAO2516.DLL DAO3032.DLL MS data access object library
MSJETERR.DLL MSJTER32.DLL MS Jet error library
VBDB16.DLL VBDB32.DLL OLE to DOA implementation DLL

The Jet 2.0 engine provides a host of new functionality to Visual Basic developers. Here are some of the highlights of the Jet 2.0 engine:

Importing External Data

Leveraging external data is very important not only to application developers but to your customers. If you are developing a custom or vertical-market application, leveraging external data is essential because most applications of this type start with a populated database with which you must preload your system with or that you must access. Even shrink-wrapped products offer their users a tremendous benefit by leveraging external data. Users do not like to re-enter information; your users will be very appreciative if they can import or attach to the information they need.

This section covers "importing" external data and "attaching" to external data. A sample application is developed to provide examples of both attaching to and importing external data.


When importing external data, there are some issues that must be addressed to maintain the integrity of the data. Data verification is essential. Does the database being imported contain field types that are not supported by my database? Is importing the data the correct choice for my application, or should I attach to the table instead?

Imported Data Verification and Manipulation

Depending on the version of the Jet engine you are using, data verification used to be a difficult task. Earlier versions of the Jet engine recognized only default relationships and referential integrity rules for Access databases. These rules could not be established through the Data Definition Language (DDL). These rules could be set only when creating the Access database with Access. Additionally, Version 1.1 of the Jet engine had no support for table-level validation.

Visual Basic 4 greatly enhances the programmer's data validation and manipulation capabilities. The Data Definition Language (DDL) provides Visual Basic with the ability to create databases, tables, fields, queries, and indexes. The Data Definition Language has added creating and editing of referential integrity and security objects also.

Jet 2.0 adds some additional enhancements for Visual Basic programmers: Table-level validation is supported as are cascading updates and deletes. These enhancements are available to the Visual Basic programmer during database construction with Access 2.0 or the Data Manager add-on tool. The enhancements can also be achieved programatically.

When you are importing data or attaching to external databases, it is extremely important that you know the relationships that exist among the tables and the fields. Not knowing this information increases the chances of data corruption when manipulating the database.

When to Import and When to Attach

When should you import data and when should you attach to external tables? This answer to this question is often complex. The responses you make to the next few questions can help you arrive at the correct answer for your application:

If your application is going to be the only application to access and manipulate the data, importing the data is probably the correct choice. The Jet engine is optimized to work with native database formats as opposed to foreign formats. However, if the data resides in a database on the network, and the data is shared by many applications, attaching to the database is probably the better choice.

Importing data from external databases is not always a trivial task. Not all database formats support the same field types as others (an example of this is the Binary Large Object�BLOB). Importing data can result in the loss of information contained in the original database. If you attach to the database, your application still cannot access these fields, but it won't lose the data either.

When importing data, it is important to know what field types are in both the source and destination tables. This is necessary because the source and destination databases may not support all of the same field types. You need to know these two pieces of information when you're designing your application. If the same field types are not supported, importing the data may not be the correct solution.

If your application does not need to contain all the fields contained in a source database, you can populate your application�s database by importing the relevant information from the source database.

Relevant Functions and Commands

Visual Basic provides two sets of objects in the Jet engine: the DDL group (Data Definition Language, which deals with defining and creating a database) and the DML group (Data Manipulation Language). Visual Basic offers methods of programatically adding table-level validation rules and enforcement. This chapter does not use the DDL to create a database for import; it assumes that you have already defined the database definition of the destination (import) database and that any table-level validation rules were added at table creation.

The following Data Manipulation Objects listed in table 8.3 are used when importing data.

Table 8.3 Database Objects Used to Import External Data

Object Definition Creation Functions
workspace A container that holds a collection of databases. CreateWorkspace
database A container or structure that holds all the information of a database. CreateDatabase
OpenDatabase
recordset
(type table)		 A structure that holds data in a database. This object organizes the data into rows and columns. A table is a type of recordset, which in turn is a group of records. OpenRecordset
recordset
(type dynaset)		 A group of records that is a result of a query. This object can have updatable records. This is a dynamic image of a set of records. OpenRecordset
recordset
(type snapshot)		 This is similar to a dynaset object. It is a group of records created through the result of a query. Unlike a dynaset, this object contains a static image of the records and is not updatable. OpenRecordset
QueryDef This object stores a SQL-like statement in the database. It is used to indicate the table values used for searching database tables. CreateQueryDef
OpenQueryDef

To demonstrate the use of the database objects indicated in table 8.2, this chapter uses a simple address-book database application. The database layout is listed in table 8.4 (the database layout was created in Microsoft Access).

Table 8.4 Database Layout for Address Book Application

Field Name Data Type Length Indexed
Name Text 64 Yes
Street Test 100 No
Street2 Text 100 No
City Text 32 No
State Text 2 No
Zip Text 10 No
Phone Text 20 No
WorkPhone Text 20 No
Fax Text 20 No

The address book application was designed to keep track of names, addresses, and phone information about people and companies. This application demonstrates the following concepts:

How to update both the native database and the attached database with imported data.

How to search for information from both the native and attached database.

Figure 8.2 shows the main screen of the address book application.

Fig. 8.2

The main screen of the Address Book application.

The application's database contains one table, the Address table. This table has one primary key, Name. The application also attaches a dBASE IV table, AddInfo, to the Address database. The AddInfo table contains a Memo field for additional information about the name of the entry. The AddInfo table also has a Name field, which is identical to the Name field of the Address table.

The FormLoad function performs two services. The first is to open the Address database for the application and create a recordset object of type table for adding records. The second service is to attach the AddInfo dBASE table to the open database and create a recordset object of type dynaset for adding information to the AddInfo table. Notice that on FormUnload, the AddInfo table is detached from the Address database. If the table wasn't detached, a run-time error would occur in Visual Basic the next time the application ran because the table would already be attached.

When adding a record (Add_Click), the AddressCtl control array copies values from the screen to the Address table. A new record is then added. The screen value in the Additonal Information field of the AddressCtl control array is copied to the attached recordset object in the AddInfo table and a new record is added.

To search a record, a SQL statement is generated to create a greater-than-or-equal-to search on the value in the Name field. A global recordset object (SearchSet) is created from this query. The subroutine FillFormFromRecord is then called. This subroutine fills the screen with the recordset record information. This subroutine also creates an additional recordset object on the attached table, based on an equal-to search on the Name field. If a value is found, the Notes field on the screen is updated.

When the application imports data from dBASE IV, information is added to both the Address table and the attached AddInfo table. The import table contains the same fields as the Address table as well as the Notes field contained in the AddInfo table. The Import_Click subroutine opens the external database and creates a recordset of all records in the Address table. Notice that the same global recordset object (SearchSet) is used so that the application can call the subroutines already provided for adding records and searching.

The code for the address book application is given in listing 8.1.

Listing 8.1 The Address Book Application

' Since all fields are in a control array
' I will setup defines for accessing them
Const NAME_FLD = 1
Const STREET_FLD = 2
Const STREET2_FLD = 3
Const CITY_FLD = 4
Const STATE_FLD = 5
Const ZIP_FLD = 6
Const PHONE_FLD = 7
Const WORK_FLD = 8
Const FAX_FLD = 9
Const NOTES_FLD = 10
Const FIRST_FLD = NAME_FLD
Const LAST_FLD = NOTES_FLD
' Objects and variables used in this module
Dim Ws As Workspace
Dim Db As Database
Dim Tbl As Recordset
Dim SearchSet As Recordset
Dim MemoSet As Recordset
Dim AddInfoAdd As Recordset
Dim SrchValue As String
Sub Add_Click ()
If Not (AddressCtl(NAME_FLD) = "") Then
Tbl.AddNew
Tbl.Fields("Name").Value = AddressCtl(NAME_FLD)
Tbl.Fields("Street").Value = AddressCtl(STREET_FLD)
Tbl.Fields("Street2").Value = AddressCtl(STREET2_FLD)
Tbl.Fields("City").Value = AddressCtl(CITY_FLD)
Tbl.Fields("State").Value = AddressCtl(STATE_FLD)
Tbl.Fields("ZipCode").Value = AddressCtl(ZIP_FLD)
Tbl.Fields("Phone").Value = AddressCtl(PHONE_FLD)
Tbl.Fields("WorkPhone").Value = AddressCtl(WORK_FLD)
Tbl.Fields("Fax").Value = AddressCtl(FAX_FLD)
Tbl.Update
'Tbl.Fields("Notes").Value = AddressCtl(NOTES_FLD)
'Tbl.Update
AddInfoAdd.AddNew
AddInfoAdd.Fields("Name").Value = AddressCtl(NAME_FLD)
AddInfoAdd.Fields("Notes").Value = AddressCtl(NOTES_FLD)
AddInfoAdd.Update
End If
End Sub
Sub Command1_Click ()
Dim i As Integer
For i = FIRST_FLD To LAST_FLD Step 1
AddressCtl(i) = ""
Next i
End Sub
Sub Delete_Click ()
If Not SearchSet.NoMatch Then
SearchSet.Delete
If Not MemoSet.NoMatch Then
MemoSet.Delete
End If
Command1_Click
FindFirst_Click
End If
End Sub
Sub FillFormfromImport ()
If Not SearchSet.NoMatch Then
AddressCtl(NAME_FLD) = ValidateRecordField("Name")
AddressCtl(STREET_FLD) = ValidateRecordField("Street")
AddressCtl(STREET2_FLD) = ValidateRecordField("Street2")
AddressCtl(CITY_FLD) = ValidateRecordField("City")
AddressCtl(STATE_FLD) = ValidateRecordField("State")
AddressCtl(ZIP_FLD) = ValidateRecordField("ZipCode")
AddressCtl(PHONE_FLD) = ValidateRecordField("Phone")
AddressCtl(WORK_FLD) = ValidateRecordField("WorkPhone")
AddressCtl(FAX_FLD) = ValidateRecordField("Fax")
AddressCtl(NOTES_FLD) = ValidateRecordField("Notes")
End If
End Sub
Sub FillFormfromRecord ()
If Not SearchSet.NoMatch Then
AddressCtl(NAME_FLD) = ValidateRecordField("Name")
AddressCtl(STREET_FLD) = ValidateRecordField("Street")
AddressCtl(STREET2_FLD) = ValidateRecordField("Street2")
AddressCtl(CITY_FLD) = ValidateRecordField("City")
AddressCtl(STATE_FLD) = ValidateRecordField("State")
AddressCtl(ZIP_FLD) = ValidateRecordField("ZipCode")
AddressCtl(PHONE_FLD) = ValidateRecordField("Phone")
AddressCtl(WORK_FLD) = ValidateRecordField("WorkPhone")
AddressCtl(FAX_FLD) = ValidateRecordField("Fax")
Dim AttachStatement As String
AttachStatement = "SELECT * FROM Addinfo WHERE Name = '" +
AddressCtl(NAME_FLD) + "'"
Set MemoSet = Db.OpenRecordset(AttachStatement, dbOpenDynaset)
If MemoSet.BOF = False Then
If MemoSet.Fields("Notes").Value > "" Then
AddressCtl(NOTES_FLD) = MemoSet.Fields("Notes").Value
Else
AddressCtl(NOTES_FLD) = ""
End If
End If
End If
End Sub
Sub FindFirst_Click ()
Dim Statement As String
Statement = "SELECT * FROM Address WHERE Name >= '" +
AddressCtl(NAME_FLD) + "'"
Set SearchSet = Db.OpenRecordset(Statement, dbOpenDynaset)
FillFormfromRecord
End Sub
Sub FindNext_Click ()
SearchSet.FindNext "Name > ' '"
FillFormfromRecord
End Sub
Sub FindPrevious_Click ()
SearchSet.FindPrevious "Name > ' '"
FillFormfromRecord
End Sub
Sub Form_Load ()
Dim TblDef As New TableDef
' first create the default workspace
Set Ws = DBEngine.Workspaces(0)
' second open the access database
Set Db = Ws.OpenDatabase("c:\vb\address.mdb")
' now attach the dbase IV table to the open db
TblDef.Connect = "dBASE IV;DATABASE=C:\VB\ADDINFO"
TblDef.SourceTableName = "ADDINFO" ' The name of the file.
TblDef.Name = "Addinfo" ' The name in your database.
Db.TableDefs.Append TblDef ' Create the link.
' now open a table info the main database
Set Tbl = Db.OpenRecordset("Address", dbOpenTable)
' now we need to create a dynaset from the attached table
Statement = "SELECT * FROM Address WHERE Name >= '" +
AddressCtl(NAME_FLD) + "'"
Set AddInfoAdd = Db.OpenRecordset("SELECT * FROM Addinfo",
dbOpenDynaset)
End Sub
Sub Form_Unload (Cancel As Integer)
Tbl.Close
AddInfoAdd.Close
Db.TableDefs.Delete "Addinfo"
End Sub
Sub ImportDbase_Click ()
Dim dbdb As Database
Dim DTable As RecordSet
Dim Statement As String
Set dbdb = Ws.OpenDatabase("c:\vb", False, False, "dBase IV")
Set DTable = dbdb.OpenRecordSet("Address", dbOpenTable)
Statement = "SELECT * FROM Address"
Set SearchSet = dbdb.OpenRecordset(Statement, dbOpenRecordset)
While Not SearchSet.EOF
FillFormfromImport
Add_Click
Command1_Click
SearchSet.MoveNext
Wend
End Sub
Function ValidateRecordField (Field As String) As String
If SearchSet.BOF = False Then
If SearchSet.Fields(Field).Value > "" Then
ValidateRecordField = SearchSet.Fields(Field).Value
Else
ValidateRecordField = ""
End If
End If
End Function

Database and File Issues

If all databases were created equal, there would only be one database vendor and only one method of entering, searching, changing, and deleting information. There would be no reason to worry about attaching to or importing external data because the world could be viewed as one giant database with many tables.

However, this is not the case. Many database vendors and competing products give the user a variety of choices. Each database provides unique features that other databases may or may not support.

This section deals with the issues involved when importing data from different databases and file formats. This chapter assumes that you are importing data from a specified database or file format into a Jet 3.0 database file.

One element that applies to using foreign databases with the Jet engine is that either the system registration database or your APP.INI must contain a section called [Installable ISAMs]. (Note: You can view the system registration database by running the application REGEDIT. You can find the Installable_ISAM section in the following path: HKEY_LOCAL_MACHINE\software\microsoft\jet20\Installable_ISAM.) In this section, you must indicate the database engine type you want to access followed by the path. The section shown in listing 8.2 contains all the installable ISAMs for the Jet engine.

Listing 8.2 The Required [Installable ISAMs] Section in System Registraton Database or the APP.INI File

[Installable ISAMs]
dBase III=c:\windows\system\xbs200.dll
dBase IV=c:\windows\system\xbs200.dll
Paradox 4.x = c:\windows\system\pdx200.dll
Paradox 3.x = c:\windows\system\pdx200.dll
Foxpro 2.0 = c:\windows\system\xbs200.dll
Foxpro 2.5 = c:\windows\system\xbs200.dll
Btreive=c:\windows\system\btrv200.dll
Excel 5.0=c:\windows\system\msxl2016.dll
Excel 4.0=c:\windows\system\msxl2016.dll
Excel 3.0=c:\windows\system\msxl2016.dll
Text = c:\windows\system\mstx2016.dll

Note that the each entry label matches the string that is passed into the OpenDatabase function as the database type parameter.

Databases That Use Jet 3.0

The Jet 3.0 file format�or, more appropriately, the Access 95 database file format�is the native database format for Visual Basic 4. Users of Jet 3.0 will have no problem importing data from other Jet 3.0 database files. All the data in the database can be transferred completely.

Databases That Use Jet 2.x and Jet 1.1

Jet 3.0 can access Jet 2.x and 1.1 database file formats. All the field types supported in Jet 3.0 are supported in Jet 2.x and 1.1. This makes the job of importing data from a Jet 1.1 format easy. Just as with a Jet 3.0 format, all the data in a Jet 2.x and 1.1 database can be transferred completely.

Delimited and Fixed-Width Text Files

The Jet engine in Visual Basic 4 can open a text file as though it were a foreign database. This capability makes importing data from a text file into an existing database a simple task. Text files are accessed through the Jet engine by using MSTX2016.DLL (16-bit) or MSTX3032.DLL (32-bit).

In previous versions of Visual Basic, importing text data required writing custom code to open the text file and parse through its contents�a tedious and inflexible task. Fortunately, the Jet engine now relieves programmers from this burden.

A delimited text file contains text in which each line represents a row in a database table. Each column in the row is delimited, or separated, by a known character (such as a tab or a comma). Visual Basic recognizes tabs, commas, and custom deliminators in text files. The only limitation is that the same deliminater must be used throughout the file. Missing field values or null data is represented by two consecutive delimiters.

A fixed-width text file contains text in which each line represents a row in a database table. In contrast to a delimited text file, each column in the fixed-width text file is a fixed width. The width of each column in a row is defined in SCHEMA.INI. Null data is represented by the space character " " entered for the width of the field.

For both types of text files, the first row can contain the names of the fields that each column represents.

The following statements open a text file as a database:

Dim Db As Database
Set Db = DBEngine.Workspaces(0).OpenDatabase ("c:\path\text.txt" ,
False, False, "Text" )

When accessing text files with the Jet engine, settings in VB.INI or your APP.INI file control how the file's data is interpreted. These values are found in the [Text I-ISAM] section and are listed in table 8.5.

Table 8.5 Values in the [Text I-ISAM] Section of VB.INI or APP.INI

Section Entry Description Default Value
Extensions Indicates the files to be browsed when looking for text-based data. N/A
ColNameHeader Indicates whether the first record (row) in the file contains column names. Set to True or False. True
Format Set to one of the following:
TabDelimited, CVSDelimited, Delimited (<singlecharacter>) or Fixed Length. If singlecharacter delimited, any character except for the quotation mark (") can be used.		 N/A
MaxScanRows The number of rows to scan when guessing the column type. If 0, the entire file is scanned. N/A
CharacterSet Set to OEM or ANSI, depending on the code page of the text file. N/A

SCHEMA.INI Settings

To import or export text data with the Jet engine, you must create and specify settings in SCHEMA.INI in addition to the [Text I-ISAM] section in VB.INI or APP.INI. SCHEMA.INI instructs the Jet engine how the text file is formatted and how it is to be read at import time.

SCHEMA.INI can contain multiple sections. Each section in SCHEMA.INI represents a file name or a saved export format.

Table 8.6 shows the settings that Visual Basic uses in SCHEMA.INI.

Table 8.6 Values in SCHEMA.INI for Text Data

Section Entry Description
ColNameHeader Indicates whether the first record (row) in the file contains column names. Set to True or False.
Format Set to one of the following: TabDelimited, CVSDelimited, Delimited(<singlecharacter>) or Fixed Length. If single-character delimited, any character except for the quotation mark (") can be used.
MaxScanRows The number of rows to scan when guessing the column type. If 0, the entire file is scanned.
CharacterSet Set to OEM or ANSI, depending on the code page of the text file.
DateTimeFormat A format string used for dates and times. This entry should be specified if all date and time fields in the import or export file use the same format. If no format string is specified, the control panel short date and time options are used. Format strings are specified by indicating the number of entries for each date or time entry� for example mm-dd-yy hh:mm:ss.
CurrencySymbol Indicates the currency symbol to be used for any currency values in the text file. If no value is specified, the control panel setting is used.
CurrencyDigits Specifies the number of digits used for the fractional (change) part of currency values. If no value is specified, the control panel setting is used.
CurrencyNegativeFormat Set to one of the following values:

Value Example
0 ($1)
1 -$1
2 $-1
3 $1-
4 (1$)
5 -1$
6 1-$
7 1$-
8 -1 $
9 -$ 1
10 1 $-
11 $ 1-
12 $ -1
13 1- $
14 ($ 1)
15 (1 $)

When specifying the entry in SCHEMA.INI, only the numeric number is used. For example: CurrencyNegativeFormat=13.

If this entry is not present, the control panel setting is used.

Note: The dollar sign ($) is used here only as an example. The actual symbol used is specified with CurrencyFormat.
DecimalSymbol Indicates the single character separating the whole and fractional parts of the currency value. If not specified, the control panel setting is used.
NumberDigits Indicates the number of decimal digits in the fractional portion of the currency value. If not specified, the control panel setting is used.
NumberLeadingZeros Specifies whether decimal values less than 1 and greater than -1 should contain leading zeros. Valid values are 0 (no leading zeros) or 1.

Listing 8.3 shows the SCHEMA.INI values for the address book application.

Listing 8.3 SCHEMA.INI Values for the Address Book Application

[address.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0
CharacterSet=ANSI

Limitations on Text Data Files

Some limitations exist when you open a text data file: Data in a text file cannot be updated or deleted. You cannot build indexes on the text file either. Text files can be opened only in exclusive mode (that is, the text file cannot be shared by simultaneous users). Tables are limited to a maximum of 256 columns. The maximum length of a row is 65,536 bytes. Column widths are limited to 65,500 characters and column names are limited to 64 characters.

Microsoft Excel (2.x, 3.0, 4.0, and 5.0) Files

A spreadsheet is set up in a very similar fashion as a database table. Spreadsheets are comprised of rows and columns. The Visual Basic Jet engine allows you to import Microsoft Excel 2.x, 3.0, 4.0, and 5.0 spreadsheets.

Microsoft Excel spreadsheets can be opened in the same manner as a foreign database. The Jet engine uses MSXL2016.DLL (16-bit) or MSXL2032.DLL (32-bit) to access the Excel spreadsheet.

The following sample of code opens an Excel spreadsheet as a database.

Dim Db As Database
Set Db = DBEngine.Workspaces(0).OpenDatabase _
("C:\path\address.XLS", False, False, "Excel 5.0")

When accessing Excel files through the Jet engine, settings in VB.INI or your APP.INI file control how the file's data is interpreted. These values are found in the [Microsoft Excel ISAM] section and are listed in table 8.7.

Table 8.7 Values in VB.INI or APP.INI for Excel Spreadsheets

Section Entry Description Default Value
TypeGuessRows The number of rows to be checked for a data type. The data type is determined by the maximum number of kinds of data found. If more than one data type is found in a column, the data is determined in the following order: Number, Currency, Date, Text, Boolean. If a data type is encountered that is not the type guessed for the column, NULL is returned. N/A
AppendBlankRows Number of blank rows to be appended to the end of an Excel 3.0 or 4.0 worksheet before new data is appended. 0
FirstRowHasNames Set to Yes or No. Yes indicates that the first row contains column names. No indicates that no column names are present. N/A

The following limitations are imposed on using Excel worksheets as a database: Rows cannot be deleted from the worksheet. Data can be cleared from individual cells but formulas cannot be cleared. Indexes cannot be created on the worksheet. Excel spreadsheets are opened in exclusive mode (that is, simultaneous users of a spreadsheet are not allowed). If the Jet engine encounters encrypted data, the open or attachment operation fails.

Paradox (3.x or 4.x DB) Files

Foreign databases such as Paradox are accessed in the same manner as Access databases. The OpenDatabase function takes additional arguments so that you can specify the type of database to open. The following code opens a Paradox database:

Dim ParadoxDB As Database
Set ParadoxDB = OpenDatabase ( "C:\paradox" , False , False , _
"Paradox" )

After the database is opened, the Jet engine provides the same capabilities to Paradox as it does to an Access database.

The Jet engine still cannot open password-protected files.

Notice that you do not specify a Paradox file name in the OpenDatabase call as you do for Access. This is because Paradox maintains each table in a separate file with the file name matching the table name. Paradox maintains a primary key for each table in the database in a file with the extension PX. Remember the Address database example; in Paradox, the database for the Address table has a file called ADDRESS.DB, in which the data is stored, and an associated index file called ADDRESS.PX. The Jet engine needs the index file to open the table. If the Paradox table doesn't have a primary key for the table, the Jet engine cannot update the table. This means that only snapshot objects can be created, not dynaset objects. For the purposes of importing data, this isn't a big concern.

Data Types from Paradox to Access

When importing data, one of the greatest concerns is when different database engines use different data types. The following table indicates the field compatibility types when you import from Paradox to Access:

Paradox Data Type Paradox DB Version Jet Data Type
Alphanumeric 3.x, 4.x Text
Currency 3.x, 4.x Numeric (Double)
Date 3.x, 4.x Date/Time
Short Number 3.x, 4.x Numeric (Integer)
Number 3.x, 4.x Numeric (Double)
Memo 3.x, 4.x Memo
OLE 3.x, 4.x OLE object
Graphic 4.x Not Supported
Binary 4.x Not Supported
Formatted Memo 4.x Not Supported

When accessing a database engine like Paradox, the Jet engine uses settings contained in VB.INI (when your application is running under the control of Visual Basic) or in APP.INI (when running a compiled version of your application). (APP is replaced with the compiled name of your executable.) Jet uses a section called [Paradox ISAM] to retrieve values the Paradox database needs. Table 8.8 lists the Paradox entries you can include in this section.

Table 8.8 Values in VB.INI or APP.INI for Paradox Databases

Section Entry Description Default Value
ParadoxNetPath Specifies the path to PARADOX.NET, a file used for locking tables or records in a multiuser system. C:\
ParadoxNetStyle Specifies the locking type to use. When using Paradox 4.x files, set this entry to 4.x. 3.x
ParadoxUserName Specifies the name of the user placing a lock on a Paradox table or record. None
CollatingSequence Determines the sort sequence of text fields. ASCII
PageTimeout Specifies the length of time in tenths of a second that your application waits to move data from a table to a virtual table. 600

FoxPro (2.0, 2.5, and 2.6 DBF) and dBASE III and dBASE IV (DBF) Files

FoxPro, dBASE III, and dBASE IV are accessed by the Jet engine through the Installable ISAM driver XBS200.DLL. This driver must be listed in the [Installable ISAM] section of either VB.INI or APP.INI.

The following sample code shows how to open a FoxPro 2.5 database:

Dim Db As Database
Set Db = OpenDatabase ( "C:\foxpro" , False , False , "Foxpro 2.5" )

As with Paradox, once a FoxPro or dBASE database is opened. all Visual Basic's Data Access Objects can be used to retrieve and access data.

Even though the FoxPro and dBASE databases share the same file extension for data tables (DBF), their index extensions are different: FoxPro uses IDX and CDX; dBASE uses NDX or MDX. Like Paradox, the file name in FoxPro or dBASE is the table name with the DBF extension. If an index is present, it carries the same file name with the index extension.

If the index file is not located in the current directory, the Jet engine accesses the index's through an information file (INF). The location of this information file is obtained through the INFPath entry in the [dBase ISAM] section in VB.INI or APP.INI.


Data Types from FoxPro or dBASE to Access

To import a FoxPro or dBASE table, your application must follow these conversion rules:

FoxPro/dBASE Data Type Version Jet Data Type
Character FoxPro 2.x, dBASE III, dBASE IV Text
Numeric FoxPro 2.x, dBASE III, dBASE IV Numeric (Double)
Logical FoxPro 2.x, dBASE III, dBASE IV Yes/No
Float dBASE III, dBASE IV Numeric (Double)
Date FoxPro 2.x, dBASE III, dBASE IV Date/Time
Memo FoxPro 2.x, dBASE III, dBASE IV Memo
General FoxPro 2.x OLE object

On additional note about memo fields. dBASE and FoxPro store memo fields in a separate file from the rest of the information. Memo fields are stored in the associated file TABLENAME.DBT. The DBT file must be in the same location as the related DBF file.

The Jet engine uses a section in VB.INI or APP.INI to control the dBASE or FoxPro database. This section is called [dBase ISAM]; table 8.9 lists the entries this section contains. Note that the section name is the same whether the database is dBASE or FoxPro.

Table 8.9 Values in VB.INI or APP.INI for dBASE and FoxPro Databases

Section Entry Description Default Value
INFPath Location of the information file that maintains the locations for database index files. None
PageTimeout Specifies the length of time in tenths of a second that your application waits to move data from a table to a virtual table. 600
CollatingSequence Determines the sort sequence of text fields. ASCII
Deleted Causes the Jet engine to not operate on deleted files. Equivalent to the dBASE instruction SET DELETED. ON
Mark The ASCII value of the mark character. Equivalent to the dBASE instruction SET MARK. 47
Date The country used for setting and retrieving date information. Equivalent to the dBASE instruction SET DATE. AMERICAN
Century The country used for setting and retrieving date information. Equivalent to the dBASE instruction SET CENTURY. OFF

Btrieve (FILE.DDF and FIELD.DDF) Files

Btrieve was the database from Novell. Although opening a Btrieve database with the Jet engine is similar to opening other databases, you use a much more complicated setup procedure with Btrieve than with other database systems.

You must have the following items in place if the Jet engine is to import or attach to a Btrieve database:

Once you are sure that the preceding Btrieve requirements are taken care of, set the [Btrieve] section of WIN.INI. Following is the default entry for this section:

[Btrieve]
options=/m:64 /p:4096 /b:16 /f:20 /l:40 /n:12 /t:c:\Btrieve.trn

All the Btrieve options that can be specified in this section are listed in table 8.10.

Table 8.10 Values in the [Btrieve] Section of WIN.INI

Option Description Required Value
/m Memory size >= 38
/p Page size 4096
/b Preimage buffer size 16
/f Open files >= 4
/l Multiple locks Open files (/f) value * 2
/n Files in a transaction >= 4
/t Transaction file Path and file name of transaction file (TRN). In a multiuser environment, this file must be shared by all users.
/u Compression buffer size If Btrieve files are compressed, this value must be >= length of longest record in the database.
/I Preimage file drive N/A
/c Image compaction N/A
/a Activate logging N/A
/s Discard unneeded segments N/A

The following commands open a Btrieve database:

Dim Db As Database
Set Db = OpenDatabase( "c:\btrieve\file.ddf", False, False, _
"Btrieve");

In a Btrieve database, each table is contained in a file with the extension DAT. Btrieve uses FILE.DDF to specify the location of the DAT files in the database. Btrieve also uses FIELD.DDF and INDEX.DDF to complete the data dictionary definition of the database.

It is important to note that once the database is defined and created, if either the data dictionary files or the data files themselves are moved, the database cannot be opened.

The Jet engine uses a section in VB.INI or APP.INI to control the Btrieve database. This section is called [Btrieve ISAM]; table 8.11 lists the entries this section contains.


Data Types from Btrieve to Access

To import a Btrieve database, your application must follow these conversion rules:

Btrieve Data Type Btrieve Version Jet Data Type
Integer, 1 byte 5.1x, 6.x Numeric (Byte)
Integer or autoinc, 2 bytes 5.1x, 6.x Numeric (Integer)
Integer or autoinc, 4 bytes 5.1x, 6.x Numeric (Long)
Decimal 5.1x, 6.x Numeric (Double)
Float or bfloat, 4 bytes 5.1x, 6.x Numeric (Single)
Float or bfloat, 8 bytes 5.1x, 6.x Numeric (Double)
Logical or bit 5.1x, 6.x Yes/No
Lvar 5.1x, 6.x OLE object
Money 5.1x, 6.x Currency
String or lstring or zstring 5.1x, 6.x Text
Date or Time 5.1x, 6.x Date/Time

As it does for other foreign databases, the Jet engine uses the [Btrieve ISAM] section in VB.INI or APP.INI for Btrieve options. Table 8.10 lists these options.

Table 8.11 Values in VB.INI or APP.INI for Btrieve Databases

Section Entry Description Default Value
DataCodePage Determines the code page to use for reading and writing data. OEM
OpenAccelerated Uses the Btrieve option to accelerate access. If used, it must be used by all users. OFF
NetworkAccess Determines whether file locking is to be used. If OFF, files are opened as exclusive. ON
PageTimeout Specifies the length of time in tenths of a second that your application waits to move data from a table to a virtual table. 600
DDFPassword Owner name for opening FILE.DDF if Xtrieve security is used. None
IndexDDF Determines whether Jet maintains the INDEX.DDF file. Require and Maintain are the values that maintain the file. Ignore
XTRPATH List of file paths for locating Btrieve data files. None
XTRENV The location of the Xtrieve environment file. None
XTRPINDX Indicates which path in the XTRPATH variable to use for creating Btrieve files. None

There is one more caveat to follow when using a Btrieve database. When using the server version of Btrieve, BTRIEVE.NLM (a NetWare Loadable Module), you must follow these rules:

Attaching to External Tables

Importing data is frequently not the preferred method of accessing data. Your application may need only temporary access to information that resides in another database and is maintained by other applications. Such a situation calls for attaching to the tables in the external database rather than importing the external database.

When attaching to external tables, your application temporarily adds the table definition to your database. This has the same effect as defining the table in your database definition and importing the data. The attached table remains in the database until explicitly deleted. This means that if you try to attach the table every time you run the program without first detaching the table, a Visual Basic run-time error is generated.

When you try to attach to a table that is already attached, you generate a trappable Visual Basic run-time error. You should trap this error before your application goes into production.

If you want to temporarily use a table in another database, open the database temporarily with OpenDatabase. Then open the specific table with the OpenRecordset command. Although this procedure does not place the table definition into your database, the table can still be accessed. The following code sample shows how to open a new database table without attaching the table:

Dim NewDatabase As Database
Dim NewRecordset As Recordset
Set NewDatabase = DBEngine.Workspaces(0).OpenDatabase _
("C:\path\dbase" , False , False , "dBASE IV")
Set NewRecordset = NewDatabase.OpenRecordset("Address")

Minimizing Overhead

Attaching a foreign database to your application adds additional overhead. To minimize the overhead, follow these guidelines to achieve the best results:

Attaching Tables with Data Manager

Visual Basic 4 provides a convenient method for setting up database attachments during application design mode rather than in run mode. This is made possible with the add-in tool Data Manager. Data Manager allows application developers to design and view the database layout and schema; and view, add, change, and delete the table contents. To access Data Manager, choose Add-Ins, Data Manager in Visual Basic.

To attach a table using Data Manager, choose File on the Data Manager menu bar and then Open Database. The dialog box shown in figure 8.3 appears.

Fig. 8.3

Opening a database using Visual Basic�s Data Manager.

Select the name of Access database and click Open. The Access database is opened and the tables contained in the database are listed in a modeless dialog box (see fig. 8.4). The database that was just opened is the database that is used by the application. This is not the database that the application is using to import data from.

Fig. 8.4

The Table/QueryDefs dialog box showing information about opened database.

To attach a table, choose the Attached Tables button. The Attach Tables dialog box appears as shown in figure 8.5.

Fig. 8.5

Showing all attached databases with the Attached Database dialog box.

Choose the New button to attach a table to the opened database. The New Attached Table dialog box (fig. 8.6) appears. The dialog box has four fields that you must fill in to attach a table. The first field is Attachment Name. The Attachment Name is the name of the table after you attach it to your Jet database. The second field, Database Name, lists the directory that contains the database tables to attach. The third field, Connect String, is a combo box that contains the name of all databases that the Jet engine supports. The last field, Table to Attach, is a combo box that dynamically fills in values based on the three preceding fields. Figure 8.6 shows values used for the Address Book application.

Fig. 8.6

Filling out the New Attached Table dialog box.

After you finish filling in all the fields in the New Attached Table dialog box, choose the Attach button to attach the table to your Jet database. The Attached Tables dialog box is then updated with the new information, as shown in figure 8.7.

Fig. 8.7

An updated list of tables in the Jet database.

Relevant Functions and Commands

As you know, Visual Basic provides two sets of objects in the Jet engine: the DDL group (Data Definition Language), which deals with defining and creating a database, and the DML group (Data Manipulation Language).

One key difference between importing and attaching database files is that attaching uses the TableDef object. The TableDef object adds the definition of the attached table to the open database so that you can create dynaset and snapshot objects as through the table was native to the database.

Table 8.12 lists the objects you can use when you attach to database tables.

Table 8.12 Database Objects Used to Attach to External Data

Object Definition Creation Functions
workspace A container that holds a collection of databases. CreateWorkspace
database A container or structure that holds all the information in a database. CreateDatabase
OpenDatabase
recordset
(type table)		 A structure that holds data in a database. This object organizes the data into rows and columns. A table is a type of recordset, which in turn is a group of records. OpenRecordset
recordset
(type dynaset)		 A group of records that is a result of a query. This object can have updatable records. This is a dynamic image of a set of records. OpenRecordset
recordset
(type snapshot)		 This is similar to a dynaset object. It is a group of records created through the result of a query. Unlike a dynaset, this object contains a static image of the records and is not updatable. OpenRecordset
QueryDef This object stores a SQL-like statement in the database. It is used to indicate the table values used for searching database tables. CreateQueryDef
OpenQueryDef
TableDef This object represents a stored table definition. TableDef objects corresponding to attached tables cannot have their definitions changed. CreateTableDef

Database-Specific Issues

Whether you are attaching or importing data from foreign databases, the setup requirements for the Jet engine are identical. Refer to "Database and File Issues," earlier in this chapter in the "Importing External Data" section, for more information.

From Here...

This chapter demonstrates the techniques and requirements for accessing other databases with the Jet engine. The Jet engine is extremely versatile in its capability to use foreign database systems even though it is highly optimized for working with the native Access database.

Attaching is a more attractive option when you need to access but not manage small amounts of data in another database. You must pay a performance penalty, but with careful planning you can minimize this penalty.

Importing data is necessary when an external database contains information that your database needs to access and manage. The Jet engine provides the same Data Access Objects for external databases as for the Access database. This makes selecting the data necessary for import relatively easy.

In other chapters of this book, you can find more details about some of the topics mentioned in this chapter. Please refer to these chapters for more information:

© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.