Access 2010 append query to SQL is very slow


Joined
Jul 6, 2010
Messages
5
Reaction score
0
Hello,

I have a complex Access application for managing golf tournaments. After scores are entered in Access for a round, a button is clicked to upload to a SQL table (connected via ODBC), which feeds the leaderboard website.

Clicking the button starts a process of:
- Confirmations (VPN connected, etc.)
- Run a Delete query that deletes all records in the SQL table for that event (primary key = event_no + player_no)
- Run an Append query to upload the same records from Access (they now contain more score data)

This simple refresh process worked fine for 11 years, but not anymore. The client says the website is crashing while the tournament director is uploading, i.e. people viewing the results online see some kind of "crash" online. The SQL Server version was upgraded following a server crash earlier in the summer - I think that may have something to do with it. Something definitely changed.

Any thoughts on why this would be happening? The SQL table would contain no more than 15,000 records. The queries would be deleting/appending no more than 150 records at a time. The Append query is very slow, even just appending 120 records.

The stop-gap solution last summer was for the tournament director to email the data file to someone inside the network (no VPN required) and they did the upload from there using the same Access front-end.

Thank you,
Peter
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top