Re: ADOBD / JET Transaction Problems

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

Date: Wed, 29 Sep 2004 09:37:23 +0100

Hi there Doug

Thanks for the reply

The transaction code is:

Public Sub InsertRecords()
On Error Goto InsertRecords_Trap

Dim wksSQL as String
Dim wkbInTrans as Boolean

cnn.BeginTrans ' Or just BeginTrans
wkbInTrans = True

wksSQL = "INSERT INTO Invoices(InvNum, CusNam, InvVal) VALUES('IN123',
'Sharps Enterprises Ltd', 150.00)"
gCurDb.Execute wksSQL, dbFailOnError

wksSQL = "INSERT INTO Invoices(InvErrNum, CusNam, InvVal) VALUES('IN124',
'Sharps Enterprises Ltd', 175.00)"
gCurDb.Execute wksSQL, dbFailOnError

wksSQL = "INSERT INTO Invoices(InvNum, CusNam, InvVal) VALUES('IN125',
'Sharps Enterprises Ltd', 200.00)"
gCurDb.Execute wksSQL, dbFailOnError

cnn.CommitTrans ' or just Committrans

InsertRecords_Exit:
Exit Sub

InsertRecords_Trap:
If wkbInTrans = True then cnn.RollbackTrans ' or just Rollback
Resume InsertRecords_Exit

End Sub

I have deliberately made an error in the second INSERT statement, so it
generates an error and Rollsback. However, when I look in the database, the
first record has been committed to the database

Any ideas?

Many thanks

Gareth Sharp

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:%236HVxbapEHA.3868@TK2MSFTNGP15.phx.gbl...
> I think it's probably more relevant to post the transaction-related code
> you're using.
>
> It looks as though you're trying to use both DAO and ADO concurrently. It
> doesn't work that way: what you'll end up with is, in essence, two
separate
> threads that don't know anything about each other.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "Gareth Sharp" <gs@paxsoft.co.uk> wrote in message
> news:cjc0eu$2i$1$8302bc10@news.demon.co.uk...
> > 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
> >
> >
>
>