Prev: zasto
Next: jeg vil venligs være fri for at modtage meddeelser fra gruppen
From: Jim Bunton on 15 Feb 2010 10:32
Daryl
Yes! you're quite right.

Moral of tale - don't rush to get something done before eeagerly anticipated
lunch!

When I checked (after much apprciated lunch) it does indeed now work aok

The crucial bit is to create a workspace object AND use it as the prefix
before Begintrans and Rollback
i.e myspace.BeginTrans works - just Begintrans DOES NOT

[The use of the DAO. prefix in the Dimension statements is not required but
maybe a cautonary tale here - use it in future anyway in case there are
other issues which may arise.

Thanks for your help - and patience. AND a BIG sorry for my error before
lunch.

Jim


"Daryl S" <DarylS(a)discussions.microsoft.com> wrote in message
news:6B6348C5-5036-4F84-8052-1DA6FF2E9EB7(a)microsoft.com...
> Jim -
>
> With the code you have, I would expect the answer to be 900 since you
> rolled
> back the transaction setting it to 100, and the update to 900 happened
> before
> the BeginTrans.
>
> What isn't working?
>
> --
> Daryl S
>
>
> "Jim Bunton" wrote:
>
>> Dear Daryl & Paul,
>> Thanks for responding to my posting
>> I'm still having the same issue!
>> see code below - I must say I'm just mystified!
>> Note I'm using Microsoft DAO 3.5 Object Library because I'm more used to
>> the
>> syntax and being a bit dinosaur like I guess
>>
>> Jim
>>
>> ** code **
>> Public Sub TestTransaction()
>> Dim cdb As DAO.Database, set1 As DAO.Recordset
>> Dim myspace As DAO.Workspace
>>
>> Set myspace = DBEngine.Workspaces(0)
>> 'Debug.Print currentdb.Name 'works fine but remmed for security
>> 'Msjet40.dll is version 4.0.9511.0
>>
>> Set cdb = currentdb
>> Set set1 = cdb.OpenRecordset("Table1", DB_OPEN_DYNASET)
>> Debug.Print set1.Properties(14).Name, set1.Properties(14)
>> set1.Edit
>> set1!Flat1 = 900
>> set1.Update
>> myspace.BeginTrans
>> Debug.Print "StartValue " & set1!Flat1
>> set1.Edit
>> set1!Flat1 = 100
>> set1.Update
>> myspace.Rollback
>> Debug.Print "EndValue - after rollback! " & set1!Flat1
>>
>> End Sub
>> ** code end **
>>
>> Code output!
>> Transactions True
>> StartValue 900
>> EndValue - after rollback! 900
>>
>>
>> "Paul Shapiro" <paul(a)hideme.broadwayData.com> wrote in message
>> news:eWdnGXDrKHA.728(a)TK2MSFTNGP04.phx.gbl...
>> >I haven't written DAO transactions for a while, but I thought the
>> >.BeginTrans was a method on the Workspace object? I seem to remember
>> >starting the procedure by getting an explicit reference to a Workspace,
>> >starting the transaction, and referencing the workspace object for all
>> >the
>> >data modifications.
>> >
>> > Here's an example from the online help:
>> > Dim wrkCurrent As DAO.Workspace
>> > Dim dbsNorthwind As DAO.Database
>> > Dim rstEmployee As DAO.Recordset
>> >
>> > On Error GoTo ErrorHandler
>> >
>> > Set wrkCurrent = DBEngine.Workspaces(0)
>> > Set dbsNorthwind = CurrentDB
>> > Set rstEmployee = dbsNorthwind.OpenRecordset("Employees")
>> >
>> > wrkCurrent.BeginTrans
>> > Do Until rstEmployee.EOF
>> > If rstEmployee!Title = "Sales Representative" Then
>> > rstEmployee.Edit
>> > rstEmloyee!Title = "Sales Associate"
>> > rstEmployee.Update
>> > End If
>> > rstEmployee.MoveNext
>> > Loop
>> >
>> > If MsgBox("Save all changes?", vbQuestion + vbYesNo) = vbYes Then
>> > wrkCurrent.CommitTrans
>> > Else
>> > wrkCurrent.Rollback
>> > End If
>> >
>> >
>> > "Daryl S" <DarylS(a)discussions.microsoft.com> wrote in message
>> > news:584D0B1F-96F9-465E-BF46-4241AF4C926C(a)microsoft.com...
>> >> Jim -
>> >>
>> >> One thought... Does the recordset (testSet) have the Transactions
>> >> property
>> >> as True? Not all recordsets can use transaction processing. - if the
>> >> property is False, then you can't use Transactions on this recordset.
>> >>
>> >> --
>> >> Daryl S
>> >>
>> >>
>> >> "Jim Bunton" wrote:
>> >>
>> >>> I'm having a bit of an issue with
>> >>> BeginTrans - CommitTrans
>> >>> . . . .
>> >>> Often used it before mainly in Access 97 ! with no problem
>> >>> But now - Access 2000 (wndows XP)
>> >>> using References
>> >>> Visual Basic For Applications
>> >>> Access 9.0 Object Library
>> >>> OLE Automation
>> >>> Microsift DAO 3.5 Object Library
>> >>> Microsoft ActiceX Data Objects 2.1Library
>> >>>
>> >>> Begin Trans CommitTrans doesn't seem to be working as I expect.
>> >>> The code below for example updates the table!
>> >>>
>> >>> **code start**
>> >>> BeginTrans
>> >>> testSet.Edit
>> >>> testSet!flat1 = 999
>> >>> testSet.Update
>> >>> Rollback
>> >>> ** Code End **
>> >
>>
>>
>> .
>>


From: Daryl S on 15 Feb 2010 12:31
Jim -
Glad it is working. Sometimes we just need a break and a little fuel to
regain our perspective and see the issue more clearly!

--
Daryl S


"Jim Bunton" wrote:

> Daryl
> Yes! you're quite right.
>
> Moral of tale - don't rush to get something done before eeagerly anticipated
> lunch!
>
> When I checked (after much apprciated lunch) it does indeed now work aok
>
> The crucial bit is to create a workspace object AND use it as the prefix
> before Begintrans and Rollback
> i.e myspace.BeginTrans works - just Begintrans DOES NOT
>
> [The use of the DAO. prefix in the Dimension statements is not required but
> maybe a cautonary tale here - use it in future anyway in case there are
> other issues which may arise.
>
> Thanks for your help - and patience. AND a BIG sorry for my error before
> lunch.
>
> Jim
>
>
> "Daryl S" <DarylS(a)discussions.microsoft.com> wrote in message
> news:6B6348C5-5036-4F84-8052-1DA6FF2E9EB7(a)microsoft.com...
> > Jim -
> >
> > With the code you have, I would expect the answer to be 900 since you
> > rolled
> > back the transaction setting it to 100, and the update to 900 happened
> > before
> > the BeginTrans.
> >
> > What isn't working?
> >
> > --
> > Daryl S
> >
> >
> > "Jim Bunton" wrote:
> >
> >> Dear Daryl & Paul,
> >> Thanks for responding to my posting
> >> I'm still having the same issue!
> >> see code below - I must say I'm just mystified!
> >> Note I'm using Microsoft DAO 3.5 Object Library because I'm more used to
> >> the
> >> syntax and being a bit dinosaur like I guess
> >>
> >> Jim
> >>
> >> ** code **
> >> Public Sub TestTransaction()
> >> Dim cdb As DAO.Database, set1 As DAO.Recordset
> >> Dim myspace As DAO.Workspace
> >>
> >> Set myspace = DBEngine.Workspaces(0)
> >> 'Debug.Print currentdb.Name 'works fine but remmed for security
> >> 'Msjet40.dll is version 4.0.9511.0
> >>
> >> Set cdb = currentdb
> >> Set set1 = cdb.OpenRecordset("Table1", DB_OPEN_DYNASET)
> >> Debug.Print set1.Properties(14).Name, set1.Properties(14)
> >> set1.Edit
> >> set1!Flat1 = 900
> >> set1.Update
> >> myspace.BeginTrans
> >> Debug.Print "StartValue " & set1!Flat1
> >> set1.Edit
> >> set1!Flat1 = 100
> >> set1.Update
> >> myspace.Rollback
> >> Debug.Print "EndValue - after rollback! " & set1!Flat1
> >>
> >> End Sub
> >> ** code end **
> >>
> >> Code output!
> >> Transactions True
> >> StartValue 900
> >> EndValue - after rollback! 900
> >>
> >>
> >> "Paul Shapiro" <paul(a)hideme.broadwayData.com> wrote in message
> >> news:eWdnGXDrKHA.728(a)TK2MSFTNGP04.phx.gbl...
> >> >I haven't written DAO transactions for a while, but I thought the
> >> >.BeginTrans was a method on the Workspace object? I seem to remember
> >> >starting the procedure by getting an explicit reference to a Workspace,
> >> >starting the transaction, and referencing the workspace object for all
> >> >the
> >> >data modifications.
> >> >
> >> > Here's an example from the online help:
> >> > Dim wrkCurrent As DAO.Workspace
> >> > Dim dbsNorthwind As DAO.Database
> >> > Dim rstEmployee As DAO.Recordset
> >> >
> >> > On Error GoTo ErrorHandler
> >> >
> >> > Set wrkCurrent = DBEngine.Workspaces(0)
> >> > Set dbsNorthwind = CurrentDB
> >> > Set rstEmployee = dbsNorthwind.OpenRecordset("Employees")
> >> >
> >> > wrkCurrent.BeginTrans
> >> > Do Until rstEmployee.EOF
> >> > If rstEmployee!Title = "Sales Representative" Then
> >> > rstEmployee.Edit
> >> > rstEmloyee!Title = "Sales Associate"
> >> > rstEmployee.Update
> >> > End If
> >> > rstEmployee.MoveNext
> >> > Loop
> >> >
> >> > If MsgBox("Save all changes?", vbQuestion + vbYesNo) = vbYes Then
> >> > wrkCurrent.CommitTrans
> >> > Else
> >> > wrkCurrent.Rollback
> >> > End If
> >> >
> >> >
> >> > "Daryl S" <DarylS(a)discussions.microsoft.com> wrote in message
> >> > news:584D0B1F-96F9-465E-BF46-4241AF4C926C(a)microsoft.com...
> >> >> Jim -
> >> >>
> >> >> One thought... Does the recordset (testSet) have the Transactions
> >> >> property
> >> >> as True? Not all recordsets can use transaction processing. - if the
> >> >> property is False, then you can't use Transactions on this recordset.
> >> >>
> >> >> --
> >> >> Daryl S
> >> >>
> >> >>
> >> >> "Jim Bunton" wrote:
> >> >>
> >> >>> I'm having a bit of an issue with
> >> >>> BeginTrans - CommitTrans
> >> >>> . . . .
> >> >>> Often used it before mainly in Access 97 ! with no problem
> >> >>> But now - Access 2000 (wndows XP)
> >> >>> using References
> >> >>> Visual Basic For Applications
> >> >>> Access 9.0 Object Library
> >> >>> OLE Automation
> >> >>> Microsift DAO 3.5 Object Library
> >> >>> Microsoft ActiceX Data Objects 2.1Library
> >> >>>
> >> >>> Begin Trans CommitTrans doesn't seem to be working as I expect.
> >> >>> The code below for example updates the table!
> >> >>>
> >> >>> **code start**
> >> >>> BeginTrans
> >> >>> testSet.Edit
> >> >>> testSet!flat1 = 999
> >> >>> testSet.Update
> >> >>> Rollback
> >> >>> ** Code End **
> >> >
> >>
> >>
> >> .
> >>
>
>
> .
>
 | 
Pages: 1
Prev: zasto
Next: jeg vil venligs være fri for at modtage meddeelser fra gruppen