getting lastModified in access2007

[Roger]

I'm creating a transaction using DAO to update linked sql server
tables

the transaction involves many UPDATEs, INSERTs and dao.recordsets

it seems that once I create new record using DAO's .addNew, further
attempts so select data using
set rs = currentdb.openRecordset(strsql, dbOpenSnapshot,
dbReadOnly) fails

I tried various combinations for parameters 2 & 3, with no luck. In
all cases looking at the sql server activity monitor shows that the
transaction is blocked by the earlier .addNew / .update function

I tried to use ADODB with the same issue
is there an openrecord that will not be blocked ?

so now, I'm going to try using an INSERT statement instead of .addNew,
but the table has an autonumber key, so how can get the value of this
key ?

now I have
rs.addnew
rs!customer = 123
rs.update
rs.bookmark = rs.lastmodified
lngId = rs!id

which will become
strsql = "insert into table (customer) values 123"
currentdb.execute strsql,dbfailonerror
lngid = what ????

 

[Douglas J Steele]

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "insert into table (customer) values 123"
Set db = DBEngine(0)(0)
db.Execute strSQL, dbFailOnError

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
lngid = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing

"Roger" wrote in message

I'm creating a transaction using DAO to update linked sql server
tables

the transaction involves many UPDATEs, INSERTs and dao.recordsets

it seems that once I create new record using DAO's .addNew, further
attempts so select data using
set rs = currentdb.openRecordset(strsql, dbOpenSnapshot,
dbReadOnly) fails

I tried various combinations for parameters 2 & 3, with no luck. In
all cases looking at the sql server activity monitor shows that the
transaction is blocked by the earlier .addNew / .update function

I tried to use ADODB with the same issue
is there an openrecord that will not be blocked ?

so now, I'm going to try using an INSERT statement instead of .addNew,
but the table has an autonumber key, so how can get the value of this
key ?

now I have
rs.addnew
rs!customer = 123
rs.update
rs.bookmark = rs.lastmodified
lngId = rs!id

which will become
strsql = "insert into table (customer) values 123"
currentdb.execute strsql,dbfailonerror
lngid = what ????