ACC97: Compile Error with Converted Macro with RunSQL Stmt

• 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. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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
Solution Type     : kbworkaround