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!