Results 1 to 12 of 12
  1. #1
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23

    Transaction causes exclusive access error so I can't save


    For some reason this code is leaving the database "open" so I can't save anything and have to restart Access. The record is created correctly and it doesn't go to trans_err. Can't see what I've got wrong but it's after 3AM so it's probably really stupid.

    Code:
        'Do transactions
        DAO.DBEngine.BeginTrans
        On Error GoTo trans_Err:
        'Create the Invoice
        sql = "Insert into [tblInvoices] (InvNumber, InvContactID, InvType, InvDate, InvAmount, InvGST, InvPST, InvNotes) "
        sql = sql & "Values (" & Me.InvNumber & ", " & Me.InvContactID & ", 'Bill', #" & Me.InvDate & "#, "
        sql = sql & Amount & ", " & Me.InvGST & ", " & Me.InvPST & ", '" & Me.InvNotes & "')"
        CurrentDb.Execute sql, dbFailOnError
    
        DAO.DBEngine.CommitTrans
        
        Exit Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Access has tables available, so there's no need to create code and transaction SQL.
    Using append queries will do the same thing and not cause errors.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what is your reason for using begintrans/commitrans? - that is intended to be used where multiple tables need to be updated.

    also, you don't have a trans_err to go to

    Suspect you are only showing part of your code so not possible to provide a considered opinion.

    Other info you need to provide - is the table local? in an access backend? another rdbms? your comments imply local

  4. #4
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23
    This will have up to five different inserts which all have to roll back if there's an error.

  5. #5
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Nothing looks obviously wrong to be honest.

    Have a read here https://codekabinett.com/rdumps.php?...ss-transaction to see if any of the record locking tips apply.
    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
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you have 5 inserts that must occur as a logical unit of work, then please show us all the code that applies to the transaction. Your structure in concept should be along this set up:
    Code:
    On Error GOTO XXX
     BeginTrans
      Insert1
      Insert2
      Insert3
      Insert4
      Insert5
    CommitTrans
    ...
    XXX:

  7. #7
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23
    I had the form originally set up use bound controls and in "New" mode. I changed that to no datasource and unbound controls later. I'm thinking maybe something got left behind that's messing me up so I deleted the form and starting again with the unbound method. We'll see what happens.

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Look at the CONTROL SOURCE of the form. If a table or query is still the control source that's your problem. Unbound forms should have no control source.

  9. #9
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23
    No control source - first thing I got rid of when moving away from bound form.

  10. #10
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23
    Redoing the form made the problem go away.

    I think there was a control still bound even though I checked everything five times. Maybe it was a corrupt form that still had a binding that wasn't showing up in the settings or maybe I was just blind and not seeing it.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you tell us more about what this entailed? "Redoing the form..." It may help someone else.
    Glad you have it working.
    If you are still using transaction logic, do you have any sort of audit log?

  12. #12
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23
    Delete the form/code and start again

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

Similar Threads

  1. Replies: 6
    Last Post: 08-08-2016, 06:42 AM
  2. Replies: 3
    Last Post: 06-10-2016, 06:07 AM
  3. Replies: 4
    Last Post: 01-15-2014, 01:00 PM
  4. Replies: 6
    Last Post: 11-15-2013, 07:01 AM
  5. Replies: 7
    Last Post: 06-28-2012, 02:05 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