Automate importing, exporting, or linking data

Depending on the type of data you're working with, you can automate the importing, exporting, and linking of data by carrying out the TransferDatabase, TransferSpreadsheet, or TransferText actions in a macro or Microsoft Visual Basic procedure.

Use a macro

1. Create a macro.

   How?

   1. In the Database window, click Macros under Objects.
   2. Click the New button on the Database window toolbar.
   3. Add an action to the macro.

      How?

      1. In the Macro window, click the first empty row in the Action column. If you want to insert an action between two action rows, click the selector for the action row just below the row where you want to insert the new action, and then click Insert Row on the Macro Design toolbar.
      2. In the Action column, click the arrow to display the action list.
      3. Click the action you want to use.
      4. In the lower part of the window, specify arguments for the action, if any are required. For action arguments whose settings are a database object name, you can set the argument by dragging the object from the Database window to the action's Object Name argument box.
      5. Type a comment for the action. Comments are optional.
   4. To add more actions to the macro, move to another action row and repeat step 3. Microsoft Access carries out the actions in the order you list them.
2. Do one of the following to import, export, or link data between the current Microsoft Access database and:

   • Another database  Click TransferDatabase in the action list of a blank action row.

   • A spreadsheet file  Click TransferSpreadsheet in the action list of a blank action row.

   • A text file  Click TransferText in the action list of a blank action row.

3. Set the action arguments as appropriate for the action.
4. Click Save to save the macro.
5. Run the macro.

   How?

   To run a macro directly, do one of the following:

   • To run a macro from the Macro window, click Run on the Macro Design toolbar.
   • To run a macro from the Database window, click Macros and then double-click a macro name.
   • On the Tools menu, point to Macro, click Run Macro, and then select the macro from the Macro Name list. Then click a macro in the Macro Name box.
   • Run a macro from a Microsoft Visual Basic procedure by using the RunMacro method of the DoCmd object.

Use a Visual Basic procedure

1. Create a Visual Basic procedure.

   How?

   1. To open a module, do one of the following:
      • To open a new standard module, 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 or report module, open the form or report in Design view, and then click Code on the Form Design toolbar.
      • To open a new class module 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.
   2. Declare the function by typing the Function statement.
   3. Type a function name immediately followed by any function arguments in parentheses. For example, the following declaration for the IsLoaded function specifies strFormName as an argument:

      Function IsLoaded (strFormName As String) As Boolean

   4. Add the Microsoft Visual Basic code that performs the operation or calculation that you want the function to perform.
2. Do one of the following to import, export, or link data between the current Microsoft Access database and:
   • Another database Use the TransferDatabase method to carry out the TransferDatabase action in the procedure.

     How?

     Microsoft Access defines a special object, DoCmd, that you use to carry out macro actions in Microsoft Visual Basic procedures. You carry out an action by adding a method of the DoCmd object to your procedure. Most actions have a corresponding DoCmd method.

     • In the procedure, add the DoCmd method that corresponds to the action by using the following syntax:

       DoCmd.method [arguments]

       Method is the name of a method. Arguments are the method arguments, if there are any.

     For example, to create a procedure that carries out the OpenForm action, add the OpenForm method of the DoCmd object to the procedure. The following method is equivalent to the OpenForm action; it opens the Add Products form:

     DoCmd.OpenForm "Add Products"

     A few actions don't have corresponding DoCmd methods, although some have equivalent Visual Basic statements or functions.

     Macro actions that don't have a corresponding DoCmd event

     Action	Microsoft Visual Basic equivalent
     AddMenu	No equivalent
     MsgBox	MsgBox function
     RunApp	Shell function
     RunCode	Procedure call (Call statement)
     SendKeys	SendKeys statement
     SetValue	Assignment statement (Let statement)
     StopAllMacros	Stop or End statements
     StopMacro	Exit Sub or Exit Function statements

   • 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.
3. Click the Run Sub/UserForm command on the Run menu in the Visual Basic Editor to run the procedure.