Solved

Error 3034..rollback.. Set dbs Problem

Posted on 2003-11-19
12
1,590 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
  • 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

815 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now