LONG: VB 3.0 EXTERNAL.TXT: Using External Database Tables

 Last reviewed: July 20, 1995
Article ID: Q108422
The information in this article applies to:

- Standard and Professional Editions of Microsoft Visual Basic for 

  Windows, version 3.0

SUMMARY

The following article contains the complete contents of the EXTERNAL.TXT file distributed with the Standard and Professional Editions of Visual Basic version 3.0 for Windows. EXTERNAL.TXT describes how to access external database tables using the data control.

MORE INFORMATION

                            EXTERNAL.TXT

  Release Notes for Microsoft (R) Visual Basic (TM) Standard Edition

                            Version 3.00

             (C) Copyright Microsoft Corporation, 1993
This document contains release notes for Microsoft Visual Basic for Windows Standard Edition version 3.0. Information in this document is more current than that in the manuals or online Help.

Visual Basic Standard Edition users can access external database tables using the data control. Professional Edition users should read Appendix C, "Accessing External Databases," in the "Data Access Guide," "Professional Features," Book 2.

How to Use This Document

To view EXTERNAL.TXT on screen in Windows Notepad, maximize the Notepad window.

To print EXTERNAL.TXT, open it in Windows Write, Microsoft Word, or another word processor. Then select the entire document and format the text in 10-point Courier before printing.

Contents

Part    Description
----    -----------
 1      Accessing External Databases

 2      Opening External Tables

 3      Achieving Optimal Performance with External Tables

Part 1: Accessing External Databases

The Standard Edition of Visual Basic can open any of the supported external databases. The following database formats are currently supported:

- Microsoft Access / Visual Basic (databases other than the open database) - Btrieve (with data definition files FILE.DDF and FIELD.DDF) - dBASE III and dBASE IV - FoxPro and FoxPro for Windows - Paradox

Note

Visual Basic can read and write Microsoft Access databases since it shares a common database engine with Microsoft Access. References to "Visual Basic" databases include those databases created or manipulated by Microsoft Access.

Pros and Cons of Accessing External Databases

You have two choices when using data from external sources.

You can directly access the external database table, or you can import the data into your Visual Basic database. It would make sense to directly access the table if it is already on an established external database which is being actively updated. In this case the mechanisms already set up to update, manage and share the data can remain in place, but your application will have to deal with the extra overhead involved in fetching the external data.

External tables can be used in most respects like any other table in your Visual Basic database while they are being used by other applications sharing the same host server. You can also combine operations that include external data from external tables with data stored in the local database. If you choose to import data from external tables into Visual Basic tables, this can be accomplished through Microsoft Access, or you can also use a Visual Basic application. Visual Basic is especially adept at reading ASCII-delimited files using the INPUT # statement.

The next sections discuss how to access external tables. Note that you can have an existing Microsoft Access or Visual Basic database that already has tables attached to it. Attached tables have linkage information built into the database that permits Visual Basic to access the data as if it were a part of your database. In this case, Visual Basic can extract data from these attached tables without any extra work on your part.

Tips for Using External Tables

When you use an external database table, consider the following tips:

- Before your application uses any data access objects, you'll need to 

  provide Visual Basic with the location of the .INI file that contains
  initialization parameters for each of the external databases you expect
  to use. You can do this using the SetDataAccessOption statement. For
  more information, search Help for "SetDataAccessOption."
- The initialization file must contain a section that includes necessary 
  external database setup information. See the section "Initialization
  File Details" below for a listing of the .INI file settings needed to
  connect to each of the supported databases.

Note

If you do not have the correct entries (as described below) in your VB.INI or Appname.INI file you will trigger the trappable error "Cannot find installable ISAM."

* If you access an external table from a Microsoft Access database, you may need to supply a password. You can do this by using the SetDefaultWorkspace statement. For example, the following code indicates the user name is "Chrissy" and the password is "HighIQ." 

   SetDefaultWorkspace "Chrissy", "HighIQ"
For more details, see SetDefaultWorkspace in the Language Reference or in Help.

