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
Solved

Error 3034..rollback.. Set dbs Problem

Posted on 2003-11-19
12
1,596 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

856 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