Results 1 to 6 of 6
  1. #1
    altemir is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    31

    Best Method to INSERT/UPDATE Records in Linked Tables

    I have an MS Access 2010 front-end and SQL Server 2008 back-end. Users all read/write to a single table (tblProductionOrders). We sometimes encounter problems with deadlocks occurring on this table. My admin tells me that indices have been optimized and I have done my best to reduce and streamline INSERT and UPDATE table trigger activity. The problem is that INSERTS and UPDATES issued from Access take a long, long time and sometimes fail due to timeouts. The tblProductionOrders table contains 2700 records and the bulk INSERTS/UPDATES generally involve no more than five or so records. I don't think I'm doing anything exotic ... just adding or changing a few records.

    What is the best transport mechanism to drive these INSERT and UPDATE transactions??

    Originally, I was using DoCmd.RunSQL to issue the INSERT and UPDATE statements from Access, which as I have said takes a long time (~45 seconds). I have experimented with using a stored procedure on the back-end to INSERT and UPDATE records. I have looked at doing this in two ways: With a DAO query def, which also take a long time and now with an ADO cnn.Execute command, which times out.

    What to do??? Please help me!!

  2. #2
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Hmmm. Indexes have been optimized - presumably also meaning that the tables have been defragged/reorganized, and the table statistics have been updated, then that eliminates kludged up tables at the server from the picture. You could verify that by creating a test transaction at the server to insert the same types of records, taking Access out of the picture. If possible, use the same SQL string Access is passing.

    One advantage to creating a stored procedure is that the resulting query can be analyzed at the server. Hard to tell (from this distance) whether the problem might be an inappropriate SQL statement being passed by Access to SQL Server, or some kind of hand-shaking error getting Access to talk to SQL Server at all, or what.

    There's a whole page of diagnostic techniques here http://technet.microsoft.com/en-us/l...=SQL.105).aspx for a slow running query.

  3. #3
    altemir is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    31
    Thanks for the reply. I have it set up so that records from TableA on the server are being inserted into TableB also on the server. I am using a stored procedure to perform the insert so that Access and the network are taken completely out of the loop as you suggested. I learned that the INSERT INTO statement in the stored procedure was taking just under 30 seconds to run. However, the SELECT portion of the INSERT INTO statement by itself was only 0.29 seconds. I don't know what it is about negotiating the INSERT that is taking so long but at least now I know that it is a server-side issue.

    Thanks for the helpful pointer!!

  4. #4
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Hmmm. That's just bizarre - 30 seconds is about a century and a half in machine time. Is this a brand new table, or the 2700 record one? Try it on a brand new table, cloned from the first but without data. If that's screaming fast, then copy all the data over and try it again. If it's still screaming fast, then you've learned something. Look for any other process in the vicinity of the other table that is holding record locks, locks in the same partition, and so on. It may even be that a small portion of the existing table is in a logical location that is getting thrashed by some other process. Just some thoughts.

  5. #5
    altemir is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    31
    Wow! Thanks for helping me see the forest through the trees! I did what you said and it made me realize that it was the INSERT trigger in that table that was causing such a long transaction time. Works like greased lightning now that I simplified the trigger. Thanks, Dal!

  6. #6
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    You're welcome. Simple debugging process... Delete complications one at a time until the problem goes away...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 08-18-2011, 02:18 PM
  2. Update different records in a Linked Table
    By Lorlai in forum Import/Export Data
    Replies: 3
    Last Post: 06-14-2011, 02:01 PM
  3. Replies: 10
    Last Post: 12-13-2010, 11:49 PM
  4. INSERT INTO and UPDATE to multiple tables
    By lupis in forum Import/Export Data
    Replies: 6
    Last Post: 05-19-2010, 05:21 AM
  5. auto update of two linked tables
    By colenzo in forum Access
    Replies: 4
    Last Post: 07-20-2009, 09:08 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums