Updating Access Database from VFP

[peterf]

I am trying to update/insert a record in a third party MS Access Database table from VFP using remote views. Unfortunately two of the field names are 'Date' and 'Type', which prevents the insert command working. The replace / append command works, but I cannot update or change the data in those two fields.

Any suggestions. Can it be done using sqlconnect commands.

 

[Neil Toulouse]

Not sure if this is of any help as I don't know anything about Access!

However, I had a similar problem with VFP and SQL Server with a couple of reserved word filed names (WHEN and CURRENT).

The way round it was to surround the offending field name with square brackets, eg:

INSERT INTO Table1 ( [WHEN], [CURRENT] ) VALUES (1,2)

Might be the same kind of thing with Access... "I love work. I could sit and stare at it for hours..."

 

[peterf]

The use of square brackets works in VB and SQL Server, but does not work in VFP. This is proving to be quite a problem since I does not have the ability to modify the Access database being third party software.

This is not a MS Access Question

 

[Neil Toulouse]

I have just tested linking to an Access database containing two fields (Date and Type) via ODBC and then use SQLCONNECT to insert data into the table with no problems.

LOCAL lnH

lnH = SQLCONNECT( 'ACCESS_ODBC' )
SQLEXEC( lnH, 'INSERT INTO TABLE1 VALUES ( '11/09/1970', 'Date' )
SQLDISCONNECT( lnH )

The date has to be sent as a string, so yes it can be done this way. "I love work. I could sit and stare at it for hours..."

 

[rgbean]

Just curious (since you've got the code all setup), does the following work:

SQLEXEC(lnH,'INSERT INTO TABLE1 ([date],[type]) VALUES ('11/09/1970','Date')


Rick

 

[peterf]

The question was 'How does one update an access database table with a field name of date using Remote Views'
For completeness of this thread and in answer to thread 184-89215 (which was never answered because the developer was in a position to modify the field name), this is what my research shows.

i. It is not possible using remote views to update or insert a record in an MS Access tables with a field name 'Date',if you want to put data in that field.

ii. Using SQL pass-thru, I was unable to get the field to retain the data after the sqlcommit command (necessitating a tablerevert()),using the method in the previous answer.

iii. The only way I was able to get it to work was by using square brackets around the date field in the UpdateNameList property as in

?cursorsetprop('UpdateNameList',"transno datacollection.transno,date datacollection.[date]&quot

Thank you for all your help

Peter F Lee

 

[Neil Toulouse]

Hi Rick!

Sorry for the response delay! The command:

SQLEXEC( lnH, "INSERT INTO TEST ([Date], [Type]) VALUES ( '11/09/1970', 'Date' )" )

...works just fine.

Neil "I love work. I could sit and stare at it for hours..."