ACC97: Compile Error with Converted Macro with RunSQL Stmt

ID: Q167066

The information in this article applies to:

Microsoft Access 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you convert a macro to Visual Basic for Applications code using the "Save as Visual Basic Module" command, and the macro contains a RunSQL action that includes a SQL Statement that was copied from a query's SQL View window, you may receive a compile error when you try to compile the module.

CAUSE

The RunSQL Statement is not being correctly copied to the module during the conversion and is causing the module to fail.

RESOLUTION

There are two ways to resolve this problem:

Edit the SQL Statement Action Argument in the macro and remove the space character to the left and right of the equal sign (=). Save, close, and convert the macro again.
Edit the code in the converted macro module, and remove the extra line.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 97.

MORE INFORMATION

Steps to Reproduce Problem

 1. Open the Northwind.mdb database in Microsoft Access 97 and create a
    new query in Design view.

 2. Add the Employees table to the query grid, and on the Query menu, click
    Update Query.

 3. In the Field row, select ReportsTo.

 4. In the Update To row, type 1.

 5. On the View menu, click SQL View and copy the SQL Statement from the
    SQL View window. Close the query.

 6. Create a new macro and add the RunSQL action to the macro. Paste the
    SQL syntax from the query in the SQL Statement box. Close and save the
    macro as Macro1.

 7. Run the Macro1 macro.

 8. On the File menu, click Save As/Export. In the Save As box, click "Save
    as Visual Basic Module," and then click OK.

 9. In the Convert Macro: Macro1 box, click Convert.

10. Click OK in the "Macro to VBA Conversion" message box.

11. Open the converted macro module in Design view. Note that the RunSQL

    Statement has been incorrectly divided into two lines of code.

12. On the Debug menu, click Compile Loaded Modules. Note that you receive

    a compile error.

REFERENCES

For more information about using the "Macro to Visual Basic" command, search the Help Index for "converting macros," and then "converting macros to Visual Basic," or ask the Microsoft Access 97 Office Assistant.

Keywords          : kbprg
Version           : 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbbug

Last Reviewed: November 20, 1998