Differences Among the Installable ISAMs in VB 3.0Last reviewed: March 29, 1996Article ID: Q104918 |
The information in this article applies to:
SUMMARYThis 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:
GENERAL DIFFERENCES: UNSUPPORTED COMMANDS OR FUNCTIONSCreateDatabase, 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:
ARTICLE-ID: Q100921 TITLE : PRB: Case Sensitivity is Different with Attached TablesNew 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 ISAMFor more information, please read BTRIEVE.TXT, located in the Visual Basic directory.
DatabasesThe DatabaseName string property used on opening a Btrieve database needs to be as follows:
DRIVELETTER\DIRECTORY\FILE.DDFIf 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.
TablesTable 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:
ARTICLE-ID: Q93685 TITLE : PRB: 'Couldn't find object <tablename>' Error with Btrieve ARTICLE-ID: Q94828 TITLE : PRB: Empty Table List When Attaching Btrieve Table IndexesSome 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 TypesThe 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_TEXTThe 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_MEMOThere 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:
ARTICLE-ID: Q103186 TITLE : PRB: Error Message: Too Many Memo or OLE Fields DBASE VERSIONS III AND IV ISAM
DatabasesDatabases are directories. On a data control or OpenDatabase statement, the exclusive property is ignored. The database name is the path to a directory.
TablesThe 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 pathTables are .DBF files in the database directory. If the code sample is successful, a file called MYTABLE.DBF is created.
Numeric FieldsWhen 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.
IndexesIndexes 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 TypesThe 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_MEMOThe 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_MEMOMemo 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:
ARTICLE-ID: Q88647 TITLE : PRB: Irregular Characters in Attached dBASE IV Memo Field FOXPRO VERSIONS 2.0 AND 2.5 ISAM
DatabasesDatabases are directories. On a data control or OpenDatabase statement, the exclusive property is ignored. The database name is the path to a directory.
TablesTables are .DBF files in the database directory.
IndexesIndex 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 TypesThe 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_MEMOThe 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_MEMOThere 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
DatabasesVisual 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:
ARTICLE-ID: Q93699 TITLE : INF: Access Cannot Attach or Import Paradox 4.0 TablesDatabases are directories. On a data control or OpenDatabase statement, the exclusive property is ignored. The database name is the path to a directory.
TablesIn Paradox, the data in a table is ordered physically according to the Primary Key. This is by design in the Paradox database.
IndexesThe 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 TypesThe 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_INTEGERThe 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 errorYou cannot create a field of Type LongBinary or Memo on a Paradox table.
REFERENCES
|
Additional reference words: 3.00
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |