John,
Interesting. I haven't used automation with Access. I'm sure it's powerful but it requires to know the object model.
Personally I find it easier to use SQL. My preferred way is to convert an SPT cursor into an updatable view by setting a few properties:
CURSORSETPROP("Tables", "test")
CURSORSETPROP("UpdateNameList", "type test.type,id test.id")
CURSORSETPROP("KeyFieldList", "type")
CURSORSETPROP("UpdatableFieldList", "type, id")
CURSORSETPROP("SendUpdates", .t.)
Now, the cursor "behaves" like a remote view. Obviously setting above properties is also pita, but I wrote a wrapper that builds the settings based on the selected fields, the table's primary key, etc.
I wonder if there's a performance difference between automation and SPT. I noted a huge difference in performance between Access and SQL Server. The above mentioned wrapper I wrote for SQL Server and performance is excellent. However, SPT against Access seems to be much slower. When doing the SQL Server stuff, I was always surprised by the speed of accessing SQL Server with SPT. I found it to be even faster than using the Query Analyzer of SQL Server. Not to mention the possibility of the command window.
>Been waiting for Dave's response about the conditions before posting further (to avoid confusion), but there is a DSN-less way to do it and create a new database besides (Access 2000 with DAO):
#DEFINE DB_TEXT = 10
oAcc=CREATEOBJECT("Access.Application")
oAcc.NewCurrentDatabase("C:\MyData\VFPDATA.MDB")
oMDB=oAcc.CurrentDB
oTbl=oMDB.CreateTableDef("personaldata")
WITH oTbl
oFld1 = .CreateField("name",DB_TEXT,30)
oFld2 = .CreateField("address",DB_TEXT,40)
oFld3 = .CreateField("city",DB_TEXT,20)
.Fields.Append(oFld1)
.Fields.Append(oFld2)
.Fields.Append(oFld3)
ENDWITH
oMDB.TableDefs.Append(oTbl)
oAcc.Quit(0)
RELEASE aAcc
I keep thinking that I might create a "Downsizing Wizard" to automate this (lol).<
Daniel