Deleting Record from Linked Table Slow in MS Access I have a Microsoft Access database that is linked to a SQL Server 2000 backend. It has one form with several subforms, all using information from linked tables. When I try to delete a record from one of the subforms, Access sometimes hangs for 60 seconds before prompting me to confirm that I want to delete the record. After the 60 seconds, it proceeds normally without delays. I haven’t been able to find any pattern as to when it hangs; it seems to happen randomly about half the time. The only pattern is that the delay is always the same length.

The delay occurs somewhere between the Delete and Before Delete Confirm events. We do not have any macros or VBA code behind either event. There is a trigger on the table that records a timestamp of when a record was deleted and some other information about the values of the fields. When I delete a record, the timestamp matches when I originally hit delete (before confirming the delete in the dialog box 60 seconds later), so that suggests that transferring the data TO the server is fine, but perhaps there is a delay in either transferring information BACK to Access, or else some other delay on Access’s end after it sends the delete command. The problem still occurs when the trigger is disabled, so the trigger itself isn’t causing the delay.

I have seen similar questions about slow deletes for thousands of records, but this delay occurs when I am just trying to delete one record.

Any ideas about what's causing the delay or suggestions for how to track it down would be greatly appreciated!
It could be a mechanism within MS Access when it comes to the linked table. How ???

MSAccess sees a linked table and you issue this:

DELETE FROM [mytable];


This will not run over to SQL Server and do a deletion of all records in a single transaction. What MSAccess does is pick up every PRIMARY KEY and issues an individual DELETE FROM [mytable] WHERE pk = ???, probably as individual transactions (explains the delay before the confirm). I remembered this wonky behavior going back 15 years because I saw this happen when having linked tables to SQL Server, MySQL, and Oracle.

If this is still what MS Access does to a linked table, you are better off connecting to SQL Server via isql and issuing the DELETE on the server side, especially if there are a lot of rows to delete. At the very least, you should minimize executing bulk operations against SQL Server from MSAccess.

Although I have never worked with it, MSAccess can allow "pass through" queries where you issue a SQL Command and the ODBC Driver simply passes the query to the Server Side for parsing and execution rather that trying to parse and execute the SQL the "ODBC" way.

If things have changed over the years with linked tables, I'll leave it to the SQL Server community to address this if I am way off base.