Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
Are there any foreign keys referencing the table you're deleting records from? –  db2 Dec 9 '13 at 20:35
    
The table in question, PaperIssues, has one foreign key linking it to the main table, and the table that the trigger writes to has PaperIssueID as a foreign key. There is also a view of PaperIssues (which has all the same fields as PaperIssues). –  Ife104 Dec 10 '13 at 15:42
add comment

1 Answer

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.

share|improve this answer
    
Thanks for your suggestions. You mentioned that there can be problems doing bulk deletes from Access, but I get the delay when I try to delete just one record. I'll still try using a passthrough query and see if that makes any difference. –  Ife104 Dec 10 '13 at 15:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.