Ö
Ömer Ayzan
Dear friends,
I encountered such a problem and do not know how to correct it:
I am using XP Professional, Office 2003 (SP2) and SQL2000
I'd like to use a transaction on one to many form. Specifically I have
Bank(main form) and Branches (Subform)
These are designed as bound forms to TB_BANK & TB_BRANCH tables
respectively. However in order to use a transaction I open another
connection and with this connection
I start a transaction (Code as follows) at the open event of the main form.
When user chooses to save whatever he has done with the form I commit
otherwise rollback.
Up until this morning this approach was working perfectly. While debugging
on the run Access hanged couple of times. Even though
I recovered thru 'Compact & Repair Project' my ability to rollback has gone.
Assuming that sth has happened to db I run SQL Querry analyzer and tried
updating tables under transaction and no problem. I have other adp's using
the very same approach they also work fine.
To eliminate the problem I created a new adp and copied only the above forms
& code behind it and still the same thing. For any one who could propose sth
the code is as follows:
Private mlngOpMode As openMode
Private mcnnMain As New ADODB.Connection
Private mrstBank As New ADODB.Recordset
Private mrstBranch As New ADODB.Recordset
Private Sub Form_Open(Cancel As Integer)
Dim strSqlMain As String
Dim strSQLSub As String
Dim strFilter As String
Dim lngCallerForm As Long
Dim strWhere As String
Dim varOparg As Variant
Dim strNewData As String
' Format of strOpenArg:
' openMode;NewData;CallerField;CallerForm;Filter
Me.ServerFilter = ""
varOparg = Split(Me.OpenArgs, ";")
Me.Caption = CStr(varOparg(0)) ' Caption
mlngOpMode = CLng(varOparg(1)) ' Open Mode
strNewData = CStr(varOparg(2)) ' New Data
' Caller Field
' Caller Form
strFilter = CStr(varOparg(5)) ' Filter
With mcnnMain
.ConnectionString = CurrentProject.Connection
.Open
.BeginTrans
End With
' MainForm Recordsource
strSqlMain = "SELECT * FROM AYZ_TB_BANK WHERE " & strFilter
' SubForm Recordsource
strSQLSub = "SELECT * FROM AYZ_TB_BRANCH WHERE " & strFilter
mrstBank.Open strSqlMain, mcnnMain, adOpenKeyset, adLockOptimistic
mrstBranch.Open strSQLSub, mcnnMain, adOpenKeyset, adLockOptimistic
Set Me.Recordset = mrstBank
Set Form_AYZ_SUBFRM_BRANCH.Recordset = mrstBranch
Private Sub cmdCancel_Click()
Dim strMsg As String, userResponse As VbMsgBoxResult
Dim strWhere As String
Dim strSQL As String
On Error GoTo Err_cmdCancel_Click
If Me.cmdSave.Enabled Then
strMsg = "Yaptýðýnýz deðiþiklikler iptal edilecektir."
userResponse = DisplayMessage(strMsg, vbExclamation + vbOKCancel)
If userResponse = vbOK Then
mcnnMain.RollbackTrans
Else
GoTo Exit_cmdCancel_Click
End If
End If
DoCmd.Close acForm, Me.Name
Exit_cmdCancel_Click:
Exit Sub
Err_cmdCancel_Click:
DisplayMessage Err.Number & ": " & Err.Description, vbCritical +
vbOKOnly, "HATA!"
Resume Exit_cmdCancel_Click
End Sub
Private Sub cmdSave_Click()
Dim strMsg As String, lngBankID As Long, strWhere As String
On Error GoTo Err_cmdSave_Click
If IsNull(Me.txtBankCode) Then
strMsg = "'Banka Kodu' boþ olamaz."
DisplayMessage strMsg, vbOKOnly + vbCritical, "UYARI!"
Me.txtBankCode.SetFocus
GoTo Exit_cmdSave_Click
End If
mcnnMain.CommitTrans
DoCmd.Close acForm, Me.Name
Exit_cmdSave_Click:
Exit Sub
Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click
End Sub
Private Sub Form_Close()
Form_AYZ_FRM_BANKBR.txtFindCriteria = Me.txtBankID
' A little bit of house keeping
Set mrstBank = Nothing
Set mrstBranch = Nothing
Set mcnnMain = Nothing
End Sub
Private Sub Form_Dirty(Cancel As Integer)
cmdSave.Enabled = True
End Sub
I encountered such a problem and do not know how to correct it:
I am using XP Professional, Office 2003 (SP2) and SQL2000
I'd like to use a transaction on one to many form. Specifically I have
Bank(main form) and Branches (Subform)
These are designed as bound forms to TB_BANK & TB_BRANCH tables
respectively. However in order to use a transaction I open another
connection and with this connection
I start a transaction (Code as follows) at the open event of the main form.
When user chooses to save whatever he has done with the form I commit
otherwise rollback.
Up until this morning this approach was working perfectly. While debugging
on the run Access hanged couple of times. Even though
I recovered thru 'Compact & Repair Project' my ability to rollback has gone.
Assuming that sth has happened to db I run SQL Querry analyzer and tried
updating tables under transaction and no problem. I have other adp's using
the very same approach they also work fine.
To eliminate the problem I created a new adp and copied only the above forms
& code behind it and still the same thing. For any one who could propose sth
the code is as follows:
Private mlngOpMode As openMode
Private mcnnMain As New ADODB.Connection
Private mrstBank As New ADODB.Recordset
Private mrstBranch As New ADODB.Recordset
Private Sub Form_Open(Cancel As Integer)
Dim strSqlMain As String
Dim strSQLSub As String
Dim strFilter As String
Dim lngCallerForm As Long
Dim strWhere As String
Dim varOparg As Variant
Dim strNewData As String
' Format of strOpenArg:
' openMode;NewData;CallerField;CallerForm;Filter
Me.ServerFilter = ""
varOparg = Split(Me.OpenArgs, ";")
Me.Caption = CStr(varOparg(0)) ' Caption
mlngOpMode = CLng(varOparg(1)) ' Open Mode
strNewData = CStr(varOparg(2)) ' New Data
' Caller Field
' Caller Form
strFilter = CStr(varOparg(5)) ' Filter
With mcnnMain
.ConnectionString = CurrentProject.Connection
.Open
.BeginTrans
End With
' MainForm Recordsource
strSqlMain = "SELECT * FROM AYZ_TB_BANK WHERE " & strFilter
' SubForm Recordsource
strSQLSub = "SELECT * FROM AYZ_TB_BRANCH WHERE " & strFilter
mrstBank.Open strSqlMain, mcnnMain, adOpenKeyset, adLockOptimistic
mrstBranch.Open strSQLSub, mcnnMain, adOpenKeyset, adLockOptimistic
Set Me.Recordset = mrstBank
Set Form_AYZ_SUBFRM_BRANCH.Recordset = mrstBranch
Private Sub cmdCancel_Click()
Dim strMsg As String, userResponse As VbMsgBoxResult
Dim strWhere As String
Dim strSQL As String
On Error GoTo Err_cmdCancel_Click
If Me.cmdSave.Enabled Then
strMsg = "Yaptýðýnýz deðiþiklikler iptal edilecektir."
userResponse = DisplayMessage(strMsg, vbExclamation + vbOKCancel)
If userResponse = vbOK Then
mcnnMain.RollbackTrans
Else
GoTo Exit_cmdCancel_Click
End If
End If
DoCmd.Close acForm, Me.Name
Exit_cmdCancel_Click:
Exit Sub
Err_cmdCancel_Click:
DisplayMessage Err.Number & ": " & Err.Description, vbCritical +
vbOKOnly, "HATA!"
Resume Exit_cmdCancel_Click
End Sub
Private Sub cmdSave_Click()
Dim strMsg As String, lngBankID As Long, strWhere As String
On Error GoTo Err_cmdSave_Click
If IsNull(Me.txtBankCode) Then
strMsg = "'Banka Kodu' boþ olamaz."
DisplayMessage strMsg, vbOKOnly + vbCritical, "UYARI!"
Me.txtBankCode.SetFocus
GoTo Exit_cmdSave_Click
End If
mcnnMain.CommitTrans
DoCmd.Close acForm, Me.Name
Exit_cmdSave_Click:
Exit Sub
Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click
End Sub
Private Sub Form_Close()
Form_AYZ_FRM_BANKBR.txtFindCriteria = Me.txtBankID
' A little bit of house keeping
Set mrstBank = Nothing
Set mrstBranch = Nothing
Set mcnnMain = Nothing
End Sub
Private Sub Form_Dirty(Cancel As Integer)
cmdSave.Enabled = True
End Sub