0

Is there anything that would cause an UPDATE statement INNER JOINed between a separate MS-Access linked table and an SQL Server 2008 R2 table to hang, when the values are being set on the SQL Server side of the join?

I already checked the MS-Access linked table to see if there was an issue with corrupted records (sorting the table by various columns to find one), but I didn't find any.

I also tried compacting the .mdb that makes the calls.

I'm thinking about re-indexing the table in question in SQL Server 2008 R2, I'm not sure if that will help. Is there anything else I can try?

2

If I understand your question correctly, you are doing a join in one MDB, based on a table in a different MDB and another table in SQL Server.

In this scenario, I would expect Access might need to read all of the data in both tables to be able to complete the join, and may not be able to do any optimizations or filtering until it has both datasets in memory. How big are the two tables? If either of them are large (10,000s of rows), it may take a while to run - although I would not expect it to hang permanently.

One option that may or may not be practical in your situation is to import the data to local tables before doing the join. Another generic approach I would recommend is to offload as much of the heavy-lifting to SQL as possible - it is much better suited for working with large datasets.

If you can give some more details about the nature of the hang (temporary? permanent?) and the data (how many rows & columns) that would help us troubleshoot.

A troubleshooting step you can consider is to eliminate all the data columns from your query - just have the Keys. If that works, then slowly add the data columns back in until you can establish which one is causing the hang.

| improve this answer | |
  • It's updating 32,506 rows!!!! Everytime! It's not my code, I simply maintain this thing. And it appears to set 3 fields. – leeand00 Nov 4 '15 at 23:35
  • There are about 2,300,000 rows x 7 columns in SQL server, and about 1,177,000 rows x 50 columns in MS Access. – leeand00 Nov 4 '15 at 23:41
  • (and actually this is my first time looking at this particular database in question, not the ones it accesses but the VBA code for this mdb file that gets called on a schedule) – leeand00 Nov 4 '15 at 23:45
  • With that many rows I would definitely recommend off-loading the work to SQL Server. Although it might be a bit of work to get to that point, the performance will be much better under SQL than Access. – Mike Nov 4 '15 at 23:45
  • 1
    So I should dump the MS Access table to SQL Server and do the join / update from there...and then delete the temp table? All of this has to run from the MS-Access database....doesn't it? Or would I have to move all of the script to SQL Server and run it from there? – leeand00 Nov 4 '15 at 23:52
1

I would recommend setting this up as a 2 step process.

Step 1. Import table from Access to SQL server

Step 2. Run UPDATE with join to local table which was imported from Access.

This approach would make the job more reliable and easier to troubleshoot.

Good luck!

| improve this answer | |
  • The table's in an external application, but i can try it...how will it make it easier? – leeand00 Nov 4 '15 at 23:02
  • It will be easier to maintain because there is no networking in between anymore. It will be infinitely faster too. How is the data populated in Access? If you have a SQL Server database, it should be used and access dumped. – Nicolas de Fontenay Nov 5 '15 at 5:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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