Re: ADOBD / JET Transaction Problems
From: Gareth Sharp (gs_at_paxsoft.co.uk)
Date: 09/30/04
- Next message: Brendan Reynolds: "Re: ADOBD / JET Transaction Problems"
- Previous message: Don: "VB reading an excel file"
- Next in thread: Brendan Reynolds: "Re: ADOBD / JET Transaction Problems"
- Reply: Brendan Reynolds: "Re: ADOBD / JET Transaction Problems"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 30 Sep 2004 16:24:02 +0100
Hi there
Understood!
Do you think the best solution would be to upgrade to ADO and SQL and VB 6
for proper Record Level Locking? Unfortunately, our product has about 450
forms to re-code!! Any shortcuts would be appreciated!
All the best!
Gareth
"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
news:%23Ra12DipEHA.516@TK2MSFTNGP09.phx.gbl...
> 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
> >> >
> >> >
> >>
> >>
> >
> >
>
>
- Next message: Brendan Reynolds: "Re: ADOBD / JET Transaction Problems"
- Previous message: Don: "VB reading an excel file"
- Next in thread: Brendan Reynolds: "Re: ADOBD / JET Transaction Problems"
- Reply: Brendan Reynolds: "Re: ADOBD / JET Transaction Problems"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
