Microsoft KB Archive/104918

= Differences Among the Installable ISAMs in VB 3.0 =

Article ID: 104918

Article Last Modified on 1/9/2003

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q104918



SUMMARY
This article contrasts the way the installable Indexed Sequential Access Methods (ISAMs) handle databases, tables, indexes, and data types with the way Microsoft Access databases handle these same structures.

The installable ISAMs discussed are:


 * Btrieve
 * dBASE versions III and IV
 * FoxPro versions 2.0 and 2.5
 * Paradox version 3.x

NOTE: This article does not discuss differences in ODBC data sources.

GENERAL DIFFERENCES: UNSUPPORTED COMMANDS OR FUNCTIONS
CreateDatabase, CompactDatabase, RepairDatabase, SetDefaultWorkspace, ListTables, CreateQueryDef, OpenQueryDef, DeleteQueryDef, and ListParameters are functions or statements are specific to Microsoft Access. CreateDatabase is not supported because the ISAMs are all single- table databases where the database can be thought of as the directory in which the tables reside. Functions similar to CompactDatabase and RepairDatabase on an ISAM database should be done by using the native database tools.

As a general rule, Microsoft Access database searches are not case sensitive, but searches on the ISAM databases are case sensitive. The following are exceptions to this:


 * If a search is made across two different database types, that search is not case sensitive.
 * Some of the international settings cause a difference in case sensitivity. For example, searches are not case sensitive if in the [Paradox ISAM] section, CollatingSequence= is set to International, Norwegian-Danish, or Swedish-Finnish.

For more information about case sensitivity of the ISAMs, please see the following article in the Microsoft Knowledge Base:

100921 : PRB: Case Sensitivity is Different with Attached Tables

New field columns cannot be added to the ISAM database tables once there are records present. This is not the case with Microsoft Access.

MS-DOS does not recognize the ANSI character set. This means that extended ANSI characters will be converted by the OemToAnsi and AnsiToOem Windows API calls. This is not a one-to-one conversion, so some characters may be lost or changed in the process: ANSI characters 147 to 159 specifically. For example, saving Chr$(148) to a dBASE database, returns Asc(34) from the database, but saving Chr$(148) to a native Microsoft Access database correctly returns Asc(148).

BTRIEVE ISAM
For more information, please read BTRIEVE.TXT, located in the Visual Basic directory.

Databases
The DatabaseName string property used on opening a Btrieve database needs to be as follows:

DRIVELETTER\DIRECTORY\FILE.DDF

If just the directory name is listed, an error message will occur.

Code Sample
Dim db As Database Set db = OpenDatabase("c:\btrieve\FILE.DDF",0,0,"btrieve;")

This code opens the database located in the C:\BTRIEVE directory. If a FILE.DDF file is not located in the specified directory, Visual Basic will create one.

NOTE: A filename is needed for Btrieve ISAM, but the filename is ignored. It will always look for or create a FILE.DDF file and other supporting files.

Databases are a set of .DDF files. You can think of the directory where the files exist as the database. There can be only one FILE.DDF in a directory.

Tables
Table data is stored in .DAT files. FILE.DDF contains the table name and path to data files. FIELD.DDF contains the information about the columns. Visual Basic cannot change the directory where these data files are located. It stores them by default in the DATABASENAME directory. However, Visual Basic can read Btrieve databases that have data files in separate directories, as long as the same directory structure exists as the one where it was created.

For more information about table data in Btrieve databases, please see the following articles in the Microsoft Knowledge Base:

93685 : PRB: 'Couldn't find object ' Error with Btrieve

Q93685 : PRB: Empty Table List When Attaching Btrieve Table

Indexes
Some compound indexes created by applications other than Visual Basic may not be viewable by Visual Basic. Btrieve permits index keys to be defined as specific byte ranges in a record, If a specified byte range is not aligned on the column boundaries of the fields in a table, then Visual Basic will not be able to use that index.

Data Types
The following table shows how data types are converted to Microsoft Access when reading an existing table:

  Btrieve          Microsoft Access ---  String           DB_TEXT Integer         DB_INTEGER or DB_LONG Float           DB_SINGLE or DB_DOUBLE Date            DB_DATE Time            DB_DATE Decimal         DB_DOUBLE Money           DB_CURRENCY Logical         DB_BOOLEAN Numeric         DB_DOUBLE Bfloat          DB_SINGLE or DB_DOUBLE Lstring         DB_TEXT Zstring         DB_TEXT

