Re: Working Transactions somehow started not to work

Hello Ömer,

you probably will be surprised, or maybe even won't believe, but the code you posted could not work ever; at least, reliably. Unlike MDB, with ADP, begintrans, committrans, and rollback are practically ignored. See for example http://support.microsoft.com/kb/223213 - it's easy to see that the results would be unpredictable.

Also see http://www.eggheadcafe.com/aspnet_answers/AccessadpSQLServer/Jan2006/post25750796.asp - Sylvain's comment. There's workaround, but it's unsupported and also may bring unpredictable results - see my comment in the end of the above page.

From all my experience of working with ADP, the best results are achieved by
allowing Access to manipulate the data its own way, rather than trying to force it to do what you want. For example, it's better to specify recordsource for a form rather than set me.recordset=<pre-created recordset> - i.e. allow Access to build the recordset as it wants, rather than force it to use yours. The path of forcing your way on Access is paved with numerous hours of troubleshooting unpredictable behaviours exactly like what you just encountered.

The best tool for working with transactions and proper locking is VB6. Everything else (Access, and VS.Net) is using client-based cursors.

Vadim Rapp


You wrote in conference microsoft.public.access.adp.sqlserver on Fri, 6 Oct 2006 13:57:18 +0300:

ÖA> Dear friends,

ÖA> I encountered such a problem and do not know how to correct it:

ÖA> I am using XP Professional, Office 2003 (SP2) and SQL2000

ÖA> I'd like to use a transaction on one to many form. Specifically I have
ÖA> Bank(main form) and Branches (Subform)
ÖA> These are designed as bound forms to TB_BANK & TB_BRANCH tables
ÖA> respectively. However in order to use a transaction I open another
ÖA> connection and with this connection
ÖA> I start a transaction (Code as follows) at the open event of the main
ÖA> form. When user chooses to save whatever he has done with the form I
ÖA> commit otherwise rollback.

ÖA> Up until this morning this approach was working perfectly. While
ÖA> debugging on the run Access hanged couple of times. Even though
ÖA> I recovered thru 'Compact & Repair Project' my ability to rollback has
ÖA> gone. Assuming that sth has happened to db I run SQL Querry analyzer
ÖA> and tried updating tables under transaction and no problem. I have
ÖA> other adp's using the very same approach they also work fine.

ÖA> To eliminate the problem I created a new adp and copied only the above
ÖA> forms & code behind it and still the same thing. For any one who could
ÖA> propose sth the code is as follows:

ÖA> Private mlngOpMode As openMode
ÖA> Private mcnnMain As New ADODB.Connection
ÖA> Private mrstBank As New ADODB.Recordset
ÖA> Private mrstBranch As New ADODB.Recordset

ÖA> Private Sub Form_Open(Cancel As Integer)
ÖA> Dim strSqlMain As String
ÖA> Dim strSQLSub As String
ÖA> Dim strFilter As String
ÖA> Dim lngCallerForm As Long
ÖA> Dim strWhere As String

ÖA> Dim varOparg As Variant
ÖA> Dim strNewData As String

ÖA> ' Format of strOpenArg:
ÖA> ' openMode;NewData;CallerField;CallerForm;Filter

ÖA> Me.ServerFilter = ""

ÖA> varOparg = Split(Me.OpenArgs, ";")
ÖA> Me.Caption = CStr(varOparg(0)) ' Caption
ÖA> mlngOpMode = CLng(varOparg(1)) ' Open Mode
ÖA> strNewData = CStr(varOparg(2)) ' New Data
ÖA> ' Caller Field
ÖA> ' Caller Form
ÖA> strFilter = CStr(varOparg(5)) ' Filter

ÖA> With mcnnMain
ÖA> .ConnectionString = CurrentProject.Connection
ÖA> .Open
ÖA> .BeginTrans
ÖA> End With

ÖA> ' MainForm Recordsource
ÖA> strSqlMain = "SELECT * FROM AYZ_TB_BANK WHERE " & strFilter
ÖA> ' SubForm Recordsource
ÖA> strSQLSub = "SELECT * FROM AYZ_TB_BRANCH WHERE " & strFilter

ÖA> mrstBank.Open strSqlMain, mcnnMain, adOpenKeyset, adLockOptimistic
ÖA> mrstBranch.Open strSQLSub, mcnnMain, adOpenKeyset, adLockOptimistic

ÖA> Set Me.Recordset = mrstBank
ÖA> Set Form_AYZ_SUBFRM_BRANCH.Recordset = mrstBranch

ÖA> Private Sub cmdCancel_Click()
ÖA> Dim strMsg As String, userResponse As VbMsgBoxResult
ÖA> Dim strWhere As String
ÖA> Dim strSQL As String

ÖA> On Error GoTo Err_cmdCancel_Click

ÖA> If Me.cmdSave.Enabled Then
ÖA> strMsg = "Yaptýðýnýz deðiþiklikler iptal edilecektir."
ÖA> userResponse = DisplayMessage(strMsg, vbExclamation +
ÖA> vbOKCancel)

ÖA> If userResponse = vbOK Then
ÖA> mcnnMain.RollbackTrans
ÖA> Else
ÖA> GoTo Exit_cmdCancel_Click
ÖA> End If
ÖA> End If
ÖA> DoCmd.Close acForm, Me.Name

ÖA> Exit_cmdCancel_Click:
ÖA> Exit Sub
ÖA> Err_cmdCancel_Click:
ÖA> DisplayMessage Err.Number & ": " & Err.Description, vbCritical +
ÖA> vbOKOnly, "HATA!"
ÖA> Resume Exit_cmdCancel_Click

ÖA> End Sub

ÖA> Private Sub cmdSave_Click()
ÖA> Dim strMsg As String, lngBankID As Long, strWhere As String
ÖA> On Error GoTo Err_cmdSave_Click

ÖA> If IsNull(Me.txtBankCode) Then
ÖA> strMsg = "'Banka Kodu' boþ olamaz."
ÖA> DisplayMessage strMsg, vbOKOnly + vbCritical, "UYARI!"
ÖA> Me.txtBankCode.SetFocus
ÖA> GoTo Exit_cmdSave_Click
ÖA> End If

ÖA> mcnnMain.CommitTrans

ÖA> DoCmd.Close acForm, Me.Name
ÖA> Exit_cmdSave_Click:

ÖA> Exit Sub
ÖA> Err_cmdSave_Click:
ÖA> MsgBox Err.Description
ÖA> Resume Exit_cmdSave_Click
ÖA> End Sub

ÖA> Private Sub Form_Close()
ÖA> Form_AYZ_FRM_BANKBR.txtFindCriteria = Me.txtBankID
ÖA> ' A little bit of house keeping
ÖA> Set mrstBank = Nothing
ÖA> Set mrstBranch = Nothing
ÖA> Set mcnnMain = Nothing
ÖA> End Sub

ÖA> Private Sub Form_Dirty(Cancel As Integer)
ÖA> cmdSave.Enabled = True
ÖA> End Sub


Vadim
----------------------------------------
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com

.

Relevant Pages
Loading