Solved

DOCMD.RUNSQL - Example w/TRANSACTION ?

Posted on 2002-03-14
7
2,209 Views
Last Modified: 2007-11-27
Would anyone have an Example of how to use DOCMD.RUNSQL with Transactions?

I want to run two Updates on a table, and if there was any error in the proccess to Roll it Back.

0
Comment
Question by:eeevbeee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 8

Expert Comment

by:dovholuk
ID: 6864739
just use begintrans and commit/rollback trans.

such as:

begintrans
docmd.runsql "here is my sql"

if (something to test) = true then
     committrans
else
     rollback
end if

that's all there is to it...

since you're using the default workspace, this should work unless you are using a db backend that doesn't support transactions...

enjoy

dovholuk
0
 

Author Comment

by:eeevbeee
ID: 6866079
dovholuk,

i get an error:
"Sub or Function not defined" on the first line, begintrans
0
 
LVL 8

Accepted Solution

by:
dovholuk earned 75 total points
ID: 6867805
ahh. it might be the whole DAO/ADO issue. MS decided in its infinite wisdom, that MS Access 2k and beyond will use ADO to connect to databases. that in itself isn't a bad idea. however, they neglected to change over all their native access table, queries, forms etc. which all still use DAO.

it's rediculously confusing at times, getting ADO vs. DAO correct. for instance, i can't tell you for certain if DoCmd.RunSQL is using ADO or DAO... i suspect ADO since you get an error at begin trans.

here's some things to check.

1.) open up your module, and click on the Tools->References... menu option. check to see if DAO 3.5 (or 3.51, 3.6 etc) is checked ON. if not, check it on and try the code again.

2.) if that didn't work, try using this:
DBEngine.Workspaces(0).BeginTrans the BeginTrans i gave you "assumes" that the default workspace is being used... but i have a hunch that the workspace is a DAO workspace, and not an ADO workspace not a DAO workspace... (argh)

i don't know for sure. what i DO know is that BeginTrans is a method of one of these two objects.
      a.) a DAO.Workspace
            or
      b.) a ADODB.Connection

personally, i'd tell you to use a different method than docmd.runsql. the method I like best is currentdb.execute. it does the EXACT same thing, but you won't need to setwarnings true / false everytime you use it (look it up or ask for more info).  in your case though, i would either create an adodb.connection and .execute / .open your sql statements wrapped in an ADODB.Connection.BeginTrans / ADODB.Connection.Rollback or i would use DAO (by creating a reference as noted above) and then you can just use the BeginTrans / Rollback i mentioned in an earlier post...

is this making any sense??? ;)

let me know how you make out...

dovholuk
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:eeevbeee
ID: 6873576
dovholuk,

I have been playing with the information/examples you have given me, and so far this is what I have managed to come up with:

Nothing I tried (begintrans/rollback or DBEngine.Workspaces(0).BeginTrans) seems to wrok with the Docmd.RunQuery. DAO 3.6 is in my Refrences...

DBEngine.Workspaces(0).BeginTrans / CurrentDB.Execute str / RollBack|CommitTrans works great.

It seems though that DBEngine.Workspaces(0).BeginTrans/Rollback will Rollback everything (all tables). So I'm wondering if there is there a away to:

1. have diferent workspaces?
2. do Transactions on a Table?
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6874552
you're right. a transaction affects ALL tables because each transaction wraps all the sql into one, big transaction. all the sql you execute inside the transaction gets rolled back. so if you modify two tables. each table will be rolled back. follow? i don't think that you can nest transactions... but you could try.

generally speaking, a transaction isn't used for conditional rollingback. it's an all or nothing shot. however, you can do it with different workspaces and then begin/rollback trans on those. all you need to do is:

a.) declare a workspace (Dim ws as DAO.workspace)
b.) create the workspace (using dbengine.createworkspace)
c.) declare a connection (dim con as dao.connection)
d.) create the connection (using ws from step b, use: set con = ws.createconnection)
e.) execute your sql from whatever connection you are trying to not rollback etc.

it's really complex. and you need a separate workspace object for each conditional rollback you wanna do... genearally speaking though, you don't need to do this. i've spent many years with transactions, and i haven't had an issue like this...

let me know if this doesn't make sense or if it doesn't cure your transaction issue...

dovholuk
0
 

Author Comment

by:eeevbeee
ID: 6875898
makes sense, thanks!
0
 

Author Comment

by:eeevbeee
ID: 6875899
makes sense, thanks!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question