Re: ADOBD / JET Transaction Problems

From: Brendan Reynolds (brenreyn)
Date: 09/29/04 

Date: Wed, 29 Sep 2004 13:36:31 +0100

It's as Doug said earlier - the transaction is associated with the ADO
Connection object, but you're updating the database via the DAO Database
object. They're completely independent. Rolling back the ADO Connection will
not undo any changes made via the DAO Database, because the changes were
never part of that transaction. Try making the changes using cnn.Execute
instead of gCurDb.Execute. 

-- 
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com
The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
"Gareth Sharp" <gs@paxsoft.co.uk> wrote in message 
news:cjds69$jif$1$8302bc10@news.demon.co.uk...
> 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
>> >
>> >
>>
>>
>
>

Relevant Pages

  • Re: Help - Timing Logic
    ... server application, both of which ran on the same box. ... the client applications and 'lodging' them in the database. ... Another part of the server application was dedicated to retrieving messages ... commit transaction ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Help - Timing Logic
    ... put a trigger on the database table. ... Transaction and Database Locking - look at isolation levels / settings ... Maybe implement a message broker ... ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Help - Timing Logic
    ... build a trigger on the database .. ... Transaction and Database Locking - look at isolation levels / settings ... If you need to implement a locking mechanism / or / logging mechanism / or / a checking mechanism to avoid duplicate messages caused by multi-threading ... ... message broker gets all the necessary messages or message id's from the database to be sent ... ...
    (microsoft.public.dotnet.languages.vb)
  • Re: ADOBD / JET Transaction Problems
    ... Sorry, Gareth, I don't have an opinion on that. ... >> It's as Doug said earlier - the transaction is associated with the ADO ... but you're updating the database via the DAO Database ... >>> Public Sub InsertRecords() ...
    (microsoft.public.vb.database.dao)
  • Re: ADOBD / JET Transaction Problems
    ... Sorry, Gareth, I don't have an opinion on that. ... >> It's as Doug said earlier - the transaction is associated with the ADO ... but you're updating the database via the DAO Database ... >>> Public Sub InsertRecords() ...
    (microsoft.public.data.ado)