Re: Working Transactions somehow started not to work
- From: "aaron.kempf@xxxxxxxxx" <aaron.kempf@xxxxxxxxx>
- Date: 11 Oct 2006 11:37:29 -0700
Malcolm;
where did you hear that from?
-Aaron
Malcolm Cook wrote:
A possible reason for "why begintrans does not work as expected." = adp opens multiple connections including (sometimes?) separate
connections for subforms
??
"Vadim Rapp" <vr@xxxxxxxxxxxxxxxxxxxx> wrote in message news:O$M$5yC7GHA.2364@xxxxxxxxxxxxxxxxxxxxxxx
Hello Sylvain,
I think, in a nutshell, Access can be assumed incompatible with
transactions. For the simple reason: transactions and locking involve
server-based cursors. While Access (as well as vs.net) works with
client-based cursors. Client-based cursors create their own
pseudo-transaction; it may be good or bad, but it's totally different from
the server-based cursors, hence from locking, hence from transactions.
I totally agree, however, with the thought that "true" locking is hardly
compatible with interactive work. A user who is trying to edit a record
currently locked by someone else, will certainly assume that his application
or even computer has locked up. To be useful, the locking mechanism should
include some statement that would attempt to place the lock, and would be
asynchronous, so the user would be able to cancel the wait for the resource.
When I found that mentioned workaround with creating the true transaction by
using connection.execute "begin transaction", I actually opened support
ticket with Microsoft and asked if it's supported scenario (having pretty
good idea what would be the answer), and why begintrans does not work as
expected. The response was that, indeed, at some point there were plans to
make begintrans etc. fully working in ADP, but then it was stopped in the
middle, so the whole thing remains half-baked and undocumented.
In all new programming environments, Microsoft is actually pushing
client-based cursors, and there are results indeed: it looks like now most
developers don't even consider true locking. Recently, I tested this
hypothesis by asking moderately difficult locking-related question on
experts-exchange - nobody even tried to answer along the correct lines
(http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_22012456.html)
- though there are _very_ skilled sql programmers there. I won't be too
surprised if in some future version of sql server, user-controlled locking
will be abandoned as such.
Vadim Rapp
You wrote in conference microsoft.public.access.adp.sqlserver on Tue, 10
Oct 2006 00:18:26 -0400:
SL> Personnally, I've always been against the use of transactions for long
SL> term editing and every time I see someone implementing a cancel
SL> function for a form by using a Rollback encased in a transaction make
SL> me grind my teeth. The problems with scaling and performance
SL> degradation that this can lead to are easy to guess.
SL> However, even if I'm against their use in this context, this doesn't
SL> explain why his particular piece of code is not working properly.
SL> Instead of using one of the connection created by Access, he creates
SL> and use its own connection (the line « ConnectionString =
SL> CurrentProject.Connection » doesn't begin wit the SET command, so
SL> instead of reusing the CurrentProject.Connection object, he's creating
SL> a new object using the connection string of the
SL> CurrentProject.Connection object) so theoritically, this should work.
SL> However, by using this connection string, he's also using the special
SL> OLEDB driver created for ADP and that is standing between the bound
SL> forms and SQL-Server. Even with a separate connection string, the
SL> effect of using an open transaction on this driver is totally
SL> undocumented by Microsoft and in my opinion, totally unpredictable.
SL> It's clear in my opinion that even with a separate connection, this
SL> driver will try to open and close its own transactions while updating
SL> the database for saving the edited data on the bound forms.
SL> Maybe he could try following the @@transaction count on the server
SL> (inside a stored procedure) to solve this problem but in my opinion,
SL> opening a direct connection to the SQL-Server without using the
SL> connection string of the CurrentProject.Connection object (to make sure
SL> that only the OLEDB provider for SQL-Server is used, not that strange
SL> beast called the Microsoft OLEDB provider for Access) and using unbound
SL> forms should be a much more safer approach (albeit also more
SL> complicated but that's life) than the previously posted code.
SL> In the past, Microsoft has warned many times that trying to use
SL> transactions with bound forms would have a high probability of problems
SL> and failures; I don't see any reason to doubt their parole. If you use
SL> them and don't have any problem, good for you but if you are unlucky,
SL> then you are on your own.
SL> --
SL> Sylvain Lafontaine, ing.
SL> MVP - Technologies Virtual-PC
SL> E-mail: http://cerbermail.com/?QugbLEWINF
SL> "Vadim Rapp" <vr@xxxxxxxxxxxxxxxxxxxx> wrote in message
SL> news:%23$7ajhA7GHA.1560@xxxxxxxxxxxxxxxxxxxxxxx
SL>> Hello Ömer,
SL>>
SL>> you probably will be surprised, or maybe even won't believe, but the
SL>> code you posted could not work ever; at least, reliably. Unlike MDB,
SL>> with ADP, begintrans, committrans, and rollback are practically
SL>> ignored. See for example http://support.microsoft.com/kb/223213 - it's
easy to see that the results would
SL>> be unpredictable.
SL>>
SL>> Also see
SL>>
http://www.eggheadcafe.com/aspnet_answers/AccessadpSQLServer/Jan2006/post25750796.asp
-
SL>> Sylvain's comment. There's workaround, but it's unsupported and also
SL>> may bring unpredictable results - see my comment in the end of the
SL>> above page.
SL>>
SL>> From all my experience of working with ADP, the best results are
SL>> achieved by allowing Access to manipulate the data its own way, rather
SL>> than trying to force it to do what you want. For example, it's better
SL>> to specify recordsource for a form rather than set
SL>> me.recordset=<pre-created
recordset>>> - i.e. allow Access to build the recordset as it wants, rather
SL>> than force it to use yours. The path of forcing your way on Access is
SL>> paved with numerous hours of troubleshooting unpredictable behaviours
SL>> exactly like what you just encountered.
SL>>
SL>> The best tool for working with transactions and proper locking is VB6.
SL>> Everything else (Access, and VS.Net) is using client-based cursors.
SL>>
SL>> Vadim Rapp
SL>>
SL>> You wrote in conference microsoft.public.access.adp.sqlserver on Fri,
SL>> 6 Oct 2006 13:57:18 +0300:
SL>>
ÖA>>> Dear friends,
SL>>
ÖA>>> I encountered such a problem and do not know how to correct it:
SL>>
ÖA>>> I am using XP Professional, Office 2003 (SP2) and SQL2000
SL>>
ÖA>>> I'd like to use a transaction on one to many form. Specifically I
ÖA>>> have Bank(main form) and Branches (Subform) These are designed as
ÖA>>> bound forms to TB_BANK & TB_BRANCH tables respectively. However in
ÖA>>> order to use a transaction I open another connection and with this
ÖA>>> connection I start a transaction (Code as follows) at the open event
ÖA>>> of the main form. When user chooses to save whatever he has done with
ÖA>>> the form I commit otherwise rollback.
SL>>
Ö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
ÖA>>> has gone. Assuming that sth has happened to db I run SQL Querry
ÖA>>> analyzer and tried updating tables under transaction and no problem.
ÖA>>> I have other adp's using the very same approach they also work fine.
SL>>
ÖA>>> To eliminate the problem I created a new adp and copied only the
ÖA>>> above forms & code behind it and still the same thing. For any one
ÖA>>> who could propose sth the code is as follows:
SL>>
Ö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
SL>>
Ö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
SL>>
ÖA>>> Dim varOparg As Variant
ÖA>>> Dim strNewData As String
SL>>
ÖA>>> ' Format of strOpenArg:
ÖA>>> ' openMode;NewData;CallerField;CallerForm;Filter
SL>>
ÖA>>> Me.ServerFilter = ""
SL>>
Ö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
SL>>
ÖA>>> With mcnnMain
ÖA>>> .ConnectionString = CurrentProject.Connection
ÖA>>> .Open
ÖA>>> .BeginTrans
ÖA>>> End With
SL>>
ÖA>>> ' MainForm Recordsource
ÖA>>> strSqlMain = "SELECT * FROM AYZ_TB_BANK WHERE " & strFilter
ÖA>>> ' SubForm Recordsource
ÖA>>> strSQLSub = "SELECT * FROM AYZ_TB_BRANCH WHERE " & strFilter
SL>>
ÖA>>> mrstBank.Open strSqlMain, mcnnMain, adOpenKeyset,
ÖA>>> adLockOptimistic mrstBranch.Open strSQLSub, mcnnMain,
ÖA>>> adOpenKeyset,
SL>> adLockOptimistic
SL>>
ÖA>>> Set Me.Recordset = mrstBank
ÖA>>> Set Form_AYZ_SUBFRM_BRANCH.Recordset = mrstBranch
SL>>
ÖA>>> Private Sub cmdCancel_Click()
ÖA>>> Dim strMsg As String, userResponse As VbMsgBoxResult
ÖA>>> Dim strWhere As String
ÖA>>> Dim strSQL As String
SL>>
ÖA>>> On Error GoTo Err_cmdCancel_Click
SL>>
ÖA>>> If Me.cmdSave.Enabled Then
ÖA>>> strMsg = "Yaptýðýnýz deðiþiklikler iptal edilecektir."
ÖA>>> userResponse = DisplayMessage(strMsg, vbExclamation +
ÖA>>> vbOKCancel)
SL>>
Ö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
SL>>
Ö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
SL>>
ÖA>>> End Sub
SL>>
ÖA>>> Private Sub cmdSave_Click()
ÖA>>> Dim strMsg As String, lngBankID As Long, strWhere As String
ÖA>>> On Error GoTo Err_cmdSave_Click
SL>>
Ö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
SL>>
ÖA>>> mcnnMain.CommitTrans
SL>>
ÖA>>> DoCmd.Close acForm, Me.Name
ÖA>>> Exit_cmdSave_Click:
SL>>
ÖA>>> Exit Sub
ÖA>>> Err_cmdSave_Click:
ÖA>>> MsgBox Err.Description
ÖA>>> Resume Exit_cmdSave_Click
ÖA>>> End Sub
SL>>
Ö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
SL>>
ÖA>>> Private Sub Form_Dirty(Cancel As Integer)
ÖA>>> cmdSave.Enabled = True
ÖA>>> End Sub
SL>>
SL>> Vadim
SL>> ----------------------------------------
SL>> Vadim Rapp Consulting
SL>> SQL, Access, VB Solutions
SL>> 847-685-9073
SL>> www.vadimrapp.com
Vadim
----------------------------------------
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com
.
- References:
- Working Transactions somehow started not to work
- From: Ömer Ayzan
- Re: Working Transactions somehow started not to work
- From: Vadim Rapp
- Re: Working Transactions somehow started not to work
- From: Sylvain Lafontaine
- Re: Working Transactions somehow started not to work
- From: Vadim Rapp
- Re: Working Transactions somehow started not to work
- From: Malcolm Cook
- Working Transactions somehow started not to work
- Prev by Date: Re: Working Transactions somehow started not to work
- Next by Date: Re: access adp, sql server and recordsets
- Previous by thread: Re: Working Transactions somehow started not to work
- Next by thread: Re: Working Transactions somehow started not to work
- Index(es):
Relevant Pages
|
