Results 1 to 12 of 12
  1. #1
    BrightSoftware is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    45

    Transaction and table lock?

    How can I prevent an entire table from being locked in a DAO transaction?

    if a record in the transaction is added to the table, the whole table is locked
    For example, a dlookup on the same table is not possible before the transaction is committed



    I also tried ADO, but the same problem there too

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Think we need to see your code

  3. #3
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Generally speaking transactions are an unnecessary complication within an Access / SQL environment.
    Why do you think you need to use one?

    As per Ajax we need more detail.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    BrightSoftware is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    45
    Why do you think you need to use one?

    We are testing to go from an access database to a SQL database
    There are a lot of transactions in the code now

    Code to open

    DBEngine.DefaultType = dbUseJet
    Set DwWorkSpace = DBEngine.CreateWorkspace("JetWorkspace1", "admin", "")

    Set DbDatabase(1) = DwWorkSpace.OpenDatabase(CurrentDb.Name, False, False)


    DwWorkSpace.IsolateODBCTrans = True
    DwWorkSpace.BeginTrans



    tSql = "SELECT * FROM TblGroepPaspoort WHERE GroepPaspoort = 5"
    '-
    Set tRs = DbDatabase(1).OpenRecordset(tSql, dbOpenDynaset, dbSeeChanges)

  5. #5
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I have no clue why you would use a transaction to open a recordset like that. What was being done with the recordset afterwards?
    99.9 % of the time you wouldn't need to.

    Do you know why that methodology has been employed?

    SQL Server handles record locking extremely efficiently on it's own internally.
    Placing locks on records unnecessarily only leads to poor performance, and kludge's to work around the issues raised.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    BrightSoftware is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    45
    It is a part of the code.
    Later records are added to the table

    That methodology has been employed because records are added to multiple tables.
    Mostly master detail.
    For example an order with orderlines

  7. #7
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You would normally handle that with a bound main form with the order header details, and a bound subform with the order lines.
    The record handling and locking would be pretty much self handling unless two people were trying to edit (not view) the same order at the same time.
    One would get a message that the record was locked by another user, but in most situations this wouldn't arise.

    An access BE database would handle this pretty well without the need for transactions, a SQL one even better imho.

    I suspect there was more to it for whoever designed the original system, but personally I see no need for a transaction based system here.
    Maybe someone else has a different opinion?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    BrightSoftware is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    45
    It is not in a form
    The data comes in via a web service.
    There is a procedure to deal with this.

  9. #9
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Okay - it would have been preferable to know that up front, your are drip feeding us with absolutely the minimum information here to help you.

    Tell us the whole process, and how it is set up and used at the moment.
    We don't need minute details, a top level description will suffice at this point.
    If this is a upload of imported data I see even less need to lock tables as you are doing simple bulk insert(s) .
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  10. #10
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    If you're going to use SQL server, write a SQL procedure to wrap the whole transaction (insert order + insert orderlines) using the begin tran/ commit [rollback] structure . Use the try/catch structure to determine when to commit or when to rollback. Only thing to do from the web app is calling the procedure with the correct parameters. The database will handle it correctly.
    If you want to allow selects to read all data, disregarding the locks, you can use the nolock option, but be aware that some data in your result can be dirty.

  11. #11
    BrightSoftware is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    45
    Thx for the suggestions
    They just don't solve my problem

    I was able to solve the problem by changing the setting 'Is Read Committed Snapshot On' in True on the SQL server

  12. #12
    Join Date
    Oct 2021
    Posts
    9
    I’ve encountered the same issue at work. Read uncommitted transactions also works. The data flow can be revised so that all your updates hits one table and all your selects hits a copy of the table. The benefits of this is to that heavy operations are done to one table and will not affect tables linked to access.

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

Similar Threads

  1. How to make inventory transaction table
    By BEubanks in forum Database Design
    Replies: 5
    Last Post: 06-12-2018, 01:31 PM
  2. Replies: 7
    Last Post: 02-10-2016, 10:00 AM
  3. Replies: 7
    Last Post: 11-14-2011, 05:59 PM
  4. Copy Transaction and Send to another table
    By jo8701 in forum Access
    Replies: 1
    Last Post: 08-16-2011, 08:46 AM
  5. Transaction v Lookup table
    By jke in forum Access
    Replies: 2
    Last Post: 06-15-2011, 08:44 PM

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