Is it possible to open additional, unlocked, recordset within a transaction?

Hi all,

I'd like to open a recordset 'within' another transaction (i.e., whilst a transaction is active), but I do not want this recordset to be subject to the transaction locking. Is this possible? The aCondition is actually error handling, and I want errors to be logged if they are not handled correctly. It can't be determined whether a .Rollback would be appropriate until the error is added to the rLogging table

Dim dbs as DAO.Database
Dim rLogging As DAO.Recordset
Dim rFeeder As DAO.Recordset
Dim rTable As DAO.Recordset
Dim wks As DAO.Workspace
  fOpenRecordset dbs, rFeeder, sSQL, dbOpenSnapshot
  fOpenRecordset dbs, rTable, sSODTableName, , dbAppendOnly
  Set wks = DBEngine.Workspaces(0)
  wks.BeginTrans
  Do While Not rFeeder.EOF
    [...]
    If aCondition Then
      'open recordset for append only, not subject to transaction?
      'add record
      'close
    End If
    rFeeder.MoveNext
  Loop
  wks.CommitTrans
  rFeeder.Close
  Set rFeeder = Nothing
  rTable.Close
  Set rTable = Nothing

Open in new window


Is this possible?

Thank you!

K.
katerina-pAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Is this possible?>>

  Yes.  Transactions are on the workspace, so just open another workspace and then open the recordset in that.

Jim.
0
katerina-pAuthor Commented:
Thanks Jim, that's good news! Could you be kind enough to check my structure? (I've not had to do this before). :

Dim dbs As DAO.Database
Dim dbs2 As DAO.Database
Dim rLogging As DAO.Recordset
Dim rFeeder As DAO.Recordset
Dim rTable As DAO.Recordset
Dim wks As DAO.Workspace
Dim wks2 As DAO.Workspace
  fOpenRecordset dbs, rFeeder, sSQL, dbOpenSnapshot
  fOpenRecordset dbs, rTable, sSODTableName, , dbAppendOnly
  Set wks = DBEngine.Workspaces(0)
  wks.BeginTrans
  Do While Not rFeeder.EOF
    [...]
    If aCondition Then
        Set wks2 = DBEngine.CreateWorkspace("", "admin", "")
        Set dbs2 = wks2.OpenDatabase(CurrentDb.Name)
        Set rst = dbs2.OpenRecordset(sSQL)
        rst.AddNew
        [...]
        rst.Update
        rst.Close
        dbs2.Close
        wks2.Close
        Set wks2 = Nothing
    End If
    rFeeder.MoveNext
  Loop
  wks.CommitTrans
  rFeeder.Close
  Set rFeeder = Nothing
  rTable.Close
  Set rTable = Nothing

Open in new window


Many thanks
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Dim wks As DAO.Workspace
Dim dbs As DAO.Database
Dim rFeeder As DAO.Recordset
Dim rTable As DAO.Recordset

Dim wks2 As DAO.Workspace
Dim dbs2 As DAO.Database
Dim rs2 As DAO.Recordset

On Error Goto Error_Procedure

' Note here - snapshot is the worst possible cursor to use performance wise.
' You should only use it if you truely need a snapshot of the data as
' it exists at a given point in time
  fOpenRecordset dbs, rFeeder, sSQL, dbOpenSnapshot
  fOpenRecordset dbs, rTable, sSODTableName, , dbAppendOnly

  Set wks2 = DBEngine.CreateWorkspace("", "admin", "")
  Set dbs2 = wks2.OpenDatabase(CurrentDb.Name)
  Set rst2 = dbs2.OpenRecordset(sSQL)
  
  Set wks = DBEngine.Workspaces(0)
  wks.BeginTrans
  Do While Not rFeeder.EOF
    [...]
    If aCondition Then
        rst2.AddNew
        [...]
        rst2.Update
    End If
    rFeeder.MoveNext
  Loop
  wks.CommitTrans

Exit_Procedure:
  On Error Resume Next

   rst2.Close
   Set rst2 = nothing
 
   dbs2.Close
   Set dbs2 = nothing

    wks2.Close
    Set wks2 = Nothing

    rFeeder.Close
   Set rFeeder = Nothing
   
   rTable.Close
   Set rTable = Nothing 

  Exit Sub

Error_Procedure:
   Msgbox "Err: " & Err.number & " - " & Err.Description
   Resume Exit_Procedure

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Also, you should probably rollback explictly within the error handler.

Jim.
0
katerina-pAuthor Commented:
Thanks again Jim!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.