Class module

[j_gold]

Hi,

I have a class module. When I call it from my form module, I get the error
3034 "You tried to commit or rollback a transaction without first beginning a
transaction".

What am I missing

Thanks,

J Gold

Option Compare Database
Option Explicit

Private wrkSpace As DAO.Workspace
Private dbUnit As DAO.Database

Private Sub Class_Initialize()

Set wrkSpace = DBEngine(0)
Set dbUnit = CurrentDb

End Sub

Public Function add_unit(newUnitID As Integer, newUnitType As String,
newUnitName As String, _
newBuildingName As String, newBuildingAddress As String,
newRoomNumber As String) As Boolean

Dim sqlQuery As String
Dim intDAOErrNo As Integer, intCtr As Integer


On Error GoTo ErrorHandler

sqlQuery = "INSERT INTO Unit (unitID, unitName, buildingName ,
buildingAddress, roomNumber, unitType) VALUES (" _
+ newUnitID + ",'" + newUnitName + " ','" + newBuildingName +
"','" + newBuildingAddress _
+ "','" + newRoomNumber + "','" + newUnitType + "')"

wrkSpace.BeginTrans


dbUnit.Execute sqlQuery

wrkSpace.CommitTrans dbForceOSFlush

Done:
Exit Function

ErrorHandler:

wrkSpace.Rollback

Resume ErrorHandler_Exit

ErrorHandler_Exit:

wrkSpace.Close

Exit Function

End Function

Private Sub Class_Terminate()

Set wrkSpace = Nothing
Set dbUnit = Nothing
End Sub

 

[Banana]

I'd bet that this is because you had a unrelated error that occurred
prior to beginning a transaction and the control went to error handler
which then tried to rollback the transaction. You may want to debug your
VBA to see what actually caused the error.

 

[vanderghast]

Technically, the error handling you specified should be placed AFTER the
beginTrans, since any error occuring before that would not have a started
transaction to rollback. Why is there code which close the workspace? have
you removed some code? are you sure your variable wrkSpace is NOT Nothing
when you call (again) add_unit?



I would also use a database defined from my workspace, rather than CurrentDb
(to be more explicit about what I do)


----------------------------------
Public Sub testTrans()
Dim wks As Workspace: Set wks = DBEngine(0)
Dim db As Database: Set db = wks(0)

Debug.Assert Not (wks Is Nothing)

wks.BeginTrans
On Error GoTo RollbackMe

' the following is an error, invalid syntax ...
db.Execute "INSERT this INTO that ", dbFailOnError
wks.CommitTrans
Exit Sub

RollbackMe:
wks.Rollback
Exit Sub

End Sub

 

[Dirk Goldgar]

Hi,

I have a class module. When I call it from my form module, I get the error
3034 "You tried to commit or rollback a transaction without first
beginning a
transaction".

What am I missing

Thanks,

J Gold

Option Compare Database
Option Explicit

Private wrkSpace As DAO.Workspace
Private dbUnit As DAO.Database

Private Sub Class_Initialize()

Set wrkSpace = DBEngine(0)
Set dbUnit = CurrentDb

End Sub

Public Function add_unit(newUnitID As Integer, newUnitType As String,
newUnitName As String, _
newBuildingName As String, newBuildingAddress As
String,
newRoomNumber As String) As Boolean

Dim sqlQuery As String
Dim intDAOErrNo As Integer, intCtr As Integer


On Error GoTo ErrorHandler

sqlQuery = "INSERT INTO Unit (unitID, unitName, buildingName ,
buildingAddress, roomNumber, unitType) VALUES (" _
+ newUnitID + ",'" + newUnitName + " ','" + newBuildingName +
"','" + newBuildingAddress _
+ "','" + newRoomNumber + "','" + newUnitType + "')"

wrkSpace.BeginTrans


dbUnit.Execute sqlQuery

wrkSpace.CommitTrans dbForceOSFlush

Done:
Exit Function

ErrorHandler:

wrkSpace.Rollback

Resume ErrorHandler_Exit

ErrorHandler_Exit:

wrkSpace.Close

Exit Function

End Function

Private Sub Class_Terminate()

Set wrkSpace = Nothing
Set dbUnit = Nothing
End Sub

You're getting a type mismatch error before you ever begin your transaction,
because this line:

sqlQuery = "INSERT INTO Unit (unitID, unitName, buildingName ,
buildingAddress, roomNumber, unitType) VALUES (" _
+ newUnitID + ",'" + newUnitName + " ','" + newBuildingName +
"','" + newBuildingAddress _
+ "','" + newRoomNumber + "','" + newUnitType + "')"

.... uses the + operator in an attempt to concatenate an integer and a
string. But with an integer as one of the operands, the + operator will be
construed as signifying addition, not concatenation. It's safer to use the
& operator for concatenation:

sqlQuery = _
"INSERT INTO Unit (" & _
"unitID, unitName, buildingName, " &
"buildingAddress, roomNumber, unitType" & _
") VALUES (" & _
newUnitID & ",'" & newUnitName & " ','" & _
newBuildingName & "','" & newBuildingAddress _
& "','" & newRoomNumber & "','" & newUnitType & "')"

As a side note, do not close the default workspace. I don't think Jet will
let you, anyway, but you shouldn't even try to execute this statement:

 

[j_gold]

Thanks Banana, sorry for the late reply, this message got lost.

 

[j_gold]

Thanks vanderghast, sorry for the delay in responding, I lost these replies
in my inbox.

I'm still new to VBA and I obviously missed an important step - I was
placing all my On Errors before not after.

Cheers,

J Gold

 

[j_gold]

Thanks Dirk, my apologies for not responding sooner, the replies got lost in
my inbox.

Appreciate the tip on the & vs + operators. Will save me a few headaches I'm
sure.

Cheers,

J Gold