ACC: How to Import Several dBASE Databases at Once 95/97

ID: Q141611

The information in this article applies to:

Microsoft Access versions 7.0, 97

SUMMARY

Many new Microsoft Access 7.0 and 97 users want to import data immediately from the systems they are currently using. The Import dialog box in Microsoft Access is designed to import one table at a time. Most of the time, this capability is enough. However, some users may have numerous tables to import, or they may want to import multiple tables regularly. To do so, they can create a batch process in a Visual Basic for Applications procedure to import multiple dBASE databases at once. This article shows how to create such a procedure.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

MORE INFORMATION

You can quickly import tables with a batch process by using a Visual Basic procedure and a batch table. This procedure is designed for importing only dBASE databases, but you can easily modify the procedure to accommodate other file formats.

The batch table lists the tables that you want to import. The Visual Basic procedure reads the table and imports each foreign table listed there. To do so, follow these steps:

1. Create a table called Batch Import with the following structure:

      Table: Batch Import
      ----------------------------
      Field Name: Source Directory
         Data Type : Text
         Field Size: 50
      Field Name: Source Database
         Data Type : Text
         Field Size: 50
      Field Name: Imported Name
         Data Type : Text
         Field Size: 50
      Field Name: Type of Table
         Data Type : Text
         Field Size: 50

2. Enter information in the new Batch Import table about the tables that

   you want to import. The fields in the Batch Import table should be
   filled out 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 once 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, you would 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 module and type the following line in the Declarations section

   if it is not already there:

      Option Explicit

4. Type the following procedure:

      Function BatchImport() As Boolean
         On Local Error GoTo BatchImport_Err
         Dim MyDB As DATABASE, MyTbl As Recordset
         Set MyDB = CurrentDb()
         Set MyTbl = MyDB.OpenRecordset("Batch Import", dbOpenTable)
         DoCmd.Hourglass True
         MyTbl.MoveFirst
         Do Until MyTbl.EOF
            DoCmd.TransferDatabase acImport, _
             MyTbl("Type of Table"), _
             MyTbl("Source Directory"), _
             acTable, _
             MyTbl("Source Database"), _
             MyTbl("Imported Name"), _
             False
            MyTbl.MoveNext
         Loop
         MyTbl.Close
      BatchImport_End:
         DoCmd.Hourglass False
         Exit Function
      BatchImport_Err:
         MsgBox Err.Description
         Resume BatchImport_End
      End Function

5. To test this function, type the following line in the Debug window,

   and then press ENTER.

         ?BatchImport()

   Note that the pointer becomes an hourglass and remains so until all of
   your databases are imported. This process may take several minutes,
   depending on the size of the databases.

REFERENCES

For more information about TransferDatabase, search the Help Index for "TransferDatabase method," or ask the Microsoft Access 97 Office Assistant.

For more information about batch importing dBASE databases in Microsoft Access version 1.x or 2.0, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q88764
   TITLE     : ACC: How to Import Several dBASE Databases at Once (1.x/2.0)

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.

Additional query words:

Keywords          : kb3rdparty PgmObj PgmFilm 
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998