O'Reilly logo

Access Data Analysis Cookbook by Wayne S. Freeze, Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Automating Imports and Exports

Problem

Is there a way to automate import and export processes so that my users don't have to use the Import/Export Wizards?

Solution

Access provides a set of actions that you can use from a macro or a Visual Basic procedure to automate the transfer process. You can choose to transfer data to or from an Access database, a spreadsheet, a SQL Server database, or a text file.

Suppose you simply want to enable your users to transfer a table from one database to another. Create a macro, and select TransferDatabase as the action (see Figure 7-9). In the Action Arguments section, specify the transfer type, the database type, the database name, the type of object to be transferred, the source and destination, and whether you want to copy only the structure.

Creating a transfer database macro

Figure 7-9. Creating a transfer database macro

Running this macro transfers the selected material from one database to another. While this example transfers only a single table, you can transfer multiple items by adding multiple actions to the macro.

Tip

You can use the TransferDatabase action to transfer several different kinds of databases, including dBase, Paradox, and any OLE DB-compliant database. If you need to work with SQL Server, however, you should use the TransferSQLDatabase action.

Text files can be transferred using the TransferText action. Like the TransferDatabase action, the TransferText action relies ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required