Differences Among the Installable ISAMs in VB 3.0

ID: Q104918

The information in this article applies to:

Microsoft Visual Basic Professional Edition for Windows, version 3.0

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:

Q100921 : 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:

Q93685 : PRB: 'Couldn't find object <tablename>' 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:

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

DBASE VERSIONS III AND IV ISAM

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:

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

FOXPRO VERSIONS 2.0 AND 2.5 ISAM

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.

PARADOX VERSION 3.X ISAM

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:

Q93699 : 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.

REFERENCES

BTRIEVE.TXT and EXTERNAL.TXT

Visual Basic version 3.0 "Professional Features Book 2," Appendix C.

Btrieve is manufactured by Btrieve Technologies, Inc., dBASE version III, dBASE version IV, and Paradox version 3.X are manufactured by Borland International Inc., both vendors are independent of Microsoft. We make no warranty implied or otherwise, regarding the performance or reliability of these products.

Additional query words: 3.00


Keywords          : kbcode 
Version           : 3.00
Platform          : WINDOWS 
Issue type        :

Last Reviewed: June 15, 1999