Microsoft KB Archive/142191

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

Article ID: 142191

Article Last Modified on 2/12/2007

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 7.0 Professional Edition
 * Microsoft Visual FoxPro 8.0 Professional Edition
 * 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 ' .~ '.

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."

 Create a datasource to a Microsoft Access database.  Establish a connection. chandle=SQLCONNECT * Select available datasources. Select datasource for Microsoft Access. IF chandle < 0 WAIT WINDOW "Bad connection" RETURN ENDIF   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   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   Disconnect the connection handle =SQLDISCONNECT(chandle) 

<div class="status_section">

STATUS
This behavior is by design.

<div class="moreinformation_section">

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

Additional query words: VFoxWin

Keywords: kbcode kbprb KB142191

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.