If I open an ODBC connection using ADO, I am able to open a recordset
on a table with a serial type in it, do an AddNew, set my fields and
call Update. At that point the serial field in the recordset is
populated with the newly allocated value.
Now, if I use Access's ADO connection object
(CurrentProject.Connection) which uses ODBC linked tables (linked
using the same ODBC connection string I used above) I get a "record is
deleted" error when I try to access the serial field in the recordset
after Update is called (running exact same code, just using a
different connection object).
I have tried to dig through the ODBC debug log and about the only
thing I can figure out is that straight ADO does a SQL_CURSOR_TYPE = 1
while the ODBC linked tables never set the cursor type value (so it is
defaulting to 0). Also with all ADO I get dequeueNeedDataCallback
ret=0 count=2 after the insert (ODBC linked tables has count=0). With
the non-zero count a query that looks like "SELECT * , "ctid",
"primary_key_serial_field_name" FROM table_name WHERE ctid =
currtid(0, '(,)')" is issued. When the count is 0 this never happens.
Instead a reselect of the original recordset open SQL is issued with
the extra criteria of primary_key_serial_field_name = null (since it
doesn't know the new serial value).
I have tried pretty much every Open recordset option with no luck. I
also tried using DAO and the LastModified bookmark but this generates
the exact same error. Anyone have any ideas on how to get the serial
value of a newly added record from an Access ODBC linked table? Is
this difference happening because Access is not setting the cursor
type? Is there any way to get a different default behavior? I tried
looking through the ODBC code without much luck in finding much.
Thanks for any ideas or help.
Mike