* If you access an external table from Btrieve, Paradox, or an SQL database, you may need to supply a password. Note that this password is different from a Microsoft Access user password; it's the password set in the external database. The database password is supplied in the Connect property of the data control using the PWD identifier. For example, the following Connect string includes a password: 

   "Paradox;PWD=mypword;"
* To access an external table on a network, you must connect to the network and have access to the database file or directory. If your network redirector supports it, and you want Visual Basic to automatically connect to the appropriate file server each time you open an external table, specify the fully-qualified network path name for the file in the DatabaseName, Connect and RecordSourceName properties. There is no mechanism to provide a network share password. In cases where a password is required to gain access to a network share, you'll have to connect to the share, provide a password, and pre-assign a drive letter to the share before starting your program.

For example, if you use a Microsoft LAN Manager network, you might enter the following path to connect to a remote dBASE file:

\\server\share\datadir\author.dbf

To provide this path when opening a remote dBASE III table, use this code: 

' Assume we want to attach a dBASE III table called AUTHOR
' on the \\SERVER\SHARE\DATADIR server.
'
Data1.Databasename = "\\SERVER\SHARE\DATADIR" Data1.RecordSource = "AUTHOR" Data1.Connect = "dBASE III" Data1.Refresh

As long as the dBASE file is not moved, the data will be available to your application. Generally, the syntax for attaching other types of external files is similar to the technique shown above.

* When defining external tables, only Paradox will support primary key definitions. Paradox tables require primary keys.

* Although you can use an attached table as if it were a Microsoft Access table, there are special considerations. For information about working with attached tables, see "Using Attached External Tables" later in this document.

* When you work with multiple external database tables, occasionally you may find the Updatable property is False. Generally, this is due to the complexity of the query. To be able to consistently update external tables, you may find it easier to access them in simpler queries.

* When Visual Basic manipulates external databases, it creates temporary indexes for the queries being performed on the workstation's hard disk - even if the database is on an external (networked) device. Temporary space is allocated from the directory indicated by the TEMP environment string variable, which usually points to the \WINDOWS\TEMP directory. If your system has not established a TEMP environment variable, if it points to an invalid path or if your workstation does not have sufficient space for these temporary indexes, your application may behave unpredictably as Windows and Visual Basic run out of resource space. The amount of space needed is a function of the size of the external table and can vary from a few thousand bytes to several megabytes.

* When deleting records from dBASE or FoxPro databases, the records may reappear when the table is closed and reopened. To tell Visual Basic not to fetch deleted records, set the DELETED parameter in the .INI file to "On" (the default).

Part 2: Opening External Tables

The method for opening each of the external databases is roughly the same. Subsequent sections in this appendix deal with the individual characteristics for each of the supported external database formats. When using the data control to directly open external tables, you will need to either work interactively with Visual Basic's Properties window at design time to set the individual properties for the data control, or use code in your application to make the settings.

Accessing Paradox Tables

Visual Basic can access external tables from Paradox versions 3.0 and 3.5. If you provide the correct password, Visual Basic can open encrypted Paradox tables. If you open an external Paradox table, you can extract and update data even if others are using it in Paradox.

When opening external Paradox database tables directly, you'll also need to specify the name of the directory (not a filename) as the DatabaseName property of the data control and the name of the table file in the RecordSource property. For example, to open a Paradox file "Author.DB" and use the name "ParaAuthor" to reference it as a table object, use the following code: 

data1.Connect = "Paradox;"         ' Specify database type
data1.DatabaseName = "C:\Paradox" ' Point to directory 
data1.RecordSource = "Author"      ' Name database table file
data1.Refresh While Not data1.RecordSet.EOF 
 Print data1.RecordSet(0)          ' Dump field(0) to the form
 data1.RecordSet.MoveNext          ' for all records
Wend

Important

Paradox stores important information about a table's primary key in an index (.PX) file. If you access a Paradox table that has a primary key, Visual Basic needs the .PX file to open the external table. If you delete or move this file, you won't be able to open the external table. If you attach a Paradox table that doesn't have a primary key, you cannot update data in the table using Visual Basic. To be able to update the table, define a primary key in Paradox.

