UtterAccess.com
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
> Can't Edit Record After Save, Any Versions    
 
   
williamlove
post May 26 2020, 02:25 PM
Post#1



Posts: 149
Joined: 8-February 06



I have a table in an SQL Server 2019 database. It is a linked table in an Access .accdb application which has a form whose Record Source is that linked table. This client/front end app runs on several machines that run Access 2010 and 2019.

When I enter a record in the form and save it things go fine. But when I try to return to that record later and edit it, I get an error popup. The title of the popup box is "Write Conflict" and the message in the popup is: "This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made."

There is another paragraph...the attachment shows the entire popup.

No one is editing the record at the time this happens. And none of the other stations have that form open.

This is ruining my app and making my end user unhappy with me so any help solving it would be greatly appreciated.


Attached File(s)
Attached File  Write_Conflict.JPG ( 46.33K )Number of downloads: 2
 
Go to the top of the page
 
June7
post May 26 2020, 02:33 PM
Post#2



Posts: 1,517
Joined: 25-January 16
From: The Great Land


This kind of conflict can occur if start edit a record and then some code tries to also edit that record or perhaps even vice versa. I had to deal with this once. Took me days to figure out what code was causing this and to modify.

So look at your code that retrieves record.

Although, I don't use SQLServer so maybe your issue is not same as I experienced.

This post has been edited by June7: May 26 2020, 02:36 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
AlbertKallal
post May 26 2020, 02:52 PM
Post#3


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


Interesting that you can edit the first time?

Ok, this (hopefully) can be solved by:

