MS Access ADO Write Conflict after SQL Server Stored Procedure updates record

Hi Everyone,

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=<Currentserver>;Database=<CurrentDatabase>;Trusted_Connection=yes;"

4. The form is bound to a linked (ODBC) view. Once the form goes into edit mode, the form calls DisplayProvider(lngProviderNo 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!

Thanks Everyone!

Private Sub DisplayProvider(lngProviderNo As Long)
            ' Display Provider

10    On Error GoTo DisplayProvider_Err

      Dim rs As New ADODB.Recordset

      'Initial Setup
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
160   Me.txtLName.SetFocus

      ' Final Setup
170   Me.AllowAdditions = False

      'Enable Save button
180   Me.cmdSave.Enabled = True

190     On Error Resume Next
200     rs.Close
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
260     Err.Clear
270     Resume DisplayProvider_Exit
End Sub
' ---------------------------------------------------------

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
30    Me.cmdExit.SetFocus

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
110   Err.Clear
120   Resume cmdSave_Click_Exit
End Sub

Open in new window

Matt StephensAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Write conflicts happen because Access is trying to update the same records as you're doing with code. The general rule with Access is either YOU do all the data work (i.e. an entire unbound application) or Access does all the data work (i.e. a bound application). Hybrids can work, but they tend to be cumbersome and unreliable. If you're using SPs to update, and then allowing Access to save those same records, you'll get a write conflict every time.
Matt StephensAuthor Commented:
Hi Scott, Thank you for the reply.  Yes this is what I'm starting to realize. But If I go the full SP route then how can I go to a new record without the write conflict? Resetting the forms recordset or record source causes the conflict because access tries to refresh the record. This is what I'm trying to get around.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can't use Bound forms if you are going to handle the data yourself. YOu'd have to set the Form's Recordsource to nothing, and set each Control's ControlSource to nothing, and then handle all the data work yourself. To go to a "new" record, you'd just clear all the controls and set the cursor to the right location, and let the user enter data.

This is a LOT of work, and it's essentially just recreating what Access does. IMO, you'd be better off reverting to a bound application and allowing Access to handle the data work. There are many apps that use SQL Server with linked tables that work fine.

Did the behavior you're describing occur AFTER migrating to SQL Server? Or is this something that's always been a problem?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Matt StephensAuthor Commented:
Yeah I agree its lots of work to do a changeover like this and this did start after the migration. The problem (I know) stems from the underlying tables that are being updated are not normalized properly nor do they have relationships as they should, so any typical options I may have when a database is normalized won't work for me here, i.e. triggers, foreign key constraints, etc. This was all enforced in code in the front end (poor practice for sure). This will change when I convert to a better front end, but having to rewrite everything now will push back any attempts to actually use SQL server for at least 6 months to a year...not ideal. This played a big part in my reasoning to even attempt it this way.

The biggest problem with just letting access handle everything is the record anomalies due to non-atomic updates, otherwise I wouldn't have even attempted this. For instance I can get this to work without the write conflict if I do me.refresh before I call the stored procedure. It works...BUT again this becomes a non-atomic update, even IF the stored procedure updates both tables in the process.

As a developer I completely agree with your assessment and would go with a bound form AFTER I drew up the requirements, defined all tables and their relationships, triggers, SPs, business rules, etc... but at this point I can't :(. I think I may just be expecting to have more control over access that I actually can have. Unfortunate really, but I understand why they do this.

Therefore, I think for brevity and to help others who may fall into the same trap as I have, (as much as I revolt at this idea) I will have to use the me.refresh before the SP call. This will ensure I still get the more user friendly write conflict message when two users are updating the same record, but I loose the ability to update the tables atomically through the stored procedure. Trade offs like this stink!

I think I just needed someone else's perspective and expertise. Once this is all updated and released (and all my hair grows back), I will be reviewing ALL system requirements and updating all tables to ensure I can move to a more robust front end like C#.

Thank you very much for helping me to clear this up.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Matt StephensAuthor Commented:
I'm going to mark my last response ID: 42466805 as the best solution, as this really is the only option I have found that works with for my current requirements. Again the hesitation comes from the fact that I cannot update tables atomically under the current system layout and will have to redefine the system to better use the tools SQL Server has to offer. I will also likely move to C# for the front end.  

To anyone who may read this later, always keep in mind that these trade offs can be serious especially in environments where connectivity issues can occur. I would rather have an update rolled back (if you have backup for verification), than to allow a partial write to one or more tables as this can cause anomalies in your tables. These requirements fit my current situation, yours may be more critical and require a partial write over no write at all, but I would try to avoid this as much as possible.
Sometimes adding a timestamp column to the underling SQL table/view may resolve write conflict issues.
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 is nothing wrong with Access as an FE to ODBC databases.  However, you are not using Access correctly.  Access is a RAD tool and as Scott pointed out, Access has its way of doing things and either you go along with the joke or don't use Access.  Hybrids are always flaky as you are experiencing.

Sounds like you don't particularly like Access and don't appreciate the work it will save you if implemented properly.

If you decide to go the full sp route, you have absolutely no reason to be using Access at all.

I would guess that a redesign that uses Access correctly will be as stable and infinitely scalable as you need and will cost a fraction of what a redesign using a different platform will cost.  The scalability limits of Access are related to the number of active developers rather than the number of concurrent users or the row counts of tables.  A properly designed Access app linked to a "real" RDBMS could support as many concurrent users as you have SQL Server CALs as long as they are all on the same LAN or if you are using Citrix.  Access does not lend itself to multiple concurrent developers.  You can do it if you use Source Control but it is a real PITA.  So, if a single developer is sufficient, Access is fine.  If the app requires multiple developers, I would not use Access.

The problem (I know) stems from the underlying tables that are being updated are not normalized properly nor do they have relationships as they should,
So why are you blaming Access?  It isn't Access that is causing your issues.  It is poor design and a lack of understanding of how to use Access.

For instance I can get this to work without the write conflict if I do me.refresh before I call the stored procedure.
Apparently you are not aware that a side effect of Me.Refresh is that Access saves the current record.  THAT is why this avoids the write conflict. You CANNOT use bound forms the way you are using them.

Again the hesitation comes from the fact that I cannot update tables atomically under the current system layout and will have to redefine the system to better use the tools SQL Server has to offer. I will also likely move to C# for the front end.
Why would you blame Access for a poor design.  A bad developer can make just as much of a mess with C#.  Would you blame C#?

I'm going to mark my last response ID: 42466805 as the best solution, as this really is the only option I have found that works with for my current requirements.
Since the Refresh has ALREADY saved the bound data, what is the point of the sp??????
Matt StephensAuthor Commented:

I came asking for legitimate help regarding already present design flaws that I was trying to work around. As I said before I inherited this system. The system needs a redesign, due to some inherent flaws in the table design and I am more familiar with C#; I have no problem with Access. The issue I'm facing currently is that the "Me.refresh" call only refreshes the underlying bound form, yet there are multiple tables that must be updated at the same time. I have code in place to update these other tables but I am trying to avoid using it, because that defeats the purpose of ensuring an atomic update, I'm looking for all or nothing when it comes to updates and inserts. I have used stored procedures before, therefore I was more inclined to pass parameters to SQL server and let it return a result set.
Without having any idea of what the system is supposed to do it is hard to solve this design Issue.  Forms should be bound to queries that have selection criteria to limit the rows returned from the server. The query may include multiple tables and Access will update them as a transaction.  In a 1-many relationship where you have a "batch" that needs to be applied as a set, the easiest solution is to use a set of holding tables where you can add the parent record and all the child records.  When the user asks to "post", you validate the  set of data and open your own transaction to either add the new set of data or if this is an update, delete the old data and insert the new.

It would have been more productive to fix the underlying design flaws and not convert to SQL server until the app was sound.  Converting to SQL Server first only made a bad situation worse.
Matt StephensAuthor Commented:
Due to system constraints and the current system setup I have no alternative except to continue to use the Me.Refresh. I was trying to ensure the all table updates happens atomically but under current  system architecture this cant happen without a major redef of the project. Multiple tables are 1st NF or worse so any true solution will involve
That is a mistake.  Me.Refresh, refreshes the form's RecordSource.  It retrieves any value changes and identifies deleted records   As a SIDE EFFECT, it first saves the current record.  If all you want to do is to save the current record, save it explicitly.  That avoids any unexpected side effects.  Access is always best when you use it correctly.

Use either the explicit Access method

If Me.Dirty Then
     DoCmd.RunCommand acCmdSaveRecord
End If


Use the trick

If Me.Dirty Then
    Me.Dirty = False
End If
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.