Solved

DOCMD.RUNSQL - Example w/TRANSACTION ?

Posted on 2002-03-14
7
2,157 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Report Expression 4 26
MS Access Subform Unique Records Issue 14 36
Specify the table containg the records you want to delete 14 30
Clear Current Value from Combobox 2 25
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA 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 …

756 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