The following table shows how data types are converted when you create a new table in Visual Basic:

  Data Field       Result ---  DB_BOOLEAN       DB_BOOLEAN DB_BYTE         DB_BYTE DB_INTEGER      DB_INTEGER DB_LONG         DB_LONG DB_CURRENCY     DB_CURRENCY DB_SINGLE       DB_SINGLE DB_DOUBLE       DB_DOUBLE DB_DATE         DB_DATE DB_TEXT         DB_TEXT DB_LONGBINARY   DB_MEMO DB_MEMO         DB_MEMO

There can be only one memo field or one long binary field per Btrieve table. Having more generates Error message 3054 "Too many memo or long binary fields." For more information about Btrieve memo fields, please see the following article in the Microsoft Knowledge Base:

103186 : PRB: Error Message: Too Many Memo or OLE Fields

Databases
Databases are directories. On a data control or OpenDatabase statement, the exclusive property is ignored. The database name is the path to a directory.

Tables
The following shows by example how to create a dBASE database and table. The code sample demonstrates the steps necessary to create a table for a dBASE database. Think of the database as the C:\DBASE directory. In Microsoft Access, databases are created using the CreateDatabase function:

Sub Command1_Click Const DB_TEXT = 10 Dim db As database Dim tb As New tabledef Dim fd As Field Set db = OpenDatabase("c:\dBASE", False, False, "dBASE iii;") tb.Name = "MyTable" Set fd = New Field fd.Name = "f1" fd.Type = DB_TEXT fd.Size = 15        'Creates a text field length 15 characters tb.Fields.Append fd        db.TableDefs.Append tb      End Sub

The code sample creates a table that has one field and places it in the C:\DBASE directory. If that directory does not exist, the following error message occurs:

'MyTable' isn't a valid path

Tables are .DBF files in the database directory. If the code sample is successful, a file called MYTABLE.DBF is created.

Numeric Fields
When you use Visual Basic to create a numeric field in a DBASE version III or IV database, Visual Basic creates a numeric field with five decimal places. This is by design.

dBASE version III or IV numeric fields can have up to 19 decimal places. If you want a dBASE version III or IV numeric field with more than five decimal places, you have to use dBASE version III or IV to modify the structure. Then Visual Basic will display and modify the value with all the decimal places and save it to the database correctly.

Indexes
Indexes are separate files. They are placed in the database directory as they are created. All .INF files list the indexes on a table. dBASE version III indexes are .NDX files and dBASE version IV indexes are .MDX files.

A FoxPro or dBASE complex index can only be made from string type fields. Internally, both FoxPro and dBASE provide functions to convert and manipulate fields into strings so that they can be combined into a complex index across several fields of different types. Visual Basic does not have the ability to manipulate these functions, so all complex indexes must be made up of DB_TEXT (string) types.

dBASE allows duplicates in the Primary Key field. This is by design of the dBASE structure, because the concept of Primary Keys does not exist.

Data Types
The following table shows how data types are converted to Microsoft Access when reading an existing table:

  dBASE             Microsoft Access --  Character         DB_TEXT Numeric          DB_DOUBLE Date             DB_DATE Logical          DB_BOOLEAN Memo             DB_MEMO

The following table shows how data types are converted when you create a new table in Visual Basic:

  Data Field        Result --  DB_BOOLEAN        DB_BOOLEAN DB_BYTE          DB_DOUBLE DB_INTEGER       DB_DOUBLE DB_LONG          DB_DOUBLE DB_CURRENCY      DB_DOUBLE DB_SINGLE        DB_DOUBLE DB_DOUBLE        DB_DOUBLE DB_DATE          DB_DATE DB_TEXT          DB_TEXT DB_LONGBINARY    DB_MEMO DB_MEMO          DB_MEMO

Memo fields in dBASE and FoxPro are for text only. This is not the case for a Microsoft Access memo field, which can contain text or binary data.

Viewing dBASE Memo fields that were created in dBASE version IV may result in strange vertical line characters every 65th characters. This is by design; that is, it is the way dBASE displays its memo fields. For more information about problems viewing dBASE Memo fields, please see the following article in the Microsoft Knowledge Base:

88647 : PRB: Irregular Characters in Attached dBASE IV Memo Field

