ODBC--Insert table failed

[karent]

I am using Access 2010 with ODBC (5.3) connection to Mysql database. I recently updated from Mysql 5.6 to 5.7. The tables are linked. There is one table that will not allow inserts. Edits work. Other tables allow inserts. Just one table gives me the above error message. There is a primary key defined. I am not a programmer so if anyone has some simple language suggestions I'd appreciate it.

 

[Jon]

Welcome to the forums!

To help us better understand your needs, I would be very grateful if you could complete this 2 minute survey: Click here for the survey

All surveys are anonymous and not linked to your account.

I want to keep this the very best place for Access and your feedback helps us!

 

[Minty]

Have you tried refreshing the link, and ensuring it picks up the PK field?
Other than that delete the link and recreate it.

 

[Isaac]

I'm not experienced with Mysql, but my advice would be similar regardless of back end.
Have you contacted the DBA (etc) to ask them to provide you with any constraints in place on the server regarding inserts? Or, essentially, a DDL extract? Theoretically, one cannot expect to be able to blindly utilize a table structure without knowing what the constraints are. You could potentially sit there all day trying to do an insert with different data each time and never happen to meet the rules. I would just ask.

 

[karent]

Have you tried refreshing the link, and ensuring it picks up the PK field?
Other than that delete the link and recreate it.

Yes I have deleted the link and relinked. Sadly no difference.

 

[karent]

I'm not experienced with Mysql, but my advice would be similar regardless of back end.
Have you contacted the DBA (etc) to ask them to provide you with any constraints in place on the server regarding inserts? Or, essentially, a DDL extract? Theoretically, one cannot expect to be able to blindly utilize a table structure without knowing what the constraints are. You could potentially sit there all day trying to do an insert with different data each time and never happen to meet the rules. I would just ask.

Structure / constraints have not changed in 5 years. I am sadly the DBA. Only change is mysql update.

 

[Isaac]

Ok so you are the DBA. How about taking the literal SQL from MS Access insert query, making any minor syntax adjustments to meet ANSI or Mysql syntax, and then running it on the server to see if it works there? In which context, surely you would get a more informative error message by using whatever is the going tool for Mysql connection/dev?
Lastly is there a trace type tool?

And try Minty's suggestion about re-linking, make sure to specify which column is PK, when Access prompts.

 

[karent]

Ok so you are the DBA. How about taking the literal SQL from MS Access insert query, making any minor syntax adjustments to meet ANSI or Mysql syntax, and then running it on the server to see if it works there? In which context, surely you would get a more informative error message by using whatever is the going tool for Mysql connection/dev?
Lastly is there a trace type tool?

And try Minty's suggestion about re-linking, make sure to specify which column is PK, when Access prompts.

I

 

[karent]

I did try Minty's suggestion and there was no change. I tried an insert directly into the sql table and it worked. So there is something with the ODBC connection to Mysql.

 

[Minty]

I have had a similar weird issue with SQL server but to do with date fields blocking edits, not new records.
Has the update changed or altered/adjusted any field data types?

Are you doing the insert directly in the table for testing? Try doing it in a simple select query based solely on the table.

 

[karent]

I have had a similar weird issue with SQL server but to do with date fields blocking edits, not new records.
Has the update changed or altered/adjusted any field data types?

Are you doing the insert directly in the table for testing? Try doing it in a simple select query based solely on the table.

I can insert a record directly into the table in sql but not in access. The field types do not appear to have changed.

 

[Isaac]

I'm not suggesting you "give up" on troubleshooting this, but just mentioning the workaround if needed ...
You could use a pass-through query in Access, and use Mysql-SQL there. I mean, any time you have a powerful server as a back end, heading in that direction isn't a bad idea anyway..

 

[karent]

I'm not suggesting you "give up" on troubleshooting this, but just mentioning the workaround if needed ...
You could use a pass-through query in Access, and use Mysql-SQL there. I mean, any time you have a powerful server as a back end, heading in that direction isn't a bad idea anyway..

Thank you for your suggestion but I am so not an IT person and truly don't understand it.

 

[Isaac]

Research pass through queries in Access if desired.

 

[Galaxiom]

You probably need to install an upgraded version of the mySQL ODBC driver on the clients to match the server version upgrade.

 

[isladogs]

Research pass through queries in Access if desired.

Passthrough queries are read only

 

[Isaac]

Passthrough queries are read only

Correct. How is this relevant?

 

[isladogs]

The OP was talking about inserting records into the ODBC table.
Presumably, running an append query based on a read only passthrough query won't work. Are you suggesting it will?

 

[Galaxiom]

The OP was talking about inserting records into the ODBC table.
Presumably, running an append query based on a read only passthrough query won't work. Are you suggesting it will?

Isaac was suggesting using a PassThrough insert query to insert the records (as opposed to trying to insert into a recordset from a PassThrough Select). Anything that can be done with a query on the server can be done with a PassThrough query (with the appropriate permissions).

BTW I still believe the first thing to try would be updating the ODBC driver. They are using Version 5.3 driver with a Version 5.7 database.

 

[arnelgp]

See post#1, she can insert record but only 1 table has a problem.