VBA & Macro Problems

How to Fix VBA RunSQL Argument Not Optional Error

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to resolve the 'Argument not optional' compile error encountered when executing SQL statements via VBA.

Product: VBA
Device & OS: not provided
Scenario: Attempting to run an UPDATE SQL query on a table with a hyphenated name using DoCmd.RunSQL in VBA.
Observed behavior: VBA throws a 'Compile error: Argument not optional' due to incorrect syntax involving an equals sign, missing spaces in SQL clauses, and unprotected hyphenated table names.

Before you start

Review your VBA code to ensure all string concatenations have proper spacing and that table names with special characters or hyphens are enclosed in square brackets.

Solution 1

Remove the Equals Sign from DoCmd.RunSQL

The 'Argument not optional' error directly stems from incorrectly assigning a value to the RunSQL method using an equals sign.

In VBA, methods that perform actions (like DoCmd.RunSQL) should be called without an equals sign unless you are returning a value to a variable.

Locate the problematic line

Open your VBA editor and find the line containing DoCmd.RunSQL.

Correct the syntax

Remove the equals sign (=) after the method name. The correct syntax is DoCmd.RunSQL strSQL.

Protect hyphenated table names

Ensure your table name is enclosed in brackets. For example, change merged-csv-files to [merged-csv-files].

Fix SQL clause spacing

Verify that each line of your concatenated SQL string ends with a space so clauses do not merge incorrectly (e.g., 'UPDATE [merged-csv-files] ').

Solution 2Recommended

Replace DoCmd.RunSQL with CurrentDb.Execute

CurrentDb.Execute is the recommended method for running action queries because it handles database errors more effectively without triggering default prompts.

Free Microsoft Office alternative

Edit Macros and VBA Smoothly with WPS Office

While database-specific VBA methods like DoCmd.RunSQL apply to Microsoft Access, you can handle extensive spreadsheet macros, data automation, and VBA scripts effortlessly using WPS Office. It provides excellent compatibility with Microsoft Office formats at zero cost.

1. Download and install: Download WPS Office Free from the official website and complete the installation.
2. Open existing workbooks: Open your existing Excel workbooks containing macros and VBA code.
3. Access the VBA editor: Navigate to the Developer tab to open the built-in VBA editor and continue working on your scripts.

Excellent Microsoft Excel VBA and macro compatibilityFully compatible with Microsoft Office formats (.xlsx, .docx, .pptx)Free, lightweight, and fast office suiteFamiliar user interface for seamless migration

microsoft office alternative - wps office

Frequently Asked Questions

Why do hyphenated table names cause errors in VBA SQL?

In VBA and SQL, hyphens are treated as subtraction operators. To prevent the compiler from misinterpreting the hyphen, table or column names with hyphens must be enclosed in square brackets, such as [merged-csv-files].

How do I properly concatenate SQL strings across multiple lines in VBA?

Use the ampersand (&) and underscore (_) characters at the end of each line to continue the string. It is crucial to leave a trailing space before the closing quote on each line so the SQL keywords do not run together into an invalid syntax.

What is the difference between DoCmd.RunSQL and CurrentDb.Execute?

DoCmd.RunSQL often triggers built-in application warning prompts (like 'You are about to update 5 rows') and lacks robust error handling. CurrentDb.Execute bypasses default warnings and allows you to use parameters like dbFailOnError to catch specific data modification errors directly in your VBA code.

Excel & Data Problems