Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2012
    Posts
    3

    Unanswered: Write Conflict Using Access as FE to MySQL BE

    I have an Access 2007 connecting to my MySQL table via ODBC. I've read everything I can find on this problem. I've got a TimeStamp column, no booleans and only one numeric and it defaults to zero. I was having trouble getting the dates in the right format so I exported the data from Excel as a tab delimited text file after changing all the dates to English(UK) YYYY-MM-DD.
    The TimeStamp column was created via an SQL statement (alter table members change `tsChanged` `tsChanged` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) since EarthLinks 2.11 phpMyAdmin would not let me do it directly.

    Everything is fine for any new records I create in either Access or via phpMyAdmin directly on the server. However, any of the records I imported cannot be edited via Access - I get the dreaded Write Conflict message. I tried an update query on the MySQL server, and updated the TimeStamp to NOW() but that did not fix the problem - I could still edit the records I created after the import but not the imported ones.

    Also, I checked the ODBC connector option for "Return matched rows instead of affected rows."

    I am deperate. I've tried everything I can think of and every suggestion that I've found to no avail.

    Please help if you can!
    J. J. Campbell, Arlington, VA

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I don't know for sure with MySQL, but the behaviour you describe is the same when you work with MS SQL Server. You cannot define default values or computed columns at the server level then use a form that has its recordsource based on a linked table with such contraints defined. More precisely: you cannot create a Read/Write DAO recordset (dbOpenDynaset option) on such a table and update it.

    In such a case, when you create a row, the value of the default column is changed at the server level while the other columns are changed by Access. This generates a write conflict: attempt to change the same row from the server and from Access at the same time.

    A similar problem occurs when you modify the contents of a column that is used to derive the value of a computed column: Access tries to change the value of the field while the server tries to change the value of the computed column for the same row.

    Unfortunately, there are no simple and satisfactory solutions to this problem. You can:

    1) Supply the default or computed value(s) in the Access application.

    2) Create a View that does not include the "offending" columns, then create a linked table on that view: if the view comprises an Identity column (usually the primary key), it will be updatable.

    3. Use SQL Pass-Through queries to exchange data between the server and the Access application.
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Never had the occasion to use a MySQL back end, so this is not my area of expertise, but I do know, in order to edit Records in such an app, the Tables have to have Primary Keys, or at least unique indexes, defined for them. Is there a PK as part of the data that is being Imported? If not, I think they’ll continue to be Read-Only, using ODBC.

    Linq ;0)>
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I don't use MySQL, but in SQL Server a TimeStamp (data type) field is used internally by the program. Have you tried not populating that field during import? Or use a date data type for the field?
    Paul

  5. #5
    Join Date
    Aug 2012
    Posts
    3
    I'll try to answer everyones questions below:

    Sinndho,
    I don't have any calculated fields or any defaults other than the PK. I'm not using any forms at this point, just trying to edit records from the Access DataView. I think I follow you re the dynaset but since I can create new records either in Access or directly on the server and then can edit those records to my hearts content but not any records that I imported via the tab delimited using Load Data, I don't see how it can be a recordset problem.

    My little table was created with:
    REATE TABLE members
    (
    ID INT(11) PRIMARY KEY AUTO_INCREMENT,
    fName VARCHAR(50) NOT NULL,
    lName VARCHAR(50) NOT NULL,
    Rank VARCHAR(50) NULL,
    Address VARCHAR(50) NOT NULL,
    City VARCHAR(50) NOT NULL,
    State VARCHAR(50) NOT NULL,
    Zip VARCHAR(50) NOT NULL,
    Phone VARCHAR(50) NULL,
    Phone2 VARCHAR(50) NULL,
    eMail VARCHAR(50) NULL,
    dBirth DATE NULL,
    Spouse VARCHAR(50) NULL,
    Membership VARCHAR(50) NOT NULL,
    dRetired DATE NULL,
    dEntered DATE NULL,
    dJoined DATE NULL,
    DuesPaid VARCHAR(50) NULL,
    dDeath DATE NULL,
    Due_LM Year(4) NULL,
    Address2 VARCHAR(50) NULL,
    Changes VARCHAR(25) NULL,
    dCorrected DATE NULL,
    tsUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP

    )
    I changed the Year(4) to an Integer(4) with a default of 0 attempting to solve the problem. Since that didn't work, I'll probably change it back.

    Missingling,
    Yes, I have a primary Key. Should it not be part of the tab delimited data that I import?

    pbaldy,
    I'v tried populating and not populating the TimeStamp field several different ways. Not having it at all on the imported data as well as having it. I've also done an update query on the server to change the data to NOW(). Problem persists - the "old" imported data is uneditable while any new records I created are fine.

    Thanks for your help - keep the ideas coming please!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by jjcampbell View Post
    I don't have any calculated fields or any defaults other than the PK.
    As far as I can understand the table definition you provided and although I'm not familiar with the specificities of MySQL DDL syntax, it seems to me that [tsUpdated] has a DEFAULT constraint defined:
    Code:
       tsUpdated       TIMESTAMP    DEFAULT CURRENT_TIMESTAMP 
                                    ON UPDATE CURRENT_TIMESTAMP
    Actually it looks like a DEFAULT contraint mixed with a trigger, from MS SQL Server viewpoint (Triggers in MS SQL Server also cause the same behaviour).
    Quote Originally Posted by jjcampbell View Post
    I'm not using any forms at this point, just trying to edit records from the Access DataView. I think I follow you re the dynaset but since I can create new records either in Access or directly on the server and then can edit those records to my hearts content but not any records that I imported via the tab delimited using Load Data, I don't see how it can be a recordset problem.
    A linked table behaves like a special kind of Pass-Through query that is updatable (Dynaset) while usual SELECT pass-through queries are read-only (Snapshot). You can verify this by creating a special key in the Registry:
    Code:
    Windows Registry Editor Version 5.00
    
    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Debug]
    "JETSHOWPLAN"="ON"
    (the version of the Jet Engine may change according to the version of Access) which will force Access to generate a kind of .log file for its SQL operation (showplan.out in the default data directory defined for Access) and that you can examine to understand what actually happens between your program and the server.

    Please try removing the constraint on [tsUpdated] temporarily and see what happens.
    Have a nice day!

  7. #7
    Join Date
    Aug 2012
    Posts
    3
    Sinndho,
    I'll try your suggestions but I added the TimeStamp and it constraints because when I googled the write conflict problem, many posts said this was the fix.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I never pretended that it was the solution. As I wrote before, I'm not familiar with MySQL and its use with Access. If you used MS SQL Server instead of MySQL though, I'm almost sure that the problem you describe would be caused by a constraint, a computed column or a FOR UPDATE trigger.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •