X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> How To Get Past Write Conflict, Access 2016    
post May 22 2020, 09:42 AM

Posts: 24
Joined: 23-April 08

I have a database with MS Access 2016 on the Front, and SQL Server on the back. (In the process of moving to the SQL Back)

One form I have is set as continuous forms to show a list of employees. By clicking on an employee, it loads a 2nd form with Payroll Transactions- to make changes for pay versus time-off etc. [The form sets a tempvar which is then used upon loading the 2nd form]

After making any changes, the 2nd form will not commit the changes- indicating a write conflict and that the record has been changed by another user, etc.

1st- There are no other users in the db, so I would assume the 1st form is submitting a record lock against the table or record.

I have already attempted options that close the first form just after opening the 2nd- still doesn't work. I've attempted to set the form to me.dirty=false upon opening to remove potential locks- still doesn't work. I've also attempted to set a separate UPDATE query per changed field to run upon close, but then get an object error.

Unfortunately, without some major redesign considerations, I am a bit stumped how I can commit my data here. The design loads some calculated data upon opening the 2nd form and links to information from two tables, so it is not as simple as just changing the new open form to link to a specific table independently. Just as a point of interest- this method has worked when everything was in Access alone without issue. It is simply moving it to SQL that the error has began.

Thoughts, considerations, suggestions?
Go to the top of the page
post May 22 2020, 09:51 AM

UA Admin
Posts: 37,510
Joined: 20-June 02
From: Newcastle, WA

You can get some valuable insights from the presentation in this download. It's been around for a while, but it is still accurate and useful.

You'll want to pay special attention to the use of "timestamp" or "RowVersion" fields.

My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post May 22 2020, 10:01 AM

UtterAccess VIP
Posts: 3,102
Joined: 12-April 07
From: Edmonton, Alberta Canada

A common problem.

So, first the nuts and bolts part.

Each table has a PK.
Each table has what is called a “row version”. Now for some strange reason, in SQL server land, this is called a timestamp column. Keep in mind that this column has ZERO to do with time, or even date time.

And in fact we see an “increased” push in Microsoft’s documentation to call that type of column a “row version”. And in fact we seeing some SQL table design tools starting to use that term (row version – but the actual type in the column is in fact “timestamp”). It is kind of a bad name, since as noted, it has nothing to do with actual date or times in that table.

So, tables need a row version column. If you used the Access to SQL server migration wizard, then it offers (I believe the default) to create + add this column to each table.

Last thing:
If you have any true/false columns in that table? You need to ensure that bit columns have a default value set (0). This is required since access will often spit out write conflict errors if you don’t have a default value for bit columns (specialty, access chokes if such bit columns are null).

Ok, assuming you have the above all nice and correct?

The simple solution is that when you click on your edit button to launch that 2nd form?

ALWAYS make sure that you “save” and ensure that the current forms data (the current row) is written out to the table, and the record in the form is NOT dirty (dirty means that pending writes exist).

So, right before that one line of code to launch + edit the 2nd form?

Change this:

Docmd.OpenForm "frmEditDetails",,,"id = " & me!id


If me.Dirty = true then me.Dirty = false
Docmd.OpenForm "frmEditDetails",,,"id = " & me!id

So, this means that any pending writes are flushed out. Now if the next form in the sequence happens to edit or modify the same record? Well, you not get a write conflict because the pending updates for that record have been flushed out.

You can now with ease and peace of mind launch that 2nd form. It can even edit + modify the same record we just were “on” in the previous form, and you not receive any write conflict errors.

And, as a general rule?

When I am on a form, and I launch another form to further edit data (even if different records)? I tend to add the above one line “dirty” check, and ensure that the current forms record is all nice saved away. This has the added bonus that if access crashes, or freezes up, that record will have been saved. So, you don’t have a case in which say the user may have traversed several forms deep in a typical sequence of forms in applications. So, each step of the way, you force a save of the current form before leaving or interacting with the next form.

Not only does this ensure that data is saved, even if things were to halt or freeze up, but this also eliminates the dreaded “this record been changed” by someone else should any form further down the sequence happen to change or edit a record that was touched in the previous forms. And this includes update by your intention, or perhaps even by accident. Either way, just ensure that things are nice and tucked away before you jump onto the next form in the process.

