LONG: VB 3.0 EXTERNAL.TXT: Using External Database TablesID: Q108422
|
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.
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 :
Version :
Platform :
Issue type : Last Reviewed: June 2, 1999