Microsoft KB Archive/141611

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

Article ID: 141611

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q141611





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:

  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   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.</li></ul>

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: <pre class="fixed_text">     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 </li> Create a module and type the following line in the Declarations section if it is not already there:

Option Explicit

</li>  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 </li> 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.</li></ol>

<div class="references_section">