>lcSourceFile = "MyAccess.accdb" && comment out for production *c >pnConnHandle = SQLSTRINGCONNECT("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + ; > lcSourceFile) >IF pnConnHandle > 0 > lnResult = SQLSETPROP(pnConnHandle,"Transactions",2 ) && added this after previous failures; did not help > > IF lnResult <= 0 && no error here > AERROR(laError) > MessageBox(laError[1,2]) > ENDIF > > *- insert values from VFP SCATTER command > lcSQLCommand = "INSERT INTO [TblHousehold] (SYSTEMPROGRAMCD, PROGRAMCD,LSTUPDTDTTM) VALUES (?m.sysprogcd,?m.programcd,?m.lstupdtdttm)" > lnResult = SQLEXEC(pnConnHandle,lcSQLCommand) && this works > WAIT WINDOW TRANSFORM(lnResult) NOWAIT > > IF lnResult <= 0 && no error here > AERROR(laError) > MessageBox(laError[1,2]) > ENDIF > > lcSQLCommand = "SELECT @@Identity FROM [TblHousehold]" > lnGetData = SQLEXEC(pnConnHandle, lcSQLCommand) && this works > lnHouseholdSeqNbr = sqlresult.expr1000 && always the same field name? > USE IN (SELECT('sqlresult')) > lcSQLCommand = "SELECT householdversionnbr FROM [TblHousehold] WHERE householdseqnbr = " + TRANSFORM(lnHouseholdSeqNbr) > lnGetData = SQLEXEC(pnConnHandle, lcSQLCommand,'myvernum') && this works > > IF lnGetData <= 0 && no error here > AERROR(laError) > MessageBox(laError[1,2]) > ENDIF > > lcHouseholdVersionNbr = myvernum.householdversionnbr > > lnResult = SQLCOMMIT(pnConnHandle) && added this after previous errors without it; made no difference > > IF lnResult <= 0 && no error here > AERROR(laError) > MessageBox(laError[1,2]) > ENDIF > > lcPersonSeqNbr = 1 && tried this as a string also > lcHouseholdVersionNbr = VAL(ALLTRIM(lcHouseholdVersionNbr)) && tried this both numeric and string > lcSQLCommand = "INSERT INTO [tblHouseholdMember] (" + ; > "HOUSEHOLDSEQNBR," + ; && shows as Number type in Access > "HOUSEHOLDVERSIONNBR," + ; && Shows as Text type in Access > "PERSONSEQNBR," + ; && shows as Text type in Access > "LASTNAME)" + ; > "VALUES (" + ; > "?lnHouseholdSeqNbr," + ; > "?lcHouseholdVersionNbr," + ; > "?lcPersonSeqNbr," + ; > "?m.lastname)" > >>Everything above works fine, the failure occurs on the next line:
> lnResult = SQLEXEC(pnConnHandle,lcSQLCommand) && this fails > WAIT WINDOW TRANSFORM(lnResult) NOWAIT > > IF lnResult <= 0 && error is here: > AERROR(laError) > MessageBox(laError[1,2]) && "Connectivity error: [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented" > ENDIF > >ELSE > WAIT WINDOW 'Connection failed.' NOWAIT >ENDIF >>And yet, the following query line works just fine from within Access itself on the same tables.
>INSERT INTO [tblHouseholdMember] (HOUSEHOLDSEQNBR,HOUSEHOLDVERSIONNBR,LASTNAME,personseqnbr)VALUES (870,1,'test',4) >>I'm inexperienced with SQLEXEC; anybody have any idea as to what I'm missing?
text to lcSQL textmerge noshow insert into myAccessTable (field1, field2, field3) values (<<Val1>>, '<<Val2>>', '<<Val3>>') endtextThe above is not recommended approach, I suggest it just to try.