This also allows you to thus say have a form editing a record, but you might want to click on a button to view/edit more details for the same record. A simple adding of the above one-line dirty check will thus allow this process to work, and eliminate write conflict errors.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
post May 22 2020, 11:55 AM

UtterAccess VIP
Posts: 2,268
Joined: 21-February 07
From: Copenhagen

You may also take advantage of the method for Automatic handling of concurrent updates using DAO in VBA
as described in my project VBA.ConcurrencyUpdates.

Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
post May 22 2020, 01:58 PM

Posts: 4,704
Joined: 11-November 02

QUOTE (zoomerny)
I would assume the 1st form is submitting a record lock against the table or record.

A single Table isn't involved in both Forms, is it?

Linq ;0)>

BTW...The Monkey is laughing at me...not at any other poster!

The problem with making anything foolproof...is that fools are so darn ingenious!

All posts/responses based on Access 2003/2007
Go to the top of the page
post May 22 2020, 04:14 PM

UtterAccess VIP
Posts: 3,102
Joined: 12-April 07
From: Edmonton, Alberta Canada

the design loads some calculated data upon opening the 2nd form and links to information from two tables,

Ok, so we are talking about a form bound to a query, but the query involves two tables? Right?

Well, this works with JET/ACE. However, if you using a linked table to SQL server?

As a general rule you can ONLY update one of the two tables. In other words, the query involves two tables, and thus controls on the form can (I assume) be bound to both tables. In this case, you have to limit your updates to ONE of the two tables. In most cases, I would assume the "base" table, but I recall that you can update the child table (the 2nd table - most likly a left join).

However, if you attempt to update BOTH tables, then you going to get a write conflict. Access + JET/ACE can actually update both tables. But with SQL server, Access will get confused if both tables become dirty. (worse yet, there is only one timestamp column it can use).

Move one set of controls belonging to child table to a child form.

If only one or two controls? then perhaps on the on-enter event, you check for form dirty and save.

So, in your case, both master and child tables in the ONE query can NOT BOTH become dirty. In 100% jet/ace applications this can work, but with SQL server, it cannot. So, if you really need up-date ability, you might have to use a sub-form for the fields from the "joined in table". That then means you need to change the forms datasource back to a single table.

If it just 2-3 extra text boxes? Then in the 2-3 controls that belong to the child table? You need a dirty check as per above. And unfortunately, in the after update of the 3 boxes, you also need the dirty check + save. The simple issue is you can't allow both tables to become dirty - it will confuse access and you get the write conflict error no matter what you do.