Paradox to Microsoft Access Data-Type Conversions

When you access an external Paradox table, Visual Basic translates Paradox data types into the corresponding Visual Basic data types. The following table lists the data-type conversions. 

Paradox data type   Microsoft Access data type

Alphanumeric        Text
Currency            Number (FieldSize property set to Double)
Date                Date/Time
Number              Number (FieldSize property set to Double)
Short number        Number (FieldSize property set to Integer)

Accessing dBASE and FoxPro Files

Visual Basic can directly open external .DBF files in dBASE III, dBASE IV, or FoxPro version 2.0 or 2.5 format. If you directly open a dBASE or FoxPro table file, you can view and update data, even if others are using it with dBASE or FoxPro. If you access a dBASE or FoxPro file, you can also tell Visual Basic to use one or more index files (.NDX or .MDX for dBASE; .IDX or .CDX for FoxPro) to improve performance.

For dBASE and FoxPro databases, Visual Basic keeps track of the table indexes in a special information (.INF) file. When you use Visual Basic to update the data in your .DBF file, Visual Basic also updates the index files to reflect your changes. The .INF file is created for you when you use Visual Basic to create a new index for a dBASE or FoxPro table, or you can create them yourself with a text editor.

The format for the .INF files is as follows:

TableName.INF contains: NDX1=<Index 1 Filename>.NDX NDX2=<Index 2 Filename>.NDX NDXn=<Index n Filename>.NDX

For example, an .INF file for the Authors table would be AUTHORS.INF and it might contain:

NDX1=CityIndx.NDX NDX2=NameIndx.NDX

Place these index and .INF files in the same directory as the other dBASE III files. FoxPro and dBASE databases are not maintained in a single file but in a disk directory which contains separate data, index, and other support files. When opening external FoxPro and dBASE database tables directly, you'll also need to specify the name of the directory (not a filename) as the DatabaseName property in the data control and the name of the table file in the RecordSource property. For example, to open a FoxPro version 2.5 file "Author.DBF", use this code: 

data1.Connect = "FoxPro 2.5;"           ' Specify database type
data1.DatabaseName = "C:\FoxPro"        ' Point to directory
data1.RecordSource = "Author"           ' Name database table file
data1.Refresh While Not data1.RecordSet.EOF 
    Print data1.RecordSet(0)            ' Dump field(0) to the form
    data1.RecordSet.MoveNext            ' for all records
Wend
FoxPro and dBASE Memo fields are located in separate files. These files cannot be located or moved outside of the directory containing the table files. FoxPro and dBASE database systems do not physically delete records but merely mark them for deletion at a later time. You must PACK the .DBF file (using your own utilities) to remove these records from the .DBF files. The CompactDatabase function will not affect attached tables. If you use the .INI file setting DELETED = ON (in the [dBASE ISAM] section), Visual Basic filters out deleted records so that they do not appear in recordsets. With DELETED=OFF, all records are included in the recordsets you create, including deleted records. This allows dBASE and FoxPro users to undelete records. In this case, when you access a dBASE or FoxPro table, Visual Basic builds a Dynaset from the records. When you delete a record, Visual Basic deselects the record in the Dynaset and marks the record as deleted in the .DBF file. If you refresh the Dynaset or reopen the table the records will still be present.

Important

If you access a .DBF file and associate an index file (.NDX or .MDX for dBASE or .IDX; .CDX for FoxPro), Visual Basic needs the index file to open the attached table. If you delete or move index files or the information (.INF) file, you won't be able to open the external table. Additionally, if you use dBASE or FoxPro to update data in a .DBF file that you have accessed from your Visual Basic Database, you must also update any dBASE or FoxPro indexes associated with the .DBF file. If the index files are not current when Visual Basic tries to use them, the results of your queries are unpredictable.

dBASE and FoxPro to Microsoft Access Data-Type Conversions

When you access a dBASE or FoxPro file, Visual Basic translates dBASE and FoxPro data types into the corresponding Microsoft Access data types. The following table lists the data-type conversions. 

