I decide to write about the topic when one day i stuck with Access tables and forms which arelinked to MySQL server through Phpmyadmin.New records were insterting but existing records are not updaing and show a message titled write conflict message where save record button is disabled.*. I tried many options like restarting MS access and MySQL server.
I searched over internet and collect information about the error. I realize from resarch that this problem may due to the reason one of given below.
- 1. Some binary or bit fields are present in the table which are possible fileds true / false or 0 or 1 values and having some null values.
- 2. There is no proper timestamp type field is present in the table.
- 3. Primary key with indexing is not created.
I examine my table and found that a primary key field is present in the table. and I am not using any true false bit field. The only issue it is that I don’t have a field with timestamp data type in my table. Now the issue was that how to implement the timestamp filed becasue I have more than 1200 rows of records as well as many columns. First I have a clomn through phpmyadmin named timestamp1 and datatype as timestamp. Default value current date time.
First of all, as I stated above, add a filed / column named timestamp1 and give him a datatype of timestamp. After applying this now you have a rowversion or a timestamp field. Now Make a little change in any record within phpmyadmin through your web browser and save It. You will see that current time and date will be updated in timestamp field automatically. This change will provide information to linked table with MS Access about last edit in record. Now open the this linked table in MS access (Like MS Access 2010 as I tested it in this version of Access). You will observe that now the line you edit before in phpmyadmin is editable and write conflict on this row of record is gone away. but the conflict on other rows will still remains. so you have to update all records in the table to make them editable in MS Aceess linked table.
This is not an easy task when you decide manually as in my case there is more than 1200 records are available. So here is a trick to do it.
First Add a new filed of integer type named it “temp”. This is temporary field which we use for our purpose to update all records. Now go to phpmy admin and run this command.
UPDATE tableName SET temp=1 WHERE PrimaryKeyField>0
Basically What are we doing here. Suppose we have a primary key field which have type integer and it is set to auto increment. So we are giving a condition greater than 0 means, we are updating the temp field to 1 in every row of the table (all rows of table). This action would update every timestamp relating to every record. Now the job of temp field completed. Our basic goal was to update the every timestamp in column. Now go the MS Access table which is linked to MySQL server through PHPmyadmin. Try to edit recod. It would be editing and saving fine. Write conflict error message is gone away. Enjoy the error free Access table or form linked with MySQL.