Solved

DOCMD.RUNSQL - Example w/TRANSACTION ?

Posted on 2002-03-14
7
2,136 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

828 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