dBASE data type         Microsoft Access data type

Character               Text
Date                    Date/Time
General (FoxPro only)   OLE
Logical                 Yes/No
Memo                    Memo
Numeric, Float          Number (FieldSize property set to Double)

Accessing Btrieve Tables

Using Visual Basic, you can directly open in Btrieve 5.1x format. To use Btrieve tables, you must have the data definition files FILE.DDF and FIELD.DDF, which tell Visual Basic the structure of your tables. These files are created by Xtrieve* or by another .DDF file-building program. If you delete or move these files or your data files, you won't be able to open an attached Btrieve table. For more information on using Btrieve with Visual Basic, see the text file BTRIEVE.TXT in your Visual Basic directory.

When accessing Btrieve database tables, you'll need to specify the name of the Btrieve data file (.DDF) as the DatabaseName (DATABASE= in the Connect property) and the name of the table file in the SourceTableName property. In this case the Btrieve file name may have no bearing on the name of the table. The correct file names are stored in the FILE.DDF file.

For example, to open a Btrieve file "FILE.DDF" to reference the Btrieve database table "Author", use this code: 

data1.Connect = "Btrieve;"           ' Specify database type
data1.DatabaseName = "C:\Btrieve\FILE.DDF" ' Point to database file 
data1.RecordSource = "Author"        ' Name database table file
data1.Refresh

While Not data1.RecordSet.EOF 

   Print data1.RecordSet(0)     ' Dump field(0) to the form
   data1.RecordSet.MoveNext     ' for all records
Wend

WIN.INI Initialization File Settings

The Btrieve driver uses the [BTRIEVE] section of the WIN.INI file (not VB.INI) when it accesses Btrieve files. After you install Visual Basic and specify that you want to access Btrieve files, the WIN.INI file contains the following default settings:

[BTRIEVE] Options=/m:64 /p:4096 /b:16 /f:20 /l:40 /n:12 /t:c:\VB3\BTRIEVE.TRN

The following table gives a brief description of each switch. You should consult your Btrieve documentation and BTRIEVE.TXT supplied with Visual Basic for a definitive and current listing of these settings. If you install another application that modifies these settings from the values shown, Visual Basic may not function normally. 

Switch   Definition

/m       Memory size
/p       Page size
/b       Pre-image buffer size
/f       Open files
/l       Multiple locks
/n       Files in a transaction
/t       Transaction file name.
         (Must be visible to all Btrieve users.)

NOTE
----
To use Btrieve data, you must have the Btrieve for Windows dynamic-link library (WBTRCALL.DLL), which is not provided with Visual Basic. This file is available with Novell* Btrieve for Windows, Novell NetWare* SQL, and other Windows-based products that use Btrieve. If you expect to share a Btrieve database, you will need to make sure that the path given for the transaction file (as specified above) is visible on the net to all users of the database. Generally, this file is placed on a common server that all users have access to. The default setting for this parameter does not take this into account.

Btrieve to Microsoft Access Data-Type Conversions

When you access a Btrieve table, Visual Basic translates Btrieve data types into the corresponding Microsoft Access data types.

The following table lists the data-type conversions. 

Btrieve data type             Microsoft Access data type

Date, time                    Date/Time
Float or bfloat (4-byte)      Number (FieldSize property set to
                                      Single)
Float or bfloat (8-byte), 
    decimal, numeric          Number (FieldSize property set to Double)
Integer (1-, 2-, or 4-byte)   Number (FieldSize property set to Byte,
                                      Integer, or Long Integer)
Logical                       Yes/No
Lvar                          OLE Object
Money                         Currency
Note                          Memo
String, lstring, zstring      Text

Part 3: Achieving Optimal Performance with External Tables

Although you can use external tables as if they're regular Microsoft Access tables, it's important to keep in mind that they aren't actually in your Visual Basic database. Each time you view data in an external table, Visual Basic has to retrieve records from another file. This can take time, especially if the external table is on a network.

If you're using an external table on a network, follow these guidelines for best results:

* View only the data you need. Don't page up and down unnecessarily in the data. Avoid jumping to the last record in a large table unless you want to add new records to the table.

