databasedev.co.uk - database solutions and downloads for microsoft access

Exporting Data

Exporting Data from Microsoft Access with VBA Code

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:

  • Export Database Objects from one Access Database to another
  • Export Access Data to Excel Worksheets
  • Export Data to Text Files
  • Export Data to dBASE Databases
  • Export Data to MS Word

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:

The table in design view.

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:

The user table in design view.

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:

The destination database window.

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:

  • Select the table1 table
  • Select File-Export. The Export Dialog box should open up at this point
  • In the Save as Type dropdown box choose Text Files:

    The Save As type drop-down list
  • Click Export to open the Export Text Wizard. The default setting is delimited. Click Next to accept the default:

    The Export Text Wizard dialog box
  • Make sure that everything in your window is the same as that which is in the window below:

  • Now, click Advanced to open the Export Specification dialog box:

  • Now click on Save as. A Dialog box with a pre-filled in name will appear, enter a name that you want or simply accept the one suggested and then click on OK:

  • Click on OK until all the open windows are closed.

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:

The generated text file.

Download the sample Microsoft Access Files for this article (29kb)

The Author

Leidago !Noabeb is a computer programmer based in Namibia. He has worked with both opensource and Microsoft technologies for over seven years and specializes in writing communications software. He has made many contributions to various online websites dedicated to web development. He can be reached at: leidago [at] googlemail.com