Depending on the type of data you're working with, you can automate the importing (import: To copy data from a text file, spreadsheet, or database table into an Access table. You can use the imported data to create a new table, or you can append (add) it to an existing table that has a matching data structure.), exporting (export: To copy data and database objects to another database, spreadsheet file, or file format so that another database or program can use the data or database objects. You can export data to a variety of supported databases, programs, and file formats.), and linking (link (tables): An action that establishes a connection to data from another application so that you can view and edit the data in both the original application and in Access.) of data by carrying out the TransferDatabase, TransferSpreadsheet, or TransferText actions in a macro (macro: An action or set of actions that you can use to automate tasks.) or in a Microsoft Visual Basic for Applications (VBA) procedure (procedure: A sequence of declarations and statements in a module that are executed as a unit. Procedures in a Visual Basic for Applications (VBA) module include both Sub and Function procedures.).
Use a macro
- Create a macro. In the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), click Macros
under Objects.
- Click the New button on the Database window toolbar.
- In the Macro window (Macro window: The window in which you create and modify macros.), click the first empty row in the Action column.
- In the Action column, click the arrow to display the action list (action list: The list that appears when you click the arrow in the Action column of the Macro object tab.).
- Do one of the following to import, export, or link data between the current Microsoft Office Access 2003 database and:
- In the lower part of the window, specify arguments for the selected action. For more information about how to specify arguments, search for "TransferDatabase Action," "TransferSpreadsheet Action," "TransferSQLDatabase," and "TransferText Action" on MSDN Library site.
- Type a comment for the action. Comments are optional.
- Click Save to save the macro.
- Run the macro.
How?
To run a macro directly, do one of the following:
Use a VBA procedure
- Create a VBA procedure.
How?
- To open a module (module: A collection of declarations, statements, and procedures stored together as one named unit. There are two types of modules: standard modules and class modules.), do one of the following:
- To open a new standard module (standard module: A Visual Basic for Applications (VBA) module in which you can place Sub and Function procedures that you want to be available to other procedures throughout your database.), in the Database window, click Modules
under Objects, and then click the New button on the Database window toolbar.
- To open an existing standard module, click Modules under Objects, select the module you want to open, and then click Design.
- To open a form module (form module: A module that includes Visual Basic for Applications (VBA) code for all event procedures triggered by events occurring on a specific form or its controls.) or report module (report module: A module that includes Visual Basic for Applications (VBA) code for all event procedures triggered by events occurring on a specific report or its controls.), open the form or report in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.), and then click Code
on the Form Design toolbar.
- To open a new class module (class module: A module that can contain the definition for a new object. When you create a new instance of a class, you create the new object. Procedures defined in the module become the properties and methods of the object.) that isn't associated with a form or report, in the Database window, click Class Module on the Insert menu.
- To open an existing class module, in the Database window, click Modules under Objects, select the module you want to open, and then click the Design button on the Database window toolbar.
- Declare the function by typing the Function statement.
- Type a function name immediately followed by any function arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.) in parentheses. For example, the following declaration for the IsLoaded function specifies strFormName as an argument:
Function IsLoaded (strFormName As String) As Boolean
- Do one of the following to import, export, or link data between the current Microsoft Office Access 2003 database and:
- Another database Use the TransferDatabase method to carry out the TransferDatabase action in the procedure.
- A SQL Server database Use the TransferSQLDatabase method to transfer an entire Microsoft SQL Server database to another SQL Server database.
- A spreadsheet file Use the TransferSpreadsheet method to carry out the TransferSpreadsheet action in the procedure.
- A text file Use the TransferText method to carry out the TransferText action in the procedure.
- An XML file Use the ExportXML or ImportXML method to export to or import from an XML file, respectively.
For more information about these methods, search for "TransferDatabase Method," "TransferSQLDatabase Method," "TransferSpreadsheet Method," "TransferText Method," "ExportXML Method," and "ImportXML Method" on the MSDN Library site.
- Click Run Sub/UserForm on the Run menu in the Visual Basic Editor (Visual Basic Editor: An environment in which you write new and edit existing Visual Basic for Applications code and procedures. The Visual Basic Editor contains a complete debugging toolset for finding syntax, run-time, and logic problems in your code.) to run the procedure.
Tip 
The Expression Builder in Access 2010 uses IntelliSense to show what arguments your expression requires. Read an article or try Office 2010.
Often, you might want to bring in data from the same source file to the same destination file, and it might be a good idea to automate the import, export, or link operation. To do this, automate the execution of the macro or the procedure that performs the operation. For example, to import data from an Excel spreadsheet into an existing database each week before you print a report, add code to run the macro or procedure in the Load event of the report. The import operation will start when the report is loaded, and you will be able to preview and print the report with the latest data.
Top of Page
