You might be wondering why you would want to use VBA code to import data while you have so many other options available when doing it the visual way. Well, there are two main reasons:
As with exporting data, Access offers three methods for importing data from various types of files. These are; transfertext, transferdatabase and transferspreadsheet. All of these methods are part of the Docmd object.
We will do two import activities, first we will look at how to import data from another Access database and then we will look at how to import data from a text file. If you’ve read my previous article titled "Exporting Data from Access with VBA Code" then you should have all the databases and relevant text files in place. I will be using them here, so it might be a good idea to download them before continuing.
Let’s import a table called "Table1" from the source database to the destination database (both available for download). To import the table we use the transferdatabase method with acImport as the TransferType parameter and "Microsoft Access" as the DatabaseType parameter. So open up the VBA editor (alt+F11) then create a new module and add the following code:
Function GrapAccessTable() DoCmd.TransferDatabase transfertype:=acImport, _ databasetype:="Microsoft Access", _ databasename:="C:\Documents and Settings\Leidago\My Documents\source.mdb", _ objecttype:=acTable, Source:="Table1", _ destination:="importedTable1", structureonly:=False End Function
The table that is imported will be called importedTable1:
Access makes it easy to import text files, but there is a catch. The data in the text file needs to be structured in a way that Access can interpret it and thus import it easily. The data can be arranged in two ways:
Both these arrangements enable you to import text data in a single line of code using the TransferText method. This method also expects a import specification to be included in one of its arguments.
In our previous article called "Exporting Data from Microsoft Access" we created a delimited text file called table1.txt (also available for download). I will be using this file to import data into a table named "friends".
But first, we need to create a import specification so let’s get to it. Open up the destination database, but don’t select any tables:
Once you’ve saved the specification, just click OK and then Finish. You do not have to go through this process again unless you want to change something in the specification.
Now, that we are armed with an import specification we can go ahead and create a new module with the following code:
Function GetTextFileData() DoCmd.TransferText transfertype:=acImportFixed, _ specificationname:="Table1 Import Specification", _ tablename:="Friends", _ filename:="c:\table1.txt", _ hasfieldnames:=True End Function
This function, when run, will create a new table called Friends and fill it with data stored in the table1.txt file. Below is a screen of what my database looks like after the function has been run: