Prev: Accdw file and Sharepoint 2010
Next: Technique used to rotate text labels when creating a PDF file from Access
From: Roger on 7 Dec 2009 16:08
simplied code
Dim wrk As DAO.Workspace
Dim rs As DAO.Recordset
Dim db As DAO.Database

Set wrk = DBEngine.Workspaces(0)
Set db = wrk(0)
wrk.beginTrans
strsql = "some valid query"
set rs = currentdb.openRecordset(strsql, dbOpenDynaset,
dbSeeChanges)
wrk.commitTrans

error - You tried to commit or roll back a transaction without first
using BeginTrans.

but the commit works fine, if I use
set rs = currentdb.openRecordset(strsql)

but I need the 3 parms, due to how the table is defined in sql server
is there a workaround ? should I be using a different value for the
2nd parm of openrecordset ?
From: paii, Ron on 7 Dec 2009 17:31

"Roger" <lesperancer(a)natpro.com> wrote in message
news:f163be47-af64-462c-a4ac-ccf9b07d92a0(a)x5g2000prf.googlegroups.com...
> simplied code
> Dim wrk As DAO.Workspace
> Dim rs As DAO.Recordset
> Dim db As DAO.Database
>
> Set wrk = DBEngine.Workspaces(0)
> Set db = wrk(0)
> wrk.beginTrans
> strsql = "some valid query"
> set rs = currentdb.openRecordset(strsql, dbOpenDynaset,
> dbSeeChanges)
> wrk.commitTrans
>
> error - You tried to commit or roll back a transaction without first
> using BeginTrans.
>
> but the commit works fine, if I use
> set rs = currentdb.openRecordset(strsql)
>
> but I need the 3 parms, due to how the table is defined in sql server
> is there a workaround ? should I be using a different value for the
> 2nd parm of openrecordset ?

You are opening a recordset outside of the transaction by using currentdb

try

Set wrk = DBEngine.Workspaces(0)
Set db = wrk .Databases(0)

Set rs = db.OpenRecordset(strsql, dbOpenDynaset, dbSeeChanges)

wrk .BeginTrans

' Modify information in rs

wrk .CommitTrans

'Close and set to nothing, rs, db ws


From: Roger on 8 Dec 2009 11:32
On Dec 7, 3:31 pm, "paii, Ron" <n...(a)no.com> wrote:
> "Roger" <lesperan...(a)natpro.com> wrote in message
>
> news:f163be47-af64-462c-a4ac-ccf9b07d92a0(a)x5g2000prf.googlegroups.com...
>
>
>
>
>
> > simplied code
> >     Dim wrk As DAO.Workspace
> >     Dim rs As DAO.Recordset
> >     Dim db As DAO.Database
>
> >     Set wrk = DBEngine.Workspaces(0)
> >     Set db = wrk(0)
> >     wrk.beginTrans
> >     strsql = "some valid query"
> >     set rs = currentdb.openRecordset(strsql, dbOpenDynaset,
> > dbSeeChanges)
> >     wrk.commitTrans
>
> > error - You tried to commit or roll back a transaction without first
> > using BeginTrans.
>
> > but the commit works fine, if I use
> >     set rs = currentdb.openRecordset(strsql)
>
> > but I need the 3 parms, due to how the table is defined in sql server
> > is there a workaround ? should I be using a different value for the
> > 2nd parm of openrecordset ?
>
> You are opening a recordset outside of the transaction by using currentdb
>
> try
>
>     Set wrk = DBEngine.Workspaces(0)
>     Set db = wrk .Databases(0)
>
>     Set rs = db.OpenRecordset(strsql, dbOpenDynaset, dbSeeChanges)
>
>     wrk .BeginTrans
>
>     ' Modify information in rs
>
>     wrk .CommitTrans
>
>     'Close and set to nothing, rs, db ws- Hide quoted text -
>
> - Show quoted text -

well, currentdb in my example was a typo...should've been db
in your modified version, if you move the 'set rs...' line after
wrk.beginTrans
it will still create the error

for now, I've got it laid out per your example, but there must be a
solution to using the 'dbSeeChanges' parms within a transaction ?
From: paii, Ron on 8 Dec 2009 15:49

"Roger" <lesperancer(a)natpro.com> wrote in message
news:ef1aef80-42f2-41af-8428-7379f733fcae(a)a39g2000pre.googlegroups.com...
On Dec 7, 3:31 pm, "paii, Ron" <n...(a)no.com> wrote:
> "Roger" <lesperan...(a)natpro.com> wrote in message
>
> news:f163be47-af64-462c-a4ac-ccf9b07d92a0(a)x5g2000prf.googlegroups.com...
>
>
>
>
>
> > simplied code
> > Dim wrk As DAO.Workspace
> > Dim rs As DAO.Recordset
> > Dim db As DAO.Database
>
> > Set wrk = DBEngine.Workspaces(0)
> > Set db = wrk(0)
> > wrk.beginTrans
> > strsql = "some valid query"
> > set rs = currentdb.openRecordset(strsql, dbOpenDynaset,
> > dbSeeChanges)
> > wrk.commitTrans
>
> > error - You tried to commit or roll back a transaction without first
> > using BeginTrans.
>
> > but the commit works fine, if I use
> > set rs = currentdb.openRecordset(strsql)
>
> > but I need the 3 parms, due to how the table is defined in sql server
> > is there a workaround ? should I be using a different value for the
> > 2nd parm of openrecordset ?
>
> You are opening a recordset outside of the transaction by using currentdb
>
> try
>
> Set wrk = DBEngine.Workspaces(0)
> Set db = wrk .Databases(0)
>
> Set rs = db.OpenRecordset(strsql, dbOpenDynaset, dbSeeChanges)
>
> wrk .BeginTrans
>
> ' Modify information in rs
>
> wrk .CommitTrans
>
> 'Close and set to nothing, rs, db ws- Hide quoted text -
>
> - Show quoted text -

>well, currentdb in my example was a typo...should've been db
>in your modified version, if you move the 'set rs...' line after
>wrk.beginTrans
>it will still create the error
>
>for now, I've got it laid out per your example, but there must be a
>solution to using the 'dbSeeChanges' parms within a transaction ?

From Access help on OpenRecordset

"dbSeeChanges: Generates a run-time error if one user is changing data that
another user is editing (Microsoft Jet dynaset-type Recordset only). This is
useful in applications where multiple users have simultaneous read/write
access to the same data."

dbSeeChanges may not work on a SQL Server table.




 | 
Pages: 1
Prev: Accdw file and Sharepoint 2010
Next: Technique used to rotate text labels when creating a PDF file from Access