asked on

Error 3034..rollback.. Set dbs Problem

I had this piece of code working fine.
Then somebody else worked on this Access DB and when I copied it back the following function gives me ROLLBACK error message. The code DID NOT change. I guess the error message occurs on the SET dbs=CurrentDb line? Any idea? Thx.

When I execute the function
MsgBox "An error occurred."

When I DEBUG:
RunTime Error 3034
You tried to commit or rollback a transaction without first beginning a transaction

CODE: (Function) -- Syntax is okay for the function i excuted it many times before..partial code is below

Set wrkDefault = DBEngine.Workspaces(0)
Set dbs = CurrentDb
On Error GoTo Err_Execute

Set rst = dbs.OpenRecordset("SELECT................

wrkDefault.BeginTrans
SELECT, DELETE…blah blah blah
rkDefault.CommitTrans (dbForceOSFlush)
rst.Close
rst2.Close
Set dbs = Nothing
Set wrkDefault = Nothing

strErrorMsg = -------
MsgBox "An error occurred."
wrkDefault.Rollback

SOLUTION

TextReport

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

walterecook

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jadedata

I'm still looking for the Err_Execute: line label...

iqbalj

ASKER

Walt/Andrew/Jade ......….I get error message on the following SET statement

Set rst = dbs.OpenRecordset("SELECT DISTINCT " _
& "[Reference Table Name], " _
& "[Reference Group Field], [Reference Group Value], [Reference Code Value Field], " _
& "[Reference Code Desc Field], [Reference Code Status Field] FROM tblTableField " _
& "WHERE ([Reference Table Name] Like 'CODE_DESC*' OR [Reference Table Name] = " _
& "'ALLEGATION_CD' OR [Reference Table Name] = 'ASSIGN_CATEGORY'" _
& "OR [Reference Table Name] = 'CATEGORY_TYPE'" _
& "OR [Reference Table Name] = 'JOB_CLASS'" _
& "OR [Reference Table Name] = 'LOCATION' AND [Reference Group Value] <> 'SRVCTGRY';")

But when I run the query manually it runs fine...it always ran fine.

SELECT DISTINCT
[Reference Table Name],
[Reference Group Field],
[Reference Group Value],
[Reference Code Value Field],
[Reference Code Desc Field],
[Reference Code Status Field]
FROM tblTableField
WHERE ([Reference Table Name] Like 'CODE_DESC*' OR [Reference Table Name] =
'ALLEGATION_CD'
OR [Reference Table Name] = 'ASSIGN_CATEGORY'
OR [Reference Table Name] = 'CATEGORY_TYPE'
OR [Reference Table Name] = 'JOB_CLASS'
OR [Reference Table Name] = 'LOCATION' AND [Reference Group Value] <> 'SRVCTGRY');

jadedata

Suggestion: Prebuild those sql strings before you try shoveling them into a recordset. It allows you to test the sql or modify it quickly.
You can more easily see flaws in the sql
You will not be hassled for large sqls by running out of line continues.

You were missing a space between an OR and a quote.

ssql = "SELECT DISTINCT " _
ssql = ssql & "[Reference Table Name], [Reference Group Field], [Reference Group Value], [Reference Code Value Field], "
ssql = ssql & "[Reference Code Desc Field], [Reference Code Status Field]
ssql = ssql & vbcrlf
ssql = ssql & "FROM tblTableField " & vbcrlf
ssql = ssql & "WHERE (
ssql = ssql & "[Reference Table Name] Like 'CODE_DESC*' OR
ssql = ssql & "[Reference Table Name] = 'ALLEGATION_CD' OR
ssql = ssql & "[Reference Table Name] = 'ASSIGN_CATEGORY' OR"
ssql = ssql & "[Reference Table Name] = 'CATEGORY_TYPE' OR "
ssql = ssql & "[Reference Table Name] = 'JOB_CLASS' OR "
ssql = ssql & "[Reference Table Name] = 'LOCATION'
ssql = ssql & ") AND (
ssql = ssql & "[Reference Group Value] <> 'SRVCTGRY'")
ssql = ssql & ";"

Set rst = dbs.OpenRecordset(ssql,dbOpenDynasaur)

walterecook

It appears that your &'s are in the wrong place:
Set rst = dbs.OpenRecordset("SELECT DISTINCT " _
& "[Reference Table Name], " _
& "[Reference Group Field], [Reference Group Value], [Reference Code Value Field], " _

should be:
Set rst = dbs.OpenRecordset("SELECT DISTINCT " & _
"[Reference Table Name], " & _
"[Reference Group Field], [Reference Group Value], [Reference Code Value Field], " & _

etc

In other words the & should come before the _

Try that one out
Walt

walterecook

(never mind. I'm going to go crawl in a hole now)

jadedata

and now mine is missing double quotes here and there. I hate writing code here....

ASKER CERTIFIED SOLUTION

jadedata

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

iqbalj

ASKER

jadedata,

It seems like working...how to declare dbOpenDynasaur and what it is for...thx

jadedata

that part was kind of a joke..... you really should use the dataset type of your choosing
(but I do actually have dbOpenDynasaur declared as a public var equal to dbOpenDynaset...)

jadedata

thank for the question iqbalj!