Ensure the table has a PK (I'm sure it does).
Next up?
For the number fields (especially the floating ones – non integer) you want on the SQL server side to set a default of (0) for such columns.
The above ESPECIALLY must be done if there are any true/false (bit) fields on the server side. They MUST have a default of 0, else you get that changed/write error over and over.

The above will likely fix your errors. However, in your screen shot, I notice the 18.5 – so we dealing with read/floating numbers. (Use money type here if possible).

Next up? Add what is called a “row version” to the table. The migration wizards (access or SSMA) both offer to create this field for you. Now the “type” is called timestamp, but it is the world’s worst name since it has zero to do with “time”. This column should be added to all tables. Once done you just ignore it – don’t worry about it.

Now re-link your tables.

Your error should go away. The row version column is not a must have or must do – but I high recommend adding that column.

So:
Any bit fields – make sure they have default of 0 (SQL server side – table designer).
Should do the same for any real/floating columns.
Add the “timestamp” column. I usually give it the name TS.

Re-link your tables.

Do all of the above – then post back here – your issue should be gone.

If all the above does not fix this? Then on startup or some place you have some VBA code or some other routine that runs, but try above first.

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

Go to the top of the page
 
williamlove
post May 26 2020, 04:05 PM
Post#4



Posts: 149
Joined: 8-February 06



The only VBA is a DoCmd to go to the last record on the Form Open event.

Albert, I will go thru your list very carefully. The table has lots of fields. It did not have a PK so I made the field called ID the PK. The fields are lab test results and include integers, reals, floats, bits, you name it.

I have a number of issues to work thru, like what to enter as a default for dates, and so forth. I will get to work on it and see how it goes. Thanks!
This post has been edited by williamlove: May 26 2020, 04:11 PM
Go to the top of the page
 
williamlove
post May 27 2020, 12:16 PM
Post#5



Posts: 149
Joined: 8-February 06



Albert, I think your recommendations helped.

(Another aspect was the new application had just started. What that means is, three databases that used to be stand-alone Access .mdb files were redeployed after exporting all the tables to SQL Server. I had developed the .accdb files from a capture taken a few weeks ago and I had to update data that had accumulated in tables. I did this using copy and paste. It appears these records may have been the ones causing the problems.)

None of the tables had a PK so I am making one for each table, and I am making all number fields (bits, ints, smallints, floats and reals) have a default value of 0. I will decide on the optional last step you recommended after I finish these two steps. There are a lot of tables and fields so these first two steps are quite a chore, esp when you do it very carefully.

In the most serious case the customer flagged, I did those steps on that table in SQL Server, deleted the table in Access (which does not actually delete the table in SQL Server as you obviously know) and created a new link. The records I copied during the transition are still throwing that error, but the new ones are okay. The customer is not complaining. So things have settled down a lot! Now I'm in that tedious phase of doing those steps to every table.

So the question is, when I'm done with that, and all 150 tables in three databases are done, what is the best way (or necessary way) to re-link the tables? Can I use External Tools, Linked Table Manager in Access? Or should I delete the links and recreate them? The latter would take a long time compared to the former so I don't want to do that if its unnecessary.
Go to the top of the page
 
AlbertKallal
post May 27 2020, 12:25 PM
Post#6


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


A re-link (re-fresh) using the the linked table manager should be fine.

I VERY strong recommend you use FILE DSN. (it is the default). The reason for this is that Access converts a FILE dsn to dsn-less.

What this means then you can deploy the Access application to other workstations, and it will not require you to setup the ODBC drivers or setup a DSN.

You can use the SQL driver as your choice, but be careful, since the migration wizards often will create datetime2 in place of datetime columns, and if that occurs, the you have use the native 11 (or later) ODBC drivers. The native 11 (or later) driver is a fine choice, but it is not installed by default on each workstation, so that is why I often still choose + use the "SQL driver". It is installed by default on all windows versions, and thus can make deployment less hassle.

So, if datetime2 columns are being used, then Access will see these columns as text, and not date columns. This can cause real havoc if you don't catch this problem.

R
albert
Go to the top of the page
 
AlbertKallal
post May 27 2020, 12:27 PM
Post#7


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


I will also add that the SSMAA (the SQL migration assistant for Access will

Keep and setup your PK for you.
Keep and setup the existing table relationships for you.
Keep and setup all of the indexing you had (you need to SETUP indexing on SQL server - very important).
Automatic add a timestamp for you.

So, all of that extra work can be eliminated if you use the SQL migration tool.

R
Albert
Go to the top of the page
 
williamlove
post May 28 2020, 12:36 AM
Post#8



Posts: 149
Joined: 8-February 06



Hi Albert,

I think it is too late to use the migration tool. My apps are running. The problem has died down or gone away. But I want to follow best practices. To that end I am implementing your advice:

I finished adding the PK and the default values.

Then, on one table only, I implemented what I think you wanted me to do for "row version". But I did it manually in SSMS. I found some directions to do that. I named the new column LastChange and gave it the type timestamp. The instructions said "If we try to add a column with a rowversion data type in SSMS, we will notice that there is no rowversion in the data type list as shown below. The only choice is timestamp." Before I do this for every other table, I wanted to know if you agree/approve. The picture shows the result of this simple step of adding the column.

In the same attached picture you see that when I added the PKs, I got those entries in the Indexes. Is that adequate to cover the base for your recommendation? I will read up on this topic but for now I just want to get a first rev completed.



Attached File(s)
Attached File  TimeStamp.JPG ( 95.59K )Number of downloads: 0
 
Go to the top of the page
 
AlbertKallal
post May 28 2020, 06:57 PM
Post#9


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


Yes, you done this correct.

I usually call the field "TS". It really just a house keeping column that helps access. In fact as noted, it not even a "time" setting at all!

Because you never will use the column in your SQL or code? The worse the name you give it, the better!

So, you have lastchanged. However, lastchanged is quite a nice friendly column name - one that I would like to use for say a date/time column and when say a user updated the record.

So, the name don't matter, but the worse the name is, the better, since any good name you call it is likly a good name that over time you might actually want to use for a "real world" application.

So a name that don't mean much, and a column name that you not pick up or use by accident is what I suggest. Something like last Updated etc. is a great name - but for an actual datetime column that one might want to use.

SQL server puts some kind of "binary" blob thing a ma do do in that column. The data in that column has no value to a end user - It used for row change detection - nothing else I can think of! I think this column should have been created and hidden by SQL server for us but some applications don't need it, so it is a optional feature/column that you the developer has to add. As noted, .net providers can ALSO use that rowversion system to detect if a record been changed.

But, no worries - what you have looks just fine.

R
Albert

Go to the top of the page
 
williamlove
post Jun 6 2020, 04:21 PM
Post#10



Posts: 149
Joined: 8-February 06



Albert, I have done everything except the TS housekeeping column. I will do that too, but right now my app has been working fine. I have another question, but I will post it as a new question. It is about licencing. I'd appreciate it if you'd have a look at it too. Thank you! thumbup.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    5th July 2020 - 11:26 PM