This article explains how you can import dBASE databases in a batch
process by using Access Basic.
Many new Microsoft Access users want to import data immediately from the
systems they are currently using. The Import dialog box in Microsoft
Access enables you to import one table at a time, which usually is
sufficient. However, if you have numerous tables to import, or if you want
to import multiple tables regularly, this process enables you to do so
This article assumes that you are familiar with Access Basic and with
creating Microsoft Access applications using the programming tools
provided with Microsoft Access. For more information about Access Basic,
please refer to the "Introduction to Programming" manual in Microsoft
Access version 1.x, or the "Building Applications" manual, Chapter 3,
"Introducing Access Basic" in version 2.0.
This process uses an Access Basic procedure and a batch table. The
procedure is designed for importing only dBASE databases, but you can
modified it to accommodate other file formats.
The batch table lists the tables that you want to import. The Access Basic
procedure reads the table and imports each foreign table listed there. To
create the table and the Access Basic procedure, follow these steps:
- Create a table called Batch Import with the following structure:
Field name Type Length
Source Directory Text 50
Source Database Text 50
Imported Name Text 50
Type of Table Text 50
- Enter information in the new Batch Import table about the tables you
want to import. You should fill out the fields in the Batch Import
table as follows:
For example, to import a dBASE IV database called EMPLOYEE.DBF from the
C:\DBASE4 directory and a dBASE III database called ORDERS.DBF from the
D:\DBASE3\DATA directory, fill out the fields in the Batch Import table
- Source Directory: This is the full path for the location of the
foreign database file (for example, C:\DBASE).
- Source Database: This is the name and extension of the dBASE
database you want to import (for example, CUSTOMER.DBF).
- Imported Name: This is the name you want the table to have after it
is imported into Microsoft Access (for example, Customers).
- Table Type: This can be either dBASE III or dBASE IV. Specify dBASE
III for both dBASE III and dBASE III PLUS databases.
Source Directory Source Database Imported Name Table Type
C:\DBASE4 EMPLOYEE.DBF Employee Table dBASE IV
D:\DBASE3\DATA ORDERS.DBF Orders Table dBASE III
- Create a new module, and type the following code.
NOTE: In the following example, an underscore (_) at the end of a line
is used as a line-continuation character. Remove the underscore from
the end of the line when re-creating this example.
Sub BatchImport ()
Dim B_DB As Database, B_TBL As Table
Set B_DB = CurrentDB()
Set B_TBL = B_DB.OpenTable("Batch Import")
DoCmd Hourglass True
Do Until B_TBL.EOF
DoCmd TransferDatabase A_IMPORT, B_TBL![Type of Table], _
B_TBL![Source Directory], A_TABLE, B_TBL![Source Database], _
B_TBL![Imported Name], False
DoCmd Hourglass False
- Import the foreign databases as follows:
- Open a module.
- From the View menu, choose Immediate Window.
- Type the following command, and then press ENTER:
The pointer will become an hourglass and remains so until all of your
databases are imported. This process may take several minutes, depending
on the sizes of the databases.
dBASE III, dBASE III PLUS, and dBASE IV are manufactured by Borland
International, Inc., a vendor independent of Microsoft; we make no
warranty, implied or otherwise, regarding the performance or reliability
of these products.
Article ID: 88764 - Last Review: October 14, 2013 - Revision: 2.0
- Microsoft Access 1.0 Standard Edition
- Microsoft Access 1.1 Standard Edition
- Microsoft Access 2.0 Standard Edition
|kbnosurvey kbarchive kb3rdparty kbhowto KB88764|