ACC97: Compile Error with Converted Macro with RunSQL Stmt

Last reviewed: August 29, 1997
Article 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. 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


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.