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

• 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:

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

Additional query words:

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