ACC1x: Cannot DDE Execute RunSQL Command with Brackets

ID: Q106185

The information in this article applies to:

Microsoft Access 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';

would be interpreted by Microsoft Access to be


   UPDATE Employees SET [First Name

which is not a complete command and cannot be run successfully.

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

Open the sample database NWIND.MDB.

With Microsoft Access still running, start Word for Windows.

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

The error message "WordBASIC Err=503 Process failed in other application" will result.

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


 Sub MAIN
    QT$ = Chr$(34)
    SQL$ = "UPDATE Employees SET [First Name] = 'Jim' "
    SQL$ = SQL$ + "WHERE [First Name]= 'Andrew';"

    chan = DDEInitiate("MSACCESS", "System")
    DDEExecute chan, "[RunSQL " + QT$ + SQL$ + QT$ + "]"
    DDETerminate chan

 End Sub

Keywords : kbinterop
Version : 1.1
Platform : WINDOWS
Issue type : kbbug