Solved

DOCMD.RUNSQL - Example w/TRANSACTION ?

Posted on 2002-03-14
7
2,179 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

710 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