ACC: How to Import Several dBASE Databases at Once (1.x/2.0)

• Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

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 more quickly.

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.

MORE INFORMATION

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:

1. 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
   
   

2. 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:

       - 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.
   

   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 as follows:

         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
   
   

3. 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
            B_TBL.MoveFirst
            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
               B_TBL.MoveNext
            Loop
            DoCmd Hourglass False
         End Sub
   
   

4. Import the foreign databases as follows:

   a. Open a module.

   b. From the View menu, choose Immediate Window.

   c. Type the following command, and then press ENTER:

            BatchImport
   
   

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.

Keywords          : IsmXbase kb3rdparty
Version           : 1.0 1.1 2.0
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto