LastModified does not work with SQL Server

[id1234]

I am migrating my Access Database to SQL Server. I use the following to Create a record in one table then create multiple records in a related table. When DB was in Access I set the Bookmark to the LastModified after I create the record in the first DB so I can Reference that record in the related table. However, when I moved DB to SQL Server the LastModified gives me <Row has been Deleted.>

Any Ideas?

Dim db as DAO.Database
Dim rsCriteria as DAO.Recordset
Dim rsQSC as DAL.Recordset

With rsCriteria
.AddNew
!CriteriaTabLocation = strTab
!Title = strTitle
!Description = strDesc
!ActiveStatus = 1
strTemp = "SELECT * FROM CRITERIA_ITEM_TYPE WHERE CRITERIA_ITEM_TYPE.CriteriaItemType = 'Criteria1'"
!CriteriaItemTypeID = db.OpenRecordset(strTemp)!CriteriaItemTypeID 'rsCIT
'.Bookmark = .LastModified
.Update
.Bookmark = .LastModified 'In SQL Server this returns <Row has Been Delete

End With

With rsQSC

.AddNew
!QueueID = rsQueue!QueueID
!ServiceTypeID = rsService!ServiceTypeID

!CriteriaItemID = rsCriteria!CriteriaItemID 'This is where I need to Reference the record created above.
.Update

​

End With

​

 

[darbid]

Any Ideas?

I see that nobody has given an answer to this question. I am not an expert but so I might not answer your question but I will work with you.

With rsCriteria

Before this line I would have thought you need to set a Database object and then set a RecordSet object.

Then addnew
Then update
then get the last modified.


​

 

[LPurvis]

Yes, you really need to include all your code (even if it doesn't seem relevant where you've created the recordset - it is completely relevant).

Is the table linked? Does it have a Primary Key? (Is that key acknowledged when you examine the design through the Access table designer?)

Cheers.