Databases
Databases are directories. On a data control or OpenDatabase statement, the exclusive property is ignored. The database name is the path to a directory.

Tables
Tables are .DBF files in the database directory.

Indexes
Index information is stored in a file (TABLENAME.CDX). This file contains the information about all the indexes on a table. This file must exist in the database directory.

A FoxPro or dBASE complex index can only be made from string type fields. Internally, FoxPro and dBASE provide functions to convert and manipulate fields into strings so that they can be combined into a complex index across several fields of different types. Visual Basic does not have the ability to manipulate these functions, so all complex indexes must be made up of DB_TEXT (string) types.

Data Types
The following table shows how data types are converted to Microsoft Access when reading an existing table:

  FoxPro            Microsoft Access --  Character         DB_TEXT Numeric          DB_DOUBLE Float            DB_DOUBLE Date             DB_DATE Logical          DB_BOOLEAN Memo             DB_MEMO General          DB_MEMO

The following table shows how data types are converted when you create a new table in Visual Basic:

  Data Field        Result --  DB_BOOLEAN        DB_BOOLEAN DB_BYTE          DB_DOUBLE DB_INTEGER       DB_DOUBLE DB_LONG          DB_DOUBLE DB_CURRENCY      DB_DOUBLE DB_SINGLE        DB_DOUBLE DB_DOUBLE        DB_DOUBLE DB_DATE          DB_DATE DB_TEXT          DB_TEXT DB_LONGBINARY    DB_LONGBINARY DB_MEMO          DB_MEMO

There can be only one Memo or LongBinary field per FoxPro table. It is stored in the database directory as a TABLENAME.FPT file.

Memo fields in dBASE and FoxPro are for text only. This is not the case for a Microsoft Access memo field, which can contain text or binary data.

Databases
Visual Basic version 3.0 is not compatible with Paradox version 4.0 or Paradox for Windows. Paradox version 4.0 and Paradox for Windows added some new data types that are not compatible with the Paradox ISAM driver in Visual Basic For Windows.

For more information about compatibility of Paradox version 4.0 or Paradox for Windows with Visual Basic, please see the following article in the Microsoft Knowledge Base:

93699 : INF: Access Cannot Attach or Import Paradox 4.0 Tables

Databases are directories. On a data control or OpenDatabase statement, the exclusive property is ignored. The database name is the path to a directory.

Tables
In Paradox, the data in a table is ordered physically according to the Primary Key. This is by design in the Paradox database.

Indexes
The first index created on a Paradox database must be a primary unique index. This is by design in the Paradox database.

Only the primary index can contain multiple fields, and they must be in the sequential order that they were created. For example, if a table was created with three fields in it, paradox keeps track of the order in which these fields were created. To create a complex index, you must set it on the first n fields of the table. This is by design in the Paradox Database.

Primary indexes will create a file in the database directory called TABLENAME.PX. If you set the Name property upon creation of a primary index, it will be ignored. After it has been created, the Name property of a primary index will return tablename#px. This is by design in Paradox; it is the way it names the primary index.

Primary indexes on a Paradox table can not be deleted even if the table is empty.

Secondary indexes will be named after the field that they are an indexed on. Setting the Name property will be ignored. The Name property after the index has been created will return the name of the field.

Descending indexes are not supported.

Records cannot be added without a primary index.

A Paradox table without a primary key (no .PX file) can only be opened once because it is not possible for the Paradox ISAM to keep track of updates without a primary key.

Visual Basic will generate error message 3051: "Couldn't open file 'xxx.db'" if another process already has the database open.

Data Types
The following table shows how data types are converted to Microsoft Access when reading an existing table:

  Paradox           Microsoft Access --  Alphanumeric      DB_TEXT Currency         DB_DOUBLE Date             DB_DATE Number           DB_DOUBLE Short number     DB_INTEGER

The following table shows how data types are converted when you create a new table in Visual Basic:

  Data Field        Result --  DB_BOOLEAN        DB_INTEGER DB_BYTE          DB_INTEGER DB_INTEGER       DB_INTEGER DB_LONG          DB_DOUBLE DB_CURRENCY      DB_DOUBLE DB_SINGLE        DB_DOUBLE DB_DOUBLE        DB_DOUBLE DB_DATE          DB_DATE DB_TEXT          DB_TEXT DB_LONGBINARY    error DB_MEMO          error

You cannot create a field of Type LongBinary or Memo on a Paradox table.

