>I have a need to update the customer number of 29 related btrieve tables running under Pervasive SQL 2000. I am using VFP 6. I have attempted several times to test the update to just one file before I write the loop of code that will update all 29. The problem is that if I key in:
> DBSETPROP("myViewname.cus_no","Field","KeyField", .T.)
> DBSETPROP("myViewname.cus_no","Field","Updatable", .T.)
> DBSETPROP("myViewname","View","SendUpdates",.T.)
> Replace Cus_No with lcNewNumber
> TableUpdate()
>(The first three are taken right out of Client/Server Applications with Visual FoxPro and SQL Server by Urwiler, DeWitt, Levy and Koorhan, p.66)
>
>I get get no errors, but the field cus_no is NOT updated in the underlying table (.BTR file).
>
>If I leave out the first statement, I get "No key columns specified for the update table "TheRealFileName". Use the KeyFieldList property of the cursor"
>
>Is it not possible to alter the key field in a btrieve file? Or am I doing something wrong here? I really need to find a way to get all these custmer numbers changed to a a new numbering scheme. Help!


Hello Barbara:

Without trying too hard to remember how I got to this point, this is an example of something that works for me with a Btrieve file -- I'm not certain that you can update a key field through ODBC, but...

Some of my fields have spaces in their names -- you query them that way using "" marks, and you can pass dates to date fields as parameters but I can't figure out how to just code them in... You have to get tricky in the DBSetProp updatename for the fields with spaces, as follows:


CREATE SQL VIEW "MyView" ;
REMOTE CONNECT "Myconnection" ;
AS SELECT "ID with spaces", fieldwithnospaces, datefield FROM MYTABLE mytable WHERE fieldwithnospaces = 'something' AND datefield > ?date1



DBSetProp('MyView', 'View', 'UpdateType', 1)
DBSetProp('MyView', 'View', 'WhereType', 3)
DBSetProp('MyView', 'View', 'FetchMemo', .F.)
DBSetProp('MyView', 'View', 'SendUpdates', .T.)
DBSetProp('MyView', 'View', 'UseMemoSize', 255)
DBSetProp('MyView', 'View', 'FetchSize', 50)
DBSetProp('MyView', 'View', 'MaxRecords', -1)
DBSetProp('MyView', 'View', 'Tables', 'MyTable')
DBSetProp('MyView', 'View', 'Prepared', .F.)
DBSetProp('MyView', 'View', 'CompareMemo', .F.)
DBSetProp('MyView', 'View', 'FetchAsNeeded', .F.)
DBSetProp('MyView', 'View', 'Comment', "")
DBSetProp('MyView', 'View', 'BatchUpdateCount', 10)
DBSetProp('MyView', 'View', 'ShareConnection', .T.)

*!* Field Level Properties for MyView

* Props for the MyView.loan_id field.
DBSetProp('MyView.ID_with_spaces', 'Field', 'KeyField', .T.)
DBSetProp('MyView.ID_with_spaces', 'Field', 'Updatable', .F.)
DBSetProp('MyView.ID_with_spaces', 'Field', 'UpdateName', 'MyTable."ID with spaces"')
DBSetProp('MyView.ID_with_spaces', 'Field', 'DataType', "C(29)")

DBSetProp('MyView.fieldwithnospaces', 'Field', 'KeyField', .F.)
DBSetProp('MyView.fieldwithnospaces', 'Field', 'Updatable', .T.)
DBSetProp('MyView.fieldwithnospaces', 'Field', 'UpdateName', 'MyTable.fieldwithnospaces')

DBSetProp('MyView.datefield', 'Field', 'KeyField', .F.)
DBSetProp('MyView.datefield', 'Field', 'Updatable', .T.)
DBSetProp('MyView.datefield', 'Field', 'UpdateName', 'MyTable.datefield')





and after you do your changes you can issue some variation on
? sqlcommit(1)
? tableupdate(1)

BUT, I've also found that doing this is so slow that it's actually sometimes faster to browse the resulting table and hand edit the fields I want to change (in my case often I'm replacing a date with the same new date over and over) -- sometimes doing the above in a SCAN/ENDSCAN, the changes start erroring out, as they pile up on each other!

Good luck!

Kevin Atkins