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 <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:
103186 : 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:
88647 : 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:
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.
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 KB104918
