Importing External Access Database Tables Using Visual Basic

ometimes a Microsoft Access database user may find it desirable to automate the process of importing tables from an external Access database file into their current database. Below is a segment of code that provides an example of how to accomplish this.

CODE

Dim root As String

Dim srcDB As database

Dim tdf As TableDef

Dim x As Integer

Root = "C:\"

Set srcDB = OpenDatabase(root)

For Each tdf In srcDB.TableDefs

x = 0

If (tdf.Name = "CULVERT") Then

DoCmd.TransferDatabase acImport, "Microsoft Access", root, acTable, "CULVERT", "CULVERT"

errorCheck = 1

End If

Next tdf

After the variable declarations, the first line of code sets the variable "root" equal to "C:\" which is the location of the external database holding the tables we wish to import. The next line sets our database variable equal to the external database. The next lines of code search through the external database to find the table named "CULVERT." If this table is found, the table is imported using the DoCmd function. The "acImport" variable simply tells the function that we wish to import information from the database. "Microsoft Access" specifies the type of database containing the table. Following that is the folder location ("root") of the external database. The variable actable specifies that the object we wish to import is a table. The next two strings contain the table name to import and the name to assign to the imported table when it is imported into the current database.

Once the desired tables have been imported, they can be utilized just like any of the other tables in the database.

Nicholas Brown is a recognized authority on the subject of Access Databases. He is the founder of Database Technology Services (DTS). DTS is a leader in custom Access database development. DTS programmers create databases for corporations, small businesses and individuals. Visit http://www.dts-consultants.com to see all of the services DTS has to offer.