Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2003
    Posts
    17

    Unanswered: Updating MySQL records with Access

    I have an online database created in MySQL.

    I have a local Access 2000 database which links to a table in the MySQL database using MyODBC ODBC drivers.

    I can see every record in the online MySQL database table in access.

    Everytime I attempt to make a change to a record, or add/delete a record. Access does not let me amend it.

    Does anyone know a way I can overcome this?

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: Updating MySQL records with Access

    Originally posted by sametch
    I have an online database created in MySQL.

    I have a local Access 2000 database which links to a table in the MySQL database using MyODBC ODBC drivers.

    I can see every record in the online MySQL database table in access.

    Everytime I attempt to make a change to a record, or add/delete a record. Access does not let me amend it.

    Does anyone know a way I can overcome this?
    Im thinking that you are having a permision problem in that your mysql user id has no permisions to make the changes you need. Change your mysql profile and then relink the tables, you should have what you need then.
    Jim

  3. #3
    Join Date
    Jan 2003
    Posts
    17
    Jim

    Thanks for your suggestion, but I am not sure I have any way of changing my mysql profile.

    My site is hosted on a server which comes pre-configured with MySQL/PHP4/PHPMyAdmin and I am allowed to create up to ten databases.

    For each database I create I am asked for a username and password.

    To edit the database I need to provide the username and password I used to set up the database in a server control panel, this then takes me to a PHPMyAdmin gui (which is not on my server!). Thereafter within PHPMyAdmin I am able to amend records and tables as much as I like.

    I am using the same username and password in my ODBC settings to connect to the database. It connects fine but does not allow any record amendments. I get a message "this recordset is not updateable".

    One more point, which I am not sure may be relevant. I can create a new table in Access and export it using the ODBC connection to my MySQL database and it goes up fine!

    This is somewhat frustrating.
    Last edited by sametch; 01-22-03 at 13:16.

  4. #4
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by sametch
    Jim

    Thanks for your suggestion, but I am not sure I have any way of changing my mysql profile.

    My site is hosted on a server which comes pre-configured with MySQL/PHP4/PHPMyAdmin and I am allowed to create up to ten databases.

    For each database I create I am asked for a username and password.

    To edit the database I need to provide the username and password I used to set up the database in a server control panel, this then takes me to a PHPMyAdmin gui (which is not on my server!). Thereafter within PHPMyAdmin I am able to amend records and tables as much as I like.

    I am using the same username and password in my ODBC settings to connect to the database. It connects fine but does not allow any record amendments.

    One more point, which I am not sure may be relevant. I can create a new table in Access and export it using the ODBC connection to my MySQL database and it goes up fine!

    This is somewhat frustrating.

    Some times my stupidity amases myself!!!!! you need a primary key for access to let you make updates/add records. put a primary key on the table in mysql and then RELINK the table. That should work fine
    Jim

  5. #5
    Join Date
    Jan 2003
    Posts
    17

    Talking

    Jim

    Your a star! Because I was only testing the principal I didn't bother with a primary field I just created a simple table with two fields FirstName and LastName. I went back added an autoincrement ID field set to primary and all now works well.

    One final question though. If I add a record in Access once I move to another record in the table all the field values for the new record show "deleted". If I check online it is added fine and if I close and open the table in Access it is there.

    Do you or anyone else know a way around this?

  6. #6
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by sametch
    Jim

    Your a star! Because I was only testing the principal I didn't bother with a primary field I just created a simple table with two fields FirstName and LastName. I went back added an autoincrement ID field set to primary and all now works well.

    One final question though. If I add a record in Access once I move to another record in the table all the field values for the new record show "deleted". If I check online it is added fine and if I close and open the table in Access it is there.

    Do you or anyone else know a way around this?
    Uggg I have neaver seen that. How are you navigating?

    Jim

  7. #7
    Join Date
    Jan 2003
    Posts
    17
    Jim

    It matters not how i do it. If I enter a record directly in the linked table in Access as soon as I commit the record all fields are replaced with deleted. Similarly if I create a quick autoform and add a record and then commit it it also displays deleted in all fields. Refreshing the record makes no difference. The only way to display the new record is to close the form and re-open or close the table and re-open.

    I have attached an image for you to look at:
    Attached Thumbnails Attached Thumbnails deleted.jpg  

  8. #8
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by sametch
    Jim

    It matters not how i do it. If I enter a record directly in the linked table in Access as soon as I commit the record all fields are replaced with deleted. Similarly if I create a quick autoform and add a record and then commit it it also displays deleted in all fields. Refreshing the record makes no difference. The only way to display the new record is to close the form and re-open or close the table and re-open.

    I have attached an image for you to look at:
    This may be an issue with how access interacts with mysql.
    If you have to make a form maybe make a commit button with a refresh code behind it??
    Jim

  9. #9
    Join Date
    Jan 2003
    Posts
    17

    Smile

    Jim

    I was thinking along similar lines, but refresh seams not to do anything. It looks like I may need to create a commit button that closes and re-opens the form. Unless anyone knows a better way.

    Thanks for your help

  10. #10
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by sametch
    Jim

    I was thinking along similar lines, but refresh seams not to do anything. It looks like I may need to create a commit button that closes and re-opens the form. Unless anyone knows a better way.

    Thanks for your help
    maybe a form/subform type thing.....where you can do a docmd.requery???? Though that may be an issue with inserting records....Just brain storming
    Jim

Posting Permissions

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