Access Version 2003, File format 2000.i, I have a form in MSAccess that produces a write conflict under the following circumstances. There is a text box, and say it has the string "This is a sentence" in the text box. If a user were to delete the e at the end of the word sentence and insert the e back, once the user moves on to the next record (the record updates) it produces a write conflict because the data didn't change. This occurs often for me because my VBA code will do this

Thus having the same affect as if a user just altered the data without changing the data. The problem is if it has already been run before and the user clicks one of the field that runs this calculation, then when the record tries to update it will complain about Write conflict.The suggested solution I found on the internet seem to state that simply adding a timestamp column to the table would do the trick, however this has not done the trick for me. I assumed it was because they assumed it was a mssql backend but this link specifically for MySQL states the same thing: http://webcache.googleusercontent.com/sear...=clnk&gl=usAny suggestions

I have a gut feeling that we're missing a crucial piece.ormally, an erroneous write conflict is caused whenever we run two different modifications in different contexts. A example of this would be:a) Editing a form, then running an action query on the same recordb) Editing same record in two different Recordset variablesYour code, as far as I can see, is fine because your code is modifying the form itself which should appear as coming from same context. If your code for instance somewhere else ran an update query, that would be a problem, and we'd want to remove the action query and replace it with a code that modify the same values in the form directly.So, do your VBA code reference a new recordset or query that is not a part of the form itself and thus would be in a different context? Another thought is that did you specify the ODBC connection to return matching rows instead of affected rows? MySQL documentation does suggest turning this on for Access clients.HTH.

Since I have a confusing set up I was able to boil it down to the smallest element possible. Just the link table.When I open the link table it shows me all the rows. The first row for example has the data 0,0,-1,-1, etc...If I highlight the -1 and change it to zero and then click next record it is fine. If however I highlight the -1 and type -1 and then click the next record then it produces the write conflict. This gives me the impression that access detects that there was new user input, but when it checks with MySql it discovers that the data is exactly the same it thinks there is something wrong. (My interpretation of what access [or mysql] is doing maybe wrong)After finding the article I linked to I did specify the ODBC connection to return matching rows, but this did not affect anything as far as I can tell.

I wonder if you table has any nulls in the boolean columns. Because if they do, you'd certainly be getting write conflict errors in such cases since MS-Access doesn't like ODBC linked nullable boolean fields.

It should not cause an error by itself you are right, unfortunately it does so whether or not there is a timestamp column present. The link you provided gave me a few ideas of where to search, but it will take me some time to look these ideas up.

I'm simply using the linked-table. think, that MSAcess is not getting the locks on record, so I think it is a setting in either MSaccess, ODBC, or MYsql that is causing the problem, but finding what and where is the quest haha.

Well, Access itself does not enforce any kind of locking against ODBC tables, leaving it up to the server entirely to lock as it see fit. Write Conflicts are caused whenever Access sees that the content of record has changed since it last looked at it when it try to make changes. That's why sometime triggers or timestamps can cause problems as well running code in different contexts. The next suggestion is if you can, turn on general log and read the output (e.g. tail -f) as you work in Access. It usually tells a lot about what it is going on.

I'm sorry I'm not familiar with tail -f. How would I implement that, or do you have a link? oes this mean I have to turn on General logs in the configuration file or can I just run it from the ssh.Ofound that our server is running version 4.1 so I'm making a full backup to see if upgrading to 5.1 will change anything. But I think getting the general logs first is a better idea.

Disclaimer: I've not worked on any version prior to 5.0 so what I may write may not apply here. Consult MySQL manual. I also should point out that 4.1 and earlier is not under active development anymore. The following also assumes you are using a bash shell. This will not work if MySQL is running on a Windows host since Windows does not have a bash or tail.Link to 4.1 MySQL manual - scroll down a bit to see the EOL announcement for 4.1If you have SUPERUSER privilege, you should log in to MySQL and issue this command in the client:

CODE

SET GLOBAL general_log = 'on';

To find where the log is being written to, issue this command:

CODE

SHOW VARIABLE LIKE 'general_log_file';

Copy the path given and save that information.Exit MySQL client back to the bash shell, and issue this:

CODE

tail -f <full path to the log>

That will then give you the realtime output of last 10 lines in the general log being written. Note that you may or may not have the permissions, again depending on where the file is located and whether your user account has permissions to the location. If you don't have a permission, use sudo.Also, this is very important: When you're done analyzing what's going on, turn general log off. You don't want to forget that step.HTH.

I upgraded to 5.0On either case I was unable to do SHOW VARIABLE LIKE 'general_log_file'; saying that it doesn't understand anything after the show. and ERROR 1193 (HY000): Unknown system variable 'general_log'This isn't a big deal, the users have been used to receiving this error message from time to time, I guess I'll have to fiddle around with it later

Looks like general_log was added to 5.1. orry. A kludge is to use slow query log which should be available in 5.0 and set the time to say, 0.0001 to mark all queries as "slow" and thus get them in the log.[url=http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_log-slow-queries[/url]HTH.

I haven't tried the General_log but what I suspect happens is that if the user manipulates the form, table, or what have you, and upon msaccess updating the row, if the data is exactly the same then Mysql will reply"

CODE

0 row(s) affected by last query. Duration for query: 0.XXX sec

And then MSAccess sees the 0 row(s) and says, hey our request got rejected the record must have been edited and locked by someone else. That is what I suspect happens. After searching all day for an answer to resolve it, I got super angry. And in that bit of anger an idea came to me. The "solution" (which is more of just a work around) Is that I removed all explicit data changes in the form that I was using.So that

So basically no form element is altered unless their values change and the Me.Refresh prevents the user from putting on thing, removing it again, and getting the write conflict message yet again.EDIT -------------------------------------It should be noted that if somebody uses this as a fix, any element that calls this function would cause the form to be refreshed so if there are any fields that are indicated as "not Null" and they are not filled in first, it may or may not cause issues to crop up.