Best is to separate out fields from one table to a sub-form. If only 2-3 text boxes, then you could code your way around this issue. (try above suggest (dirty = false check on "enter", and on the way out of the 2-3 controls, (after update), you again do the dirty check.)

If you have more then 2-3 fields that edit from the 2nd table? Then its time to move them out to a sub form.

So I missed this little detail - it would seem that you have one query based on two tables, and that query is bound to the form. As noted, ACE can do this, but with SQL server, only one of the two tables can become dirty at a given time, and if you attempt to edit columns that belong to the other table, the dirty of the first table must not exist. And the reverse is also true. (you have to save 2nd table before you go back to editing first table).

I think with 2-3 fields you could try on-enter + after update events. but more then 2-3, I think you are starting to write too much code, and moving all fields from 2nd table into a sub-form would fix this issue.

Go to the top of the page
post May 25 2020, 09:49 AM

Posts: 24
Joined: 23-April 08

Hi Albert,

Thank you! That is the most detailed explanation of this process I have ever seen. I do have a few quick clarification questions I am hoping you can answer.

1- I exported the original Table from Access through ODBC into SQL Server. I don't believe that is the method indicated by the wizard? Therefore, my original PK did not maintain the identity of a PK on the other side and was only set to NOT allow null. I have since corrected the ID Field to contain the status as the PK. Does that now qualify as the row version you indicated, or would I need to additionally change some other criteria or setting?

2- Should the Me.Dirty be set as part of the Form Open statement on Form 1 that I am viewing, or should it be included as part of the Form Load statement on Form 2 that I am opening. If I follow your initial statement, I believe it should be in Form 1, that I am "leaving", in that it would unlock entries prior to a new form taking control of the data?

3- In SQL Server, I see an entry for "Lock Escalation" under the Table Designer / Table Properties section. It indicates my current lock escalation set to TABLE (other choices are auto or disable). Do I need or want to change that setting in any capacity, or would making the adjustments you listed be sufficient?

Go to the top of the page
post May 25 2020, 10:41 AM

Posts: 24
Joined: 23-April 08

It would appear that I have a different gremlin causing my headache than anticipated.

For troubleshooting, I went directly to Form 2- changed the Query to include a single Table only.

I proceeded to save and close everything, then open Form 2 directly, with no call from anywhere else.

My attempt to save any change in data still brings up the refusal to save any data with the only option being to Drop Changes. I have attempted this with the form load changing the status to Dirty/False, and with no on load code at all. :-/

Thank you so far for all of the assistance everyone has provided, as it has been helpful and did let me find and correct some other issues. But so far, I am still stalled on this matter and may just consider a form and process redesign from scratch, as clearly there is a larger issue looming here.

I also apologize to a few others who posted input, as I didn't initially see some of the other posts prior to responding back to the forum. Pays to update the page prior to replying.
Go to the top of the page
post May 25 2020, 04:27 PM

UtterAccess VIP
Posts: 3,102
Joined: 12-April 07
From: Edmonton, Alberta Canada

Great stuff.

So, our goose may not be cooked as of yet.

A few things:

I have since corrected the ID Field to contain the status as the PK. Does that now qualify as the row version you indicated,

No, you need the PK, and you REALLY need to ensure that not only all tables have a PK, but you ALSO need to ensure that after you link, that Access in the table design mode (ignore the read only warning) shows the PK as a PK.

Now for a table linked to SQL server? Well, if the table has a PK, then it should show as a PK in the client side table design mode.

However, you might be using a SQL server view. In that case you are prompted to select the PK, since Access (or SQL server) unfortunately does NOT have a defined PK – so you have to set/choose the PK. Now of course because a view can involve more than one table, then which column is the PK, since such queries can have more than one PK included? (So, on linking to a view, you are prompted to pick/set the PK – since access can’t figure this out or know).

But, let’s leave the multi-table query issue aside, since you noted that you testing with ONE table, and still getting errors.

This brings us back to the row version issue.

The existence of “row version” is a 100% separate issue from that of PK. They are not related. They have nothing to do with each other. But you STILL need one!!!

However, as noted:
If you have bit fields?
Make sure the default of such fields server side is set to 0 (they can’t be null else you get random “this record been changed by someone else”.

If you have floating point value (single, double) that are part of this table?

Once again, try and set defaults to zero for these columns (SQL server side).

But, in BOTH of the above cases, and as a general rule?

Yes, you want to add a NEW column to the table of data type “row version”. And as I stated, that row version “type” is a column of timestamp type. (The world’s worst name, and the cause of 20+ years of confusing). “Timestamp” is NOT related to time and has zero to do with “time” It is called “row version”, but has the horrible name of “timestamp”.

You add this column – that’s all – you as developer don’t have to do ANY thing else. It simply a type of column that needs to exist in the table.

After you add this column, I would re-link the table(s) in question. (Linked table manger – refresh is fine).

should the Me.Dirty be set as part of the Form Open statement on Form 1 that I am viewing, or should it be included as part of the Form Load statement on Form 2 that I am opening.

Ok, lets assume the query or bound data source is ONLY one table, not the duel table issue.

In this case? The dirty check will occur in the calling form. So, RIGHT before you launch another form (that could edit the same data), then you simply ensure writes are 100% all done and nice tucked away.

At that point, the form you launch is free to do whatever it wants – including editing the same record from the calling form.

So, the code will be:

If me.Dirty = True then me.Dirty = false ‘ write data
Docmd.OpenForm "frmEditDetails",,,"id = " & me!id

Now above is a common and typical example of say launching a detail form from a say a sub form of many rows, and a “edit” button to launch/see/view/edit/expand some detail form that you launch to the same record for further details.

So, the rule and suggest is:

If you are on a form, and launching more forms, and those forms might edit or touch the data the current form is touching?

Then simply ensure that you write out all changes to the table before moving on in your code and to the NEXT form.
Never leave the current form with pending table writes.

This nice suggest not only applies to launching that “drill down” form, or additional forms, but also if you about to call some VBA/SQL update routines on the current forms data. So, always before such operations ensure that the current forms data is written out. If the record is “dirty” then of course any other code attempting to edit the forms data will cause that write conflict error.

So above should clear up your two issues:

Yes, you need/want to add a row version. If you add this, then a good chance your write conflict will go away.

Next issue:
The duel table issue.
As I stated?
Well, if you using a “join” and your query has two tables and you are editing that table?

As noted, with SQL server, you can try and edit such data in a SINGLE form, but as a general rule, you can ONLY dirty one of the two tables at a GIVEN point in time.

So my 2nd narrative was is that if you ONLY have 2-3 text boxes that will “cause” an edit of the 2nd table?

You can’t edit/use/update data in those 2-3 text boxes UNTIL you eliminate the dirty record of the OTHER table. So, that’s where the additional suggesting comes into play here. So the suggesting was ONLY for duel table edit with one query. We have to be 100% clear, and limit 100% the context to the given scenario and use case you are talking about.

In this duel table case?
Only ONE table can be dirty at a given time.

So, it only follows that if the current record is dirty, and we are about to use/edit/change a text box bound to the other table that is not yet dirty?

Well, then in the on-enter command of those 2-3 text boxes, we could check for dirty, and simply write out the record.


  ‘ on control enter event.
   If me.Dirty = true then me.Dirty = false

So, if you “enter” into the 2-3 contarols? We force a record save, and thus we ARE LIMITING the update of the query to ONE table. As I stated, 2-3 text boxes should work.

But if you have lots more, then we writing code that saves the form data by the “mere” act of just tabbing or clicking into a text box. Now, I can’t think that just tabbing around and moving around text boxes on a form, and for that simple “act” of jumping around we will be firing off a record saves when doing this.

But then again, for 2-3 controls, you could/can get away doing this. For more controls? Well, then the coding will get too messy, and you be firing off a lot of save record commands. So, I suggesting that you move all those fields to a sub form that then allows you to limit updates to a single table on that form. This is just a strategy suggest to keep you out of the insane asylum.

So, right now?
Address and fix the write conflict – especially that you now limiting the form to one table. The fix for this is the addition of the row version (all tables should have this).

And do the table write before launching the next form. These two steps should fix the write conflict error for you.

Once you have that working, then you might attempt the duel table editing with one query. (And are you using a “view” for this, or attempting this with client side query?

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
post May 26 2020, 10:50 AM

Posts: 24
Joined: 23-April 08

Albert you are a God-send!

Two part correction here. The issue was definitely the Null values in the Table.

I changed all Null numericals to default with 0.

Separately- I missed relinking the tables to have Access acknowlege that my table now had a proper Primary Key assigned as well.

Everything appears to be operating correctly now!
Go to the top of the page
post May 26 2020, 01:31 PM

UtterAccess VIP
Posts: 3,102
Joined: 12-April 07
From: Edmonton, Alberta Canada


Do keep in mind that adding/having a timestamp (rowversion) column is not a "must" have, but I seen it fix miss-behaving sub forms. The same goes for forms that have single, or double numbers, and the numbers are filled from other code (not VBA).

So the rowversion issue is still optional, but I still recommend it. If some sub form starts to go wonkey on you? Try adding rowversions to those tables.

Access will use rowversion to determine if a record is dirty. Even .net code does also. So, it still often can be helpful. If access does not detect a rowversion, then it reverts to a column by column compare of the record to determine "dirty" status. So, rowversion makes this whole process "easier" and more reliable then tables that don't have this "change" trigger column. Even the SSMAA tools do "offer" to add time stamp to all tables during a migration. So, it not a "must" do, but it still helps access to figure out when a record is dirty.

Ironically, the one possible case where this "row version" be a bad idea? Your query with two tables! (and this would have to be a SQL view anyway - but as noted, that's a different testing scenario.

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    12th July 2020 - 05:30 PM