You may receive error messages if you use a field name that starts with a tilde character in Visual FoxPro (142191)


The information in this article applies to:

  • Microsoft Visual FoxPro for Windows 3.0
  • Microsoft Visual FoxPro for Windows 3.0b
  • Microsoft Visual FoxPro for Windows 7.0
  • Microsoft Visual FoxPro 8.0
  • Microsoft Visual FoxPro 9.0 Professional Edition
This article was previously published under Q142191

SYMPTOMS

You may receive error messages if you use a field name that begins with a tilde character in Visual FoxPro



When you use Microsoft Visual FoxPro with ODBC to update a remote view, you may receive either one of the following error messages:

Error message 1
[Microsoft][ODBC Microsoft Access 7.0 Driver] Syntax error in update statement.
Error message 2
Connectivity Error: [Microsoft][ODBC Microsoft Access 7.0 Driver] Syntax error in query expression '<tablename>.~<fieldname>'.
This problem occurs when the remote view is visually designed with a tilde (~) character as the first character in a field name. This problem occurs in the following tables:
  • A Microsoft Access 2.0 table
  • An Access 7.0 table
  • An Office Access 2003 table

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
Also, ~first is the primary key of the table. Add one record where ~first = "Adam" and ~last = "Smith."

  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:

138501 Modifying a Remote View Results in a Syntax Error

Modification Type:MajorLast Reviewed:3/11/2005
Keywords:kbcode kbprb KB142191
©2004 Microsoft Corporation. All rights reserved.