ADOBD / JET Transaction Problems

From: Gareth Sharp (gs_at_paxsoft.co.uk)
Date: 09/28/04 

Date: Tue, 28 Sep 2004 16:38:07 +0100

Hi all!

Firstly, I am using VB5, a password protected Access 97 database, DAO3.6,
and ADO2.8

I am trying to overcome page level locking using an Access 97 database, by
using ADODB to open the database to use record level locking.

This seems to work fine, but the transactions seem to not be working
properly. If I use BeginTrans, then do 3 "INSERT INTO " queries, then
Rollback, the Rollback does not Rollback the transactions - all 3 are
processed, and committed to the datebase. I have tried changing it to
cnn.BeginTrans, etc.

The following is the code that I am using to open the database - is there
anything obviously wrong with this?

    Global gCurDb As Database
    Global wsDAO As DAO.Workspace
    Global cnn As ADODB.Connection

Public Sub OpenDB

   Set cnn = New ADODB.Connection
   cnn.Provider = "Microsoft.JET.OLEDB.4.0"
   cnn.Properties("Data Source") = "v:\data\TestDatabase.mdb"
   cnn.Properties("Jet OLEDB:Database Locking Mode") = 1 ' record level
locking
   cnn.CursorLocation = adUseServer
   cnn.Properties("Jet OLEDB:Database Password") = "123456"
   cnn.Open

   Set wsDAO = DBEngine.CreateWorkspace("WorkSpace", "Admin", "", dbUseJet)
   Set gCurDb = wsDAO.OpenDatabase(DBFile, False, False, ";pwd=" &
gDBPassWord)

End Sub

All the best!
Gareth Sharp
Technical Director
Paxton Computers Ltd
website: http://www.paxsoft.co.uk
eMail: mailto:gs@paxsoft.co.uk

Relevant Pages

  • ADOBD / JET Transaction Problems
    ... I am trying to overcome page level locking using an Access 97 database, ... Rollback, the Rollback does not Rollback the transactions - all 3 are ... Public Sub OpenDB ...
    (microsoft.public.vb.database.dao)
  • Re: just wondering about MSKB 828481
    ... seems to depend on the database being in other than ... > rollback I'll be OK. ... SQL Server doesn't, AFAIK, offer any native method for unravelling ... Steve Foster [SBS MVP] ...
    (microsoft.public.windows.server.sbs)
  • Re: How to transactionally update a DB Schema?
    ... You can execute DDL as part of an explicit transaction so that you can ... rollback in the case of an error. ... database prior the upgrade and restore if an error is encountered. ...
    (microsoft.public.sqlserver.server)
  • Re: Recovery from Delete statement using the transaction logs
    ... If you did, just do ROLLBACK. ... Are you performing regular transaction log backups? ... Is database in full or bulk logged recovery ...
    (microsoft.public.sqlserver.server)
  • Re: ROLLBACK a transaction
    ... execute your select query, but it would ignore all non-committed records as ... > I'm reading records from one database to another database, ... We have to do a rollback of all the ... > Is there a solution for this whitout changing the select to "......with ...
    (microsoft.public.sqlserver.programming)