Inserting records to MYSQL using ODBC

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

im using MS Access as a front end to link to MYSQL. although this is
possibly an Access issue i thought someone would be able to help me.
the error message "ODBC--insert on a linked table 'tablname' failed" is
displayed when i attempt to insert records to the MYSQL table.

Does anyone know of a way round this ?? im using the most recent MYSQL
driver and i have only text fields in my insert statement.

any help appreciated.


Re: Inserting records to MYSQL using ODBC

Quoted text here. Click to load it

The error message is, of course, very general and unhelpful.

How was the insert created?  Did you use the Access query builder?  If so
it's not likely you have a bad SQL statment.  Or did you write the "INSERT"
statement yourself?  I make mistakes all the time - and I'm an expert ;-)
It would be helpful to post your SQL INSERT statement here.

I'm going to assume that you can double click on the linked table and when
you do, Access brings up the table record view.

A few of the most common problems:

    1) The usr/pwd used to set up the linked table ODBC does not have
permission to INSERT/DELETE records. You can view records (SELECT) but you
can not INSERT or DELETE.  You may want to try this directly in the Access
table view.  You should be able to go to the end of the table and type in a
new record manually.

    2) The MySQL table structure has been altered since the link was created
and it needs to be updated/refreshed.  Access is confused about the table
structure.  A common situation because you can change the table structure in
MySQL and neglect to tell Access about it!

    3) You have a malformed SQL statement - not likely if you used the
Access query builder.

There's more but I think that's probably 99%+ of all the problems your
likely to see ;-)
Thomas Bartkus

Re: Inserting records to MYSQL using ODBC

thanks for that - im sorry for the lack of information.

Basically i used the Access query builder to build an insert query. Its
a very simple table inserting one automunber field & text/memo fields.
this inserts into a table with integer in MYSQL and varchar. I probably
think the problem relates to ur first suggestion.

I suppose all i have to do is use the root account to test this - il
give it a whirl later.


Site Timeline