Access 2010 append query locking SQL table, crashing website

[pdesjardins]

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. 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 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.

Thanks,
Peter

[ItsMe]

Wow, not sure I can be any help. So many dynamics that need to be isolated before determining the relevance of any one. The fact that emailing the tournament results allows for the append/update to occur does not mean much in my opinion. They are probably not using an Access FE to run the SQL.

So that leaves everything from the ODBC connection failing to user permissions at the table level. In other words, the title of your thread does not necessarily jive with the body of your message. I would second guess the description of the symptoms. Did you observe the "upload" crashing?

Watching, first hand, what happens might give some direction on where to look first. I am thinking ODBC connection and invalid drivers or an improper DSN. Just wild guesses though, sorry.

[pdesjardins]

Thanks for the quick reply. Yes, so many moving parts.

When I mentioned that emailing the results allows for the update, I meant that through the VPN the process is slow, apparently locks the table and people viewing the results online see some kind of "crash" online. When they email the data file to someone inside the network and they upload from there, it goes faster and is not as noticeable for people viewing results online.

As for watching first hand, not really possible. I'm in Boston, client is in Atlanta, server/website host is somewhere near them.

Thanks.

[ItsMe]

I get the VPN vs. LAN thing. My thought is they may use a different method at the local level, ie not Access. I was going to mention timeouts but that is usually a client setting thing and you mentioned changes most likely occurred on the server side.

[pdesjardins]

They use the exact same method. They unzip the data file, open up the Access front end, and complete the process from there.

They are definitely pretty long running queries, even if they're just deleting 120 records, then appending 120 records. Never really known why.

[ItsMe]

Then perhaps a security update that changed UDP or TCP settings. This link has some info. Really just a shot in the dark
http://stackoverflow.com/questions/6...erver-over-vpn

This is why I am curious about the behavior from the remote client perspective. Is it failing at the VPN connection or when the client tries something that needs a specific permission.

Another consideration is how the domain controller treats remote users vs. local users. Connecting vs. running an update are different permissions and this could cause a goofy symptom. You connect but then when you try to execute things go south. Maybe Group policies?