>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?

I see a few minor problems with the way you retrieve identity - I suggest to remove the line with select @@identity and add OUTPUT clause to the first insert statement, so you will have one less statement to run.

Now, I also suggest to use text to lcSQL noshow instead of using one line SQL statements - the query will be easier to read.
Finally, if the above suggestions will not help much, as an experiment, you can use textmerge noshow in the above and use the actual values, e.g.

text to lcSQL textmerge noshow
insert into myAccessTable (field1, field2, field3)
values (<<Val1>>, '<<Val2>>', '<<Val3>>')
endtext

The above is not recommended approach, I suggest it just to try.