BeginTrans/Commit/Rollback within Access itself

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

BeginTrans/Commit/Rollback within Access itself

Author

Topic

vbFace
Starting Member

1 Post

Posted - 2004-02-11 : 10:51:44

I am trying to set up a transaction within Access itself using VBA. So far I have tried the following:


' This line doesn't error, but will error on CurrentProject.Connection.Rollback / .CommitTrans
' saying no transaction is started
CurrentProject.Connection.BeginTrans

' These lines do not error but do not do anything!
DBEngine.BeginTrans
DBEngine.Rollback
DBEngine.CommitTrans
' and
DBEngine.Workspaces(0).BeginTrans
DBEngine.Workspaces(0).Rollback
DBEngine.Workspaces(0).CommitTrans

I am using DoCmd.RunSql "<the sql>" to run the INSERT and DELETE queries.

So, does anyone know how to do this the right way?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-11 : 11:11:30

Try sending the begin tran and commit/rollback tran to the server in he same way as you do a query.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-11 : 11:49:36

you cannot use Docmd. that is a different "connection" to the database -- the actual application connection. it has nothing to do with your workspace.

you need to your SQL using the "execute" method from a db connection from within the workspace. DoCmd() does things through the application layer.

i.e.,

Workspaces(0).Databases(0).Execute "some sql"

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources