ACC2000: How to Import Several External Databases at Once (210177)

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.