Solved Record is Deleted Error on Linked SQL Server database in Access (1 Viewer)

[RogerCooper]

I recently changed an Access native table to a linked SQL Server table.

When I ran a loop to edit individual records in the database (in a situation where I could not run a query), I started to get "Record is deleted" errors.

After much searching I discovered, that sometimes the ODBC driver fails with composite keys. The fix was creating an ID field as key working like an Access Autonumber.

I am posting this to help others who run into the same problem.

 

[Pat Hartman]

Thanks for the report.

When I ran a loop to edit individual records in the database (in a situation where I could not run a query)

You always run a query even when you open a recordset directly from the table. I have never encountered this problem probably because I almost always use autonumbers as the PK for tables with a multi-field index to enforce uniqueness if the business rules require it.

If you have an autonumber in a table, it should ALWAYS be the PK. Make a unique index to enforce business rules if neccssary.

I'm also pretty sure this issue could have been resolved by adding a timestamp (RowVersion) field which I always add to tables when I upsize. It helps to make the update process more efficient for Access since it only has to check the timestamp to find out if there has been an intervening update.

 

[Isaac]

I recently changed an Access native table to a linked SQL Server table.

View attachment 108255

When I ran a loop to edit individual records in the database (in a situation where I could not run a query), I started to get "Record is deleted" errors.

After much searching I discovered, that sometimes the ODBC driver fails with composite keys. The fix was creating an ID field as key working like an Access Autonumber.

I am posting this to help others who run into the same problem.

Hi Roger,

I appreciated that you took the time to post this, but feel I don't fully understand.

Can you expound with more detail on describing the situation if you have time?

'ODBC driver fails with composite keys' - are you referring to the actual DDL of the SQL Server table? If so, can you post the ddl that didn't work, and the fix that did?

For what it's worth, I am pretty firm believer in using the simple SQL Server identity for ID columns.

parent table = ParentTable ID bigint identity(1,1) and making it the PK
child table =
ChildTable ID bigint identity(1,1), ParentTableID bigint,
CONSTRAINT FK_ParentTable_ID FOREIGN KEY (ParentTableID) REFERENCES ParentTable (ID)

Composite for me mostly comes into play for indexes, sure, but PK's are usually just an identity column.

Anyway, in your situation, while I don't fully understand what happened, it makes me think also of the potential impact of various ways of opening a recordset (?) and whether dbSeechanges, etc? But without seeing your code nor your t-sql ddl I'm very unsure, just wondering out loud

 

[Isaac]

You always run a query even when you open a recordset directly from the table.

Pretty sure OP was referring to the difference between using VBA + recordsets , versus executing a saved query

 

[Pat Hartman]

And I thought he was talking about the difference between opening a recordset based on the tabledef rather than a recordset on a querydef

 

[Isaac]

And I thought he was talking about the difference between opening a recordset based on the tabledef rather than a recordset on a querydef

Ahh - gotcha. Yeah, for that basically six to one..