Solved

Error 3034..rollback.. Set dbs Problem

Posted on 2003-11-19
12
1,602 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:iqbalj
[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
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 40 total points
ID: 9782372
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
 
LVL 17

Assisted Solution

by:walterecook
walterecook earned 40 total points
ID: 9782400
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
 
LVL 32

Expert Comment

by:jadedata
ID: 9782422
I'm still looking for the Err_Execute: line label...
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:iqbalj
ID: 9782775
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
 
LVL 32

Expert Comment

by:jadedata
ID: 9782856
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
 
LVL 17

Expert Comment

by:walterecook
ID: 9782862
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
 
LVL 17

Expert Comment

by:walterecook
ID: 9782867
(never mind.  I'm going to go crawl in a hole now)
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9782870
and now mine is missing double quotes here and there.  I hate writing code here....
0
 
LVL 32

Accepted Solution

by:
jadedata earned 120 total points
ID: 9782911
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
 

Author Comment

by:iqbalj
ID: 9782993
jadedata,

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

Expert Comment

by:jadedata
ID: 9783013
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
 
LVL 32

Expert Comment

by:jadedata
ID: 9783101
thank for the question iqbalj!
0

Featured Post

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!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

739 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