Knowledge Base |

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

PSS ID Number: 108422

Article Last Modified on 10/29/2003

The information in this article applies to:

- Microsoft Visual Basic Standard Edition for Windows 3.0
- Microsoft Visual Basic Professional Edition for Windows 3.0

This article was previously published under Q108422

## 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 query words: 3.00

Keywords: KB108422
Technology: kbAudDeveloper kbVB300 kbVB300Search kbVBSearch kbZNotKeyword2 kbZNotKeyword6