Sorry for the long code snippet, but this one has me buffaloed so far. Scenario is query to extract VFP data, SCATTER to memvars, then need to insert that data into an MS Access Database (Access 2007).

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?