Microsoft KB Archive/210177

= ACC2000: How to Import Several External Databases at Once =

Article ID: 210177

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210177



This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

Advanced: Requires expert coding, interoperability, and multiuser skills.



SUMMARY
By using the Import dialog box, you can import data from other systems that you may be using, such as dBASE or Paradox. By using the Import dialog box, however, you can import only one table at a time. Most of the time, this functionality is enough. But if you have multiple tables to import, or if you want to import multiple tables on a regular basis, using the Import dialog box would be cumbersome. For these cases, you can create a batch process in Visual Basic for Applications (VBA) to import multiple database files at once. This article shows you how to do this.



MORE INFORMATION
You can use a VBA procedure to quickly import tables with a batch process. This procedure is designed to import database file-types supported by Microsoft Access. The following example uses dBASE files.

This example creates a table that lists the location of the external table to import, the file name, the name of the resultant Access table, and the type of file that is being imported. The procedure reads the table, and then imports each external file listed.   In any Access 2000 database or project, create a table named tblBatchImport with the following structure.

For an Access Database:
  Field Name       Data Type     Field Size     Description SourceID        AutoNumber    Long Integer   Unique identifier; set this as your Primary Key

SourceDirectory Text          50             This is the full path for the location of the external database file (for example,                                                C:\Databases\Paradox)

SourceDatabase  Text          50             This is the name and extension of the database you want to import (for                                                 example, Customer.db)

ImportName      Text          50             This is the name you want the table to have once it                                                is imported into Microsoft Access (for example,                                                 tblCustomers)

TableType       Text          50             See on-line help for the various file types. Specify dBASE III dBASE III PLUS databases

For an Access Project:
  Column Name       Datatype   Length   Allow Nulls   Identity -  SourceID          int         4 SourceDirectory  varchar    50 SourceDatabase   varchar    50 ImportName       varchar    50 TableType        varchar    50   Enter information into tblBatchImport about the external files that you want to import. In this example, enter the follow information for the sample files installed by Office 2000:

  SourceDirectory    SourceDatabase    ImportName     TableType -  C:\Program Files\  CUSTOMER.DBF      tblCustomers   dBASE III Microsoft Office\ Office\1033 C:\Program Files\ EMPLOYEE.DBF      tblEmployees   dBASE IV   Microsoft Office\ Office\1033 C:\Program Files\ ORDERS.DBF        tblOrders      dBASE 5.0 Microsoft Office\ Office\1033   Create a new module, and then type or paste the following code: Option Compare Database Option Explicit

Function fncBatchImport As Boolean 'Reference the library Microsoft ActiveX Data Objects 2.1 (or higher). On Local Error GoTo ImportError

Dim con As ADODB.Connection Dim rst As ADODB.Recordset Set con = CurrentProject.Connection Set rst = New ADODB.Recordset rst.Open "tblBatchImport", con, adOpenForwardOnly, adLockOptimistic DoCmd.Hourglass True

rst.MoveFirst

Do Until rst.EOF DoCmd.TransferDatabase acImport, rst("TableType"), _ rst("SourceDirectory"), acTable, rst("SourceDatabase"), _ rst("ImportName"), False rst.MoveNext Loop

rst.Close Set rst = Nothing Set con = Nothing

ImportEnd: DoCmd.Hourglass False Exit Function

ImportError: MsgBox Err.Description Resume ImportEnd End Function   To test this function, type the following line in the Immediate Window, and then press ENTER: ?fncBatchImport </ol>

Note that the mouse 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.

The third-party products that are discussed in this article are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.

<div class="references_section">