General information
Title:
SQL Pass Through to Acess Tables with spaces in name
I am having a problem with updating an Access table from a VFP table when the Access table has a space in the name. My code works fine if there is no space in the table name but bombs if there is a space no matter what I do.
I don't show it here but my code has no problems when there are spaces in the field names. It is spaces in the table names that is giving me problems.
The over-simplified and edited code looks something like this.
***START
gnConnHand=SQLSTRINGCONNECT(lcConnString)
=SQLSETPROP(gnConnHand, "Asynchronous", .T. ) && Needed for long table names.
lnPrepare = SQLPrepare(gnConnHand, lcSQLString, 'cRemoteView')
lnExec = SQLExec(gnConnHand)
SELECT cRemoteView
*|
*| Make the Access cursor Updatable.
*|
*| The Access table that we want to update.
=CURSORSETPROP("Tables", lcTable)
*| List of Acces fields with table alias
=CURSORSETPROP("UpdateNameList",lcUpNameLst)
*| The key field used to match both the Access and dbf tables.
=CURSORSETPROP("KeyFieldList",lcKeyField)
*|List of Access fields to update w/o alias'
=CURSORSETPROP("UpdatableFieldList",lcAccessFields)
*| Do the updates.
=CURSORSETPROP("SendUpdates",.T.)
SELECT VFP_Table
SCAN
SCATTER MEMO TO aTransfer
SELECT cRemoteView
GATHER MEMO FROM aTransfer
*| The original Access record will be updated as soon as you move off of the cursor record.
SKIP 1
SELECT VFP_Table
ENDSCAN
***END
Basically the memvars look something like this:
lcTable = "Access Table"
lcUpNameLst = "field1 ACCESS TABLE.FIELD1,field2 ACCESS TABLE.FIELD2"
lcKeyField = "ID"
lcAccessFields = "FIELD1,FIELD2"
With the lcUpNameLst memvar in the above configuration I get an error on the line
=CURSORSETPROP("UpdateNameList",lcUpNameLst)
which says "Invalid Update Column Name "Table".
Obviously the space is making the command see the next word after the space as a column name and not part of the table name.
If I change lcUpNameLst to the form:
lcUpNameLst = "field1 [ACCESS TABLE.FIELD1],field2 [ACCESS TABLE.FIELD2]"
using the brackets as quotes to isolate the table name, I get an error on the command SKIP 1 that says
"No Update tables are specified. Use the Tables property of the cursor."
I have tried [Access Table].field1
I have tried lcTable = "[Access Table]"
but no matter what I try I will get an error on one of the two lines.
Can anybody offer a clue as to what I may be doing wrong?
TIA
Ed
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only