Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Import Several External Databases at Once

ACC2000: How to Import Several External Databases at Once

View products that this article applies to.

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.

↑ Back to the top

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.

↑ Back to the top

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.
  1. 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       <uncheck>     <check>
   SourceDirectory   varchar    50       <check>       <uncheck>
   SourceDatabase    varchar    50       <check>       <uncheck>
   ImportName        varchar    50       <check>       <uncheck>
   TableType         varchar    50       <check>       <uncheck>
  2. 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
  3. 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
  4. To test this function, type the following line in the Immediate Window, and then press ENTER:
    ?fncBatchImport()
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.

↑ Back to the top

References

For more information about transferring databases programmatically and to view the list of various file types that can be imported, click Microsoft Visual Basic Help on the Help menu, type transferdatabase method in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

↑ Back to the top

Keywords: KB210177, kb3rdparty, kbhowto

↑ Back to the top

Article Info
Article ID : 210177
Revision : 2
Created on : 6/24/2004
Published on : 6/24/2004
Exists online : False
Views : 373