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.
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 <uncheck> <check>
SourceDirectory varchar 50 <check> <uncheck>
SourceDatabase varchar 50 <check> <uncheck>
ImportName varchar 50 <check> <uncheck>
TableType varchar 50 <check> <uncheck>
- 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:
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.
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