* Use queries to limit the number of records that you fetch. This way, Visual Basic can transfer less data over the network.

* In queries that involve external tables, avoid using functions in query criteria. In particular, avoid using aggregate functions, such as DSum, anywhere in your queries. When you use an aggregate function, Visual Basic retrieves all of the data in the external table in order to execute the query.

* If you often add records to an external table, add the records to a Microsoft Access-format table and use an action query to append all added records in one operation. This saves time because Visual Basic won't have to retrieve all the records in the external table.

* Remember that other users may be trying to use an external table at the same time you are. When a Visual Basic Database is on a network, you should avoid locking records longer than necessary.

NOTE

If the information stored in the attached table link properties changes (for example, the database file is moved or a password is changed), you won't be able to open the attached table. To specify current information, delete the outdated link and attach the table again.

Initialization File Details

When Visual Basic is installed, you can install as many of the external database drivers as you want. For those drivers that are installed, an associated .INI file entry is made. Shown below are the default settings for all supported external database drivers. In some cases, these .INI file settings are discussed earlier in the specific driver sections. When you ship your application, it will be necessary to create an initialization file that has the correct .INI settings for the drivers you want to support.

NOTE

To determine the number of retries on commit locks, Visual Basic uses the following formula for the actual retry count:

Count = LockRetry * CommitLockRetry

VB.INI or <Appname>.INI Default Settings

[Options] 

SystemDB=C:\MYPATH\SYSTEM.MDA   ; Access SYSTEM.MDA for use only if
            ; Microsoft Access is being used
[ISAM] 
PageTimeout=5                   ;500 ms - non-read-locked page timeout
MaxBufferSize=128               ;128K
LockRetry=20                    ;20 - retries on Read/Write locks
CommitLockRetry=20              ;20 - retries on Commit locks
ReadAheadPages=16               ;16 pages
[Installable ISAMs] 
Paradox 3.X=C:\VB\pdx110.DLL    ;Path of the Paradox driver
FoxPro  2.0=C:\VB\xbs110.DLL    ;Path of the FoxPro 2.0 driver
FoxPro  2.5=C:\VB\xbs110.DLL    ;Path of the FoxPro 2.5 driver
dBASE III=C:\VB\xbs110.DLL      ;Path of the dBASE III driver
dBASE IV=C:\VB\xbs110.DLL       ;Path of the dBASE IV driver
Btrieve=C:\VB\btrv110.DLL       ;Path of the Btrieve driver
[Paradox ISAM] 
PageTimeout=600                 ;60 seconds
ParadoxUserName=Joe User        ;Name displayed when lock
                                ; conflicts occur
ParadoxNetPath=P:\PDOXDB\       ;Path to the PARADOX.NET file
CollatingSequence=Ascii         ;Collating sequence of your files
            ;  (Ascii, International, Norwegian-Danish,
            ;  or Swedish-Finnish)
[BTrieve ISAM] 
PageTimeout=600                 ;60 seconds
[dBase ISAM] 
PageTimeout=600                 ;60 seconds CollatingSequence=Ascii
            ;Collating sequence
            ;(Ascii or International)
Century=Off ;Use of four-digit dates 
            ;(On or Off)
Date=American                   ;Date format: correlates to
            ;the SET DATE command in dBase
Mark=47     ;Decimal value of the ascii
            ;mark character:correlates to the
            ;SET MARK command in dBase
Deleted=ON ;Show and operate on deleted records 
            ;Deleted=On: never operate
            ;on deleted records

WIN.INI

The following line must appear in WIN.INI (located in your Windows directory) if you intend to use external Btrieve tables. The details of this entry are discussed in the Btrieve section earlier in this appendix.

[BTRIEVE] Options= /m:64 /P:4096 /b:16 /f:20 /l:40 /n:12 /t:c:\VB\BTRIEVE.TRN

Additional reference words: 3.00
KBCategory: kbreadme kbref kbinterop kb3rdparty
KBSubcategory: RefsDoc

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: July 20, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.