Access gives you two ways to export data, visually by going to File-Export and then selecting an export format from a dialog box or by using VBA code. In this article we will be looking at how to export Access data using VBA code, since even a beginner can use the visual interface to export data. Using VBA code requires some ability to write code and also a basic understanding of the target application’s object model.
So why code? Because it offers you a wider choice of formats to export data to. It supports all the formats that is available in the dialog box as well as a whole host of others such as Microsoft Outlook contacts, appointments or a Word document.
Using Access’ transfer methods, you can export data to the following:
We will use two of these options which I believe are the most useful to demonstrate how to actually export data using VBA code. The first is how to export data to another Access Database:
Create two databases, one called destination.mdb and the other source.mdb. Now, in the source database create two tables with the following info:
source.mdb
Field | Data Type |
---|---|
name | Text |
occupation | Text |
age | Text |
Save the table as ‘Table1’. The design view of the ‘Table1’ table structure should look something like this:
Then insert the following data:
name | occupation | age |
---|---|---|
Joe Blogg | Database Administrator | 22 |
Jack Daniels | UDontWannaKnow | 25 |
John Smith | Security Guard | 30 |
Kev Dee | Comedian | 25 |
Sonia McKay | Welder | 35 |
Now create a new table called ‘users’ it has the following structure:
Field | Data Type |
---|---|
uid | AutoNumber |
uname | Text |
pw | Text |
Save the table as ‘users’. The design view of the ‘users’ table structure should look something like this:
Then insert the following data:
uid | uname | pw |
---|---|---|
1 | Leidago | admin |
2 | Joe Blogg | generic |
The destination.mdb database will not have any tables because we will be exporting tables to it.
To actually export the data using VBA code we will be using MS Access’s DoCmd object. This object has a method called transfers that we will use to transfer databases and text files as you will see in the next section.
To export table data from one Access database to another, we use the transferdatabase method with acExport as the TransferType parameter and "Microsoft Access" as the DatabaseType parameter. So select the users table then open up the VBA editor(alt+F11) then create a new module and add the following code:
Function Send2Access() DoCmd.TransferDatabase transfertype:=acExport, _ databasetype:="Microsoft Access", _ databasename:="C:\Documents and Settings\Leidago\My Documents\destination.mdb", _ ObjectType:=acTable, Source:="users", _ Destination:="userstbl", structureonly:=True End Function
This function exports the ‘users’ table to the “destination” database which in this case is located on the “c” drive. Take note of the transfertype, databasetype, databasename and destination. All have to be specified so the program knows where to put the data and how to do it. When this table is exported, a duplicate table (called userstbl) will be created in the target database:
To export data to a text file takes slightly longer but Access makes it easy for us. Here we use the transfertext() method. The reason why I want to show how to export to a text file is because you can import data that is stored in this format into almost any program. First we need to create what is called a specification. A export specification is just a way in which you store export information so that Access can export your data in the same way every time you export a particular file. This is especially useful for recurring export task that you might want to perform.
To create an export specification for exporting data from an Access table or query to a delimited text file, do the following:
You can now use the spec in code as the setting of the SpecificationName parameter of the TransferText method. We will now transfer the data stored in the Table1 table of the source database to a tab delimited text file called myfriends.txt. Create a second module and add the following code:
Function Send2TextFile() DoCmd.TransferText transfertype:=acExportDelim, _ specificationname:="Table1 Export Specification", _ tablename:="table1", _ filename:="C:\Documents and Settings\Leidago\My Documents\myfriends.csv", _ hasfieldnames:=True End Function
Run the code and you should have a text file called myfriends.txt that you can open in notepad, WordPad, Excel or many other programs. When I open up my version of myfriends.txt this is what it looks like:
Download the sample Microsoft Access Files for this article (29kb)