Solved

Error 3034..rollback.. Set dbs Problem

Posted on 2003-11-19
12
1,606 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

729 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