Transactions [DAO]

' http://www.vb123.com/Toolshed/99_accvb/transactions.htm
option explicit

public sub transaction()

  dim access as Access.Application
  dim db     as Dao.Database

  dim mdb_file_name as string 
  mdb_file_name = "c:\\temp\\dao_test.mdb"
  set access = new Access.Application

  access.OpenCurrentDatabase(mdb_file_name)
  set db = access.CurrentDb

  call do_transaction(access, db, false)
  call do_transaction(access, db, true )

  set db      = nothing
  set access  = nothing

end sub

private sub do_transaction(access as Access.Application, db as dao.Database, commit as boolean)

  dim wrk    as Dao.Workspace
  set wrk = access.DBEngine.Workspaces(0)

  call wrk.beginTrans()
  call select_count(db, "At begin of transaction")

  db.Execute("delete from some_table where theAutonumberField > 100")
  call select_count(db, "After delete")

  if commit then
    
    call wrk.commitTrans()
    call select_count(db, "After Commit")

  else

    call wrk.rollback()
    call select_count(db, "After Rollback")

  end if

  set wrk = nothing

end sub

private sub select_count (db as Dao.Database, txt as string)

  dim rs as dao.recordSet

  set rs = db.OpenRecordSet("select count(*) as cnt from some_table")

  msgBox txt & vbCrLf & "Count: " & rs("cnt")

end sub