ACC1x: Cannot DDE Execute RunSQL Command with Brackets

• Microsoft Access version 1.1

SYMPTOMS

You receive an error message indicating that the process has failed when you use DDE from a DDE client application such as Microsoft Word for Windows or Microsoft Excel to run the RunSQL macro action in Microsoft Access version 1.1.

CAUSE

The SQL statement you are trying to run with the RunSQL command contains square brackets. When Microsoft Access receives the command it assumes that the first right square bracket marks the end of the command. For example, the command

   UPDATE Employees SET [First Name] = 'Jim' WHERE [First Name] =
   'Andrew';

   UPDATE Employees SET [First Name

RESOLUTION

The SQL command must be enclosed in double quotation marks in the DDE execute string.

The SQL command in the example above could be successfully sent as:

   "[RunSQL "UPDATE Employees SET [First Name] = 'Jim' WHERE
   [First Name] = 'Andrew';"]"

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 1.1. This problem no longer occurs in Microsoft Access version 2.0.

MORE INFORMATION

Steps to Reproduce Problem

1. Open the sample database NWIND.MDB.

2. With Microsoft Access still running, start Word for Windows.

3. Create and then run the following Word for Windows macro, which will use DDE to instruct Microsoft Access to change all first names in the Employees table that are Andrew to Jim:

         Sub MAIN
            SQL$ = "UPDATE Employees SET [First Name] = 'Andrew' "
            SQL$ = SQL$ + "WHERE [First Name]= 'Jim';"
   
            chan = DDEInitiate("MSACCESS", "System")
            DDEExecute chan, "[RunSQL " + SQL$ + "]"
            DDETerminate chan
         End Sub
   
   

To correct the problem, enclose the SQL statement in double quotation marks, as follows: