• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2416
  • Last Modified:

DOCMD.RUNSQL - Example w/TRANSACTION ?

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
eeevbeee
Asked:
eeevbeee
  • 4
  • 3
1 Solution
 
dovholukCommented:
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
 
eeevbeeeAuthor Commented:
dovholuk,

i get an error:
"Sub or Function not defined" on the first line, begintrans
0
 
dovholukCommented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
eeevbeeeAuthor Commented:
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
 
dovholukCommented:
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
 
eeevbeeeAuthor Commented:
makes sense, thanks!
0
 
eeevbeeeAuthor Commented:
makes sense, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now