Microsoft Access 2007 is a programming language with database objects operating through a Visual Basic for Applications (VBA) framework.
Importing data into your Access system from another database should be done on a wholesale basis using the transfer database command in a syntax similar to the following.
DoCmd.TransferDatabase acImport, “Microsoft Access”, myPath, acTable, ExternalTableName, InternalTableName
Setting up the use of this command can be tricky
- Dimension the variables you will need. I recommend keeping it all straight by dimensioning everything at the beginning of your block of code, whether you are in a macro, module, form, or other access object: Dim myPath as string, ExternalTableName as string, InternalTableName as string
- Fill your strings. The myPath variable should be set to the location of the database that you are importing from, for example myPath=”C:InputimportDB.accdb” is valid, as is the result from a user dialog such as Application.FileDialog(msoFileDialogFilePicker).Show. Set the external and internal table names in a similar fashion, either statically in the system such as ExternalTableName=”Mytable”:InternalTableName=”MyImport” or through a dynamic process with user input.
- Kill the internal table if needed. This happens to me all the time, you set all the variables up but because there is a table of the same name in my database I have to either kill it before importing the new one or rename the new one. I’d recommend using the following code if you want to overwrite the internal table with the new one: On Error Resume Next: Currentdb.Execute “Drop Table ” & InternalTableName.
- Run the DoCmd statement. Access utilizes the DoCmd statement to perform generic operations not associated with a particular object, it’s built in commands are very useful for quickly moving things around and performing actions within your database object. The code is the same as above: DoCmd.TransferDatabase acImport, “Microsoft Access”, myPath, acTable, ExternalTableName, InternalTableName.
Note that you can replace acTable with acQuery if you’re importing a query, or acReport for a report, and in general there’s a variety of objects you can import from your source database.
A full stored procedure for this operation would look something like the following.
Public Sub DBtransfer(myPath as String, Etbl as String, Itbl as String)
On Error Resume Next
Currentdb.Execute “Drop Table ” & Itbl
DoCmd.TransferDatabase acImport, “Microsoft Access”, myPath, acTable, Etbl, Itbl
MsgBox “Done!” ‘If you want to indicate completion