A little history...
I have inherited and support a multi-user MS Access FE/BE system using DAO with linked tables (jet) that that I have migrated to SQL Server 2012. Most of the existing functionality currently works as expected, though updating records produces the dreaded write conflict with the "Save Record" button unavailable. In a simple attempt to circumvent much re-coding and complete system/table redef (at this point) I decided to only update DML system calls to use ADODB/OLEDB with stored procedures, all of which are tested and work as expected. Before this conversion the system relied heavily on VBA/DAO to update the record information in multiple tables. This has resulted in lost/corrupt data due to non-atomic updates (vba calls procA to open DAO Recordset then update tableA, then VBA calls procB to open DAO Recordset again then update TableB - no transactions/batches are being used). Once I converted to ADODB/OLEDB I had to also change HOW the code updated the information so I decided to go with an ADODB connection with parameters to call a stored procedure in SQL (all updates now in one batch) and update all necessary tables atomically.
I have gotten this to work as needed, the records update as expected, but after the update to the table, the forms vba code clears the form then calls for a new record using the "DoCmd.GoToRecord , , acNewRecord" and the write conflict occurs at this point in code (the "Save Record" button is now enabled, which I do want). The stored procedure has already updated everything, and I can only assume that this write conflict is happening because before the "acNewRecord" is added, the underlying recordset is again saved/refreshed/requery before it is cleared to ensure no changes have occurred (I assume this because no one else is even in the system, I am currently testing it one form at a time). I have added two methods to show how the recordset is being retrieved and set (no problems in this code), and the save button code (Problem occurs at line 60). I have compressed/removed some of the save button code for readability where code that does not relate to the issue such as validation checks occurring before the stored procedure call (line 20 in cmdSave_click), or where code is proprietary.
Points to clarify:
1. All bit fields in all underlying tables are set to "Not null default 0"; All tables with bit fields also have a timestamp field to ensure concurrency.
2. I am aware of ADODB/OLEDB end of life support and fully intend to migrate to a more robust front end by the end of next Fiscal year; There are however complications (aren't there always;) that require me to go this route first.
3. Connection string: "Provider=SQLOLEDB;Server=
4. The form is bound to a linked (ODBC) view. Once the form goes into edit mode, the form calls DisplayProvider(lngProvide
rNo As Long) that runs a SQL Server stored procedure that returns one ADODB recordset for updating/editing. From this point on (until the form is closed) it is bound by a recordset, not the record source. When opening this recordset I'm using a CursorType = adOpenDynamic, LockType = adLockBatchOptimistic. (no locks) I have checked that this configuration does allow for new records using rs.Supports(adAddNew), which is true, it does work.
5. Have considered After triggers but the tables are poorly designed and no relationships exist, nor can I create them without a complete redesign at this point. See point 2. (sorry endless loop:P)
6. Have considered refresh/requery then calling the stored procedure but this just brings all the problems together, no transaction/batching available, updating multiple tables in a non-atomic way, then still getting the write conflict when the form tries to go to the new record.
7. Have considered just using DAO but do not want to update existing records with JET due to multi layers Jet must go through first.
My question is this, is it possible using this configuration, i.e update using the stored procedure, to have the form go to a new record in the bound recordset without giving me a write conflict, and if so how might I accomplish this? For instance is there a way to suppress, or go around the update/refresh of current record set before it goes to the new record; I am not wanting to suppress the write conflict when it actually is a true write conflict between multiple users
, only find a way to have the form go to a new record after the stored procedure call without the recordset refreshing and seeing the record has changed due to the stored procedure being called.
Any thoughts, ideas, help is greatly appropriated. I have googled this extensively and gone back to all my reference material but cannot find a simple work around for this. I'm almost out of hair to pull out of my head!
Private Sub DisplayProvider(lngProviderNo As Long)
' Display Provider
10 On Error GoTo DisplayProvider_Err
Dim rs As New ADODB.Recordset
20 Set pCmd = New ADODB.Command
30 Set pCmd.ActiveConnection = pDBConnector.CurrentConnection
'Set Stored Proc information
40 pCmd.CommandText = "sp_GetProvider"
50 pCmd.CommandType = adCmdStoredProc
60 Set pPrm = pCmd.CreateParameter("@ProviderNo", adInteger, adParamInput, , frmProviderNo)
70 pCmd.Parameters.Append pPrm
80 With rs
90 .CursorLocation = adUseClient
100 .CursorType = adOpenDynamic
110 .ActiveConnection = pDBConnector.CurrentConnection
120 .LockType = adLockBatchOptimistic
130 .Open pCmd
140 End With
'Set Forms recordset
150 Set Me.Recordset = rs
' Set Focus
' Final Setup
170 Me.AllowAdditions = False
'Enable Save button
180 Me.cmdSave.Enabled = True
190 On Error Resume Next
210 Set rs = Nothing
220 Set pCmd = Nothing
230 mCallStack.Pop ' Remove Method information from call stack
240 Exit Sub 'leave method
DisplayProvider_Err: 'Generate Error Report for User
250 mErrHandle.ErrorReport errRpt_Log
270 Resume DisplayProvider_Exit
Private Sub cmdSave_Click()
' Save Record
10 On Error GoTo cmdSave_Click_Err
' Check Form Mode
' Data Validation
' Existing Record?
' Write Record - This calls a stored procedure on SQL Server that runs an
'update batch to update the provider and entity table record atomically
20 Call UpdateCurrentRecord '<--THIS WORKS NO WRITE CONFLICT!
' Operator Prompt
' Refresh Search
' Clear Form
40 Me.AllowAdditions = True
50 If Me.NewRecord = False Then
60 DoCmd.GoToRecord , , acNewRec '<--PRODUCES WRITE CONFLICT
70 End If
80 mCallStack.Pop ' Remove Method information from call stack
90 Exit Sub 'leave method
cmdSave_Click_Err: 'Generate Error Report for User
100 mErrHandle.ErrorReport errRpt_Log
120 Resume cmdSave_Click_Exit