ACC97: Compile Error with Converted Macro with RunSQL Statement
ID: Q167066
|
The information in this article applies to:
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
- Open the Northwind.mdb database in Microsoft Access 97 and create a new query in Design view.
- Add the Employees table to the query grid, and on the Query menu, click Update Query.
- In the Field row, select ReportsTo.
- In the Update To row, type 1.
- On the View menu, click SQL View and copy the SQL Statement from the
SQL View window. Close the query.
- 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.
- Run the Macro1 macro.
- On the File menu, click Save As/Export. In the Save As box, click "Save
as Visual Basic Module," and then click OK.
- In the Convert Macro: Macro1 box, click Convert.
- Click OK in the "Macro to VBA Conversion" message box.
- Open the converted macro module in Design view. Note that the RunSQL
Statement has been incorrectly divided into two lines of code.
- 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.
Additional query words:
Keywords : kbprg
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbbug