Re: Working Transactions somehow started not to work

Personnally, I've always been against the use of transactions for long term
editing and every time I see someone implementing a cancel function for a
form by using a Rollback encased in a transaction make me grind my teeth.
The problems with scaling and performance degradation that this can lead to
are easy to guess.

However, even if I'm against their use in this context, this doesn't explain
why his particular piece of code is not working properly. Instead of using
one of the connection created by Access, he creates and use its own
connection (the line « ConnectionString = CurrentProject.Connection »
doesn't begin wit the SET command, so instead of reusing the
CurrentProject.Connection object, he's creating a new object using the
connection string of the CurrentProject.Connection object) so theoritically,
this should work.

However, by using this connection string, he's also using the special OLEDB
driver created for ADP and that is standing between the bound forms and
SQL-Server. Even with a separate connection string, the effect of using an
open transaction on this driver is totally undocumented by Microsoft and in
my opinion, totally unpredictable. It's clear in my opinion that even with
a separate connection, this driver will try to open and close its own
transactions while updating the database for saving the edited data on the
bound forms.

Maybe he could try following the @@transaction count on the server (inside a
stored procedure) to solve this problem but in my opinion, opening a direct
connection to the SQL-Server without using the connection string of the
CurrentProject.Connection object (to make sure that only the OLEDB provider
for SQL-Server is used, not that strange beast called the Microsoft OLEDB
provider for Access) and using unbound forms should be a much more safer
approach (albeit also more complicated but that's life) than the previously
posted code.

In the past, Microsoft has warned many times that trying to use transactions
with bound forms would have a high probability of problems and failures; I
don't see any reason to doubt their parole. If you use them and don't have
any problem, good for you but if you are unlucky, then you are on your own.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Vadim Rapp" <vr@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:%23$7ajhA7GHA.1560@xxxxxxxxxxxxxxxxxxxxxxx
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

  • Project Error
    ... Private Declare Sub Sleep Lib "Kernel32" ... Dim strDataSrc As String ...
    (microsoft.public.vb.bugs)
  • Re: Is there a way to prevent a RichTextBox from scrolling?
    ... Private _isRegex As Boolean ... Public Sub New(ByVal thispattern As String, ... Dim entry As tDict ...
    (microsoft.public.dotnet.framework.windowsforms.controls)
  • Excel Listing tool using VB
    ... Sub ListFiles2() ... Dim directories() As String, CurrentDirectory As String ... Dim dirtopaste, dirok ...
    (microsoft.public.vb.general.discussion)
  • Form Error
    ... SMSDS_CallerID As String ... Private Declare Sub Sleep Lib "kernel32" ... Dim ComString As String ... Dim AppPath As String, FreeFileNo% ...
    (microsoft.public.vb.bugs)
  • Re: Encrypt/hide Password
    ... Public Sub New(ByVal strCryptoName As String) ... ' instantiated crypto class. ... Dim fsKey As New FileStream(strSaveToPath, FileMode.OpenOrCreate, _ ...
    (microsoft.public.scripting.wsh)
Loading