P
paolo
Hi,
I'm just getting to know transactions. In the following routine, I've
got a bunch of action queries with parameters which I am trying to run.
I understand that my code is not appropriate. If not, how to I go
about passing parameters to the action queries?
Thanks in advance,
Paolo
Private Sub Add_Element()
On Error GoTo ErrHandler
Dim ws As DAO.Workspace 'Current workspace (for transaction)
Dim db As DAO.Database 'Inside the transaction
Dim bInTrans As Boolean 'Flag that transaction is active
Dim strSql As String 'SQL string
'Initialize database object inside a transaction.
Set ws = DBEngine(0)
ws.BeginTrans
bInTrans = True
Set db = ws(0)
'Identify parent element by creating temporary table
strSql = "SELECT tblElements.[ElementID],
tblElements.[ElementName], " & _
"tblElements.
I'm just getting to know transactions. In the following routine, I've
got a bunch of action queries with parameters which I am trying to run.
I understand that my code is not appropriate. If not, how to I go
about passing parameters to the action queries?
Thanks in advance,
Paolo
Private Sub Add_Element()
On Error GoTo ErrHandler
Dim ws As DAO.Workspace 'Current workspace (for transaction)
Dim db As DAO.Database 'Inside the transaction
Dim bInTrans As Boolean 'Flag that transaction is active
Dim strSql As String 'SQL string
'Initialize database object inside a transaction.
Set ws = DBEngine(0)
ws.BeginTrans
bInTrans = True
Set db = ws(0)
'Identify parent element by creating temporary table
strSql = "SELECT tblElements.[ElementID],
tblElements.[ElementName], " & _
"tblElements.
, tblElements.
,
tblElements.[WorkPack] " & _
"INTO tblIsolateElementAdd " & _
"FROM tblElements " & _
"WHERE tblElements.[ElementID] = '" & ParentNode & "'"
db.Execute strSql, dbFailOnError
'Update left counter permanent table
strSql = "UPDATE tblElements, tblIsolateElementAdd " & _
"SET tblElements.
tblElements.[WorkPack] " & _
"INTO tblIsolateElementAdd " & _
"FROM tblElements " & _
"WHERE tblElements.[ElementID] = '" & ParentNode & "'"
db.Execute strSql, dbFailOnError
'Update left counter permanent table
strSql = "UPDATE tblElements, tblIsolateElementAdd " & _
"SET tblElements.
= tblElements.
+2 " & _
"WHERE tblElements.
"WHERE tblElements.
>tblIsolateElementAdd.
"
db.Execute strSql, dbFailOnError
'Update right counter permanent table
strSql = "UPDATE tblElements, tblIsolateElementAdd " & _
"SET tblElements.
db.Execute strSql, dbFailOnError
'Update right counter permanent table
strSql = "UPDATE tblElements, tblIsolateElementAdd " & _
"SET tblElements.
= tblElements.
+2 " & _
"WHERE tblElements.
"WHERE tblElements.
>= tblIsolateElementAdd.
"
db.Execute strSql, dbFailOnError
'Update temporary table for child element
strSql = "UPDATE tblIsolateElementAdd " & _
"SET tblIsolateElementAdd.[ElementID] ='" & Me![Add_Text1] &
"', " & _
"tblIsolateElementAdd.[ElementName] ='" & Me![Add_Text2] & "',
" & _
"tblIsolateElementAdd.
db.Execute strSql, dbFailOnError
'Update temporary table for child element
strSql = "UPDATE tblIsolateElementAdd " & _
"SET tblIsolateElementAdd.[ElementID] ='" & Me![Add_Text1] &
"', " & _
"tblIsolateElementAdd.[ElementName] ='" & Me![Add_Text2] & "',
" & _
"tblIsolateElementAdd.
= tblIsolateElementAdd.
, "
& _
"tblIsolateElementAdd.
& _
"tblIsolateElementAdd.
= tblIsolateElementAdd.
+
1, " & _
"tblIsolateElementAdd.[WorkPack] =" & Me![Add_Check]
db.Execute strSql, dbFailOnError
'Append permanent table with temporary table for new child elements
strSql = "INSERT INTO tblElements " & _
"([ElementID], [ElementName],
1, " & _
"tblIsolateElementAdd.[WorkPack] =" & Me![Add_Check]
db.Execute strSql, dbFailOnError
'Append permanent table with temporary table for new child elements
strSql = "INSERT INTO tblElements " & _
"([ElementID], [ElementName],
,
, [WorkPack]) " &
_
"SELECT tblIsolateElementAdd.[ElementID],
tblIsolateElementAdd.[ElementName], " & _
"tblIsolateElementAdd.
_
"SELECT tblIsolateElementAdd.[ElementID],
tblIsolateElementAdd.[ElementName], " & _
"tblIsolateElementAdd.
, tblIsolateElementAdd.
, " &
_
"tblIsolateElementAdd.[WorkPack] " & _
"FROM tblIsolateElementAdd"
db.Execute strSql, dbFailOnError
'Drop temporary table
strSql = "DROP TABLE tblIsolateElementAdd"
db.Execute strSql, dbFailOnError
'Commit transaction
ws.CommitTrans
bInTrans = False
Exit_AddElement:
'Clean up
On Error Resume Next
Set db = Nothing
If bInTrans Then 'Rollback if the transaction is active.
ws.Rollback
End If
Set ws = Nothing
Exit Sub
ErrHandler:
MsgBox "Error in AddElement()." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
Resume Exit_AddElement
End Sub
_
"tblIsolateElementAdd.[WorkPack] " & _
"FROM tblIsolateElementAdd"
db.Execute strSql, dbFailOnError
'Drop temporary table
strSql = "DROP TABLE tblIsolateElementAdd"
db.Execute strSql, dbFailOnError
'Commit transaction
ws.CommitTrans
bInTrans = False
Exit_AddElement:
'Clean up
On Error Resume Next
Set db = Nothing
If bInTrans Then 'Rollback if the transaction is active.
ws.Rollback
End If
Set ws = Nothing
Exit Sub
ErrHandler:
MsgBox "Error in AddElement()." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
Resume Exit_AddElement
End Sub