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
iqbaljAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TextReportCommented:
Change your error handler to report the actual error, I normally use the following

On Error Goto xxx_Error

normal code

xxx_Exit:
    Exit Sub/Function

xxx_Error:
    Select Case Err
        Case Else
               MsgBox "An Unexpected error has ocoured in xxx" & vbcrlf & vbcrlf & err & vbTab & err.description
               Resume xxx_Exit
    End Select

In this way you will see the actual error

Cheers, Andrew
0
walterecookCommented:
Well done Andrew and quite true.
I'd also suggest finding just where the error is.  That'd make it easier to fix, too.
2 ways:
- put a break on line 1 then f8 through the code
- comment out OnError lines

That way you find the offending line.

Walt

(PS my money's on a missing reference and a type mismatch/object required error)
0
jadedataMS Access Systems CreatorCommented:
I'm still looking for the Err_Execute: line label...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

iqbaljAuthor Commented:
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');
0
jadedataMS Access Systems CreatorCommented:
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)
0
walterecookCommented:
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

0
walterecookCommented:
(never mind.  I'm going to go crawl in a hole now)
0
jadedataMS Access Systems CreatorCommented:
and now mine is missing double quotes here and there.  I hate writing code here....
0
jadedataMS Access Systems CreatorCommented:
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 & ";"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
iqbaljAuthor Commented:
jadedata,

It seems like working...how to declare dbOpenDynasaur and what it is for...thx
0
jadedataMS Access Systems CreatorCommented:
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...)
0
jadedataMS Access Systems CreatorCommented:
thank for the question iqbalj!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.