PRB: Special Character Causes ODBC Update Syntax Error

• Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b

SYMPTOMS

Using Visual FoxPro with ODBC to update a remote view designed visually with a Microsoft Access 2.0 or Microsoft Access 7.0 table with a tilde (~) character as the first character in a field name may generate either of the two following error messages:

[Microsoft][ODBC Microsoft Access 7.0 Driver] Syntax error in update statement.

-or-

Connectivity Error: [Microsoft][ODBC Microsoft Access 7.0 Driver] Syntax error in query expression '<tablename>.~<fieldname>'.

CAUSE

The updatable field contains a tilde (~) character, which Visual FoxPro converts to an underscore.

WORKAROUND

The following code demonstrates how to use SQL passthrough to accomplish the update to the Microsoft Access table. This example uses a Microsoft Access table named Family that contains these two fields:

   Field 1 = ~first
   Field 2 = ~last 

1. Create a datasource to a Microsoft Access database.



2. Establish a connection.

   
      chandle=SQLCONNECT()
      * Select available datasources. Select datasource for Microsoft Access.
      IF chandle < 0
         WAIT WINDOW "Bad connection"
         RETURN
      ENDIF 




3. Process the update statement, and display a wait window with result.

   
      x=SQLEXEC(chandle,;
         "Update family set [~first]='Eve' where [~first]='Adam'")
      IF x > 0
         WAIT WINDOW "Successful update"
      ELSE
         WAIT WINDOW "Update failed"
         =SQLDISCONNECT(chandle)
         RETURN
      ENDIF 




4. View changes, if the update was successful.

   
      x=SQLEXEC(chandle,"SELECT * FROM family")
      IF x > 0
         BROWSE
      ELSE
         WAIT WINDOW "Select statement failed"
         =SQLDISCONNECT(chandle)
         RETURN
      ENDIF 




5. Disconnect the connection handle

   
      =SQLDISCONNECT(chandle) 

STATUS

This behavior is by design.

MORE INFORMATION

In Visual FoxPro the tilde (~) is not a valid character for a field name, although this is acceptable in Microsoft Access. Therefore, use of a Visual FoxPro ODBC Remote View to Microsoft Access, converts this special character to an underscore (_) character. The update using the Remote View then fails because Visual FoxPro sends an underscore instead of the tilde that Microsoft Access is expecting.

For more information about Remote View Syntax, please see the following article in the Microsoft Knowledge Base:

Q138501 Modifying a Remote View Results in a Syntax Error

Additional query words: VFoxWin

Keywords : kbcode FxinteropAccess
Version : 3.00 3.00b
Platform : WINDOWS
Issue type :