ODBC--Insert table failed (1 Viewer)

karent

New member
Local time
Yesterday, 21:35
Joined
Oct 13, 2020
Messages
8
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

Access World Site Owner
Staff member
Local time
Today, 01:35
Joined
Sep 28, 1999
Messages
7,305
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

AWF VIP
Local time
Today, 01:35
Joined
Jul 26, 2013
Messages
10,355
Have you tried refreshing the link, and ensuring it picks up the PK field?
Other than that delete the link and recreate it.
 

Isaac

Lifelong Learner
Local time
Yesterday, 18:35
Joined
Mar 14, 2017
Messages
8,738
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

New member
Local time
Yesterday, 21:35
Joined
Oct 13, 2020
Messages
8
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

New member
Local time
Yesterday, 21:35
Joined
Oct 13, 2020
Messages
8
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

Lifelong Learner
Local time
Yesterday, 18:35
Joined
Mar 14, 2017
Messages
8,738
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

New member
Local time
Yesterday, 21:35
Joined
Oct 13, 2020
Messages
8
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

New member
Local time
Yesterday, 21:35
Joined
Oct 13, 2020
Messages
8
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

AWF VIP
Local time
Today, 01:35
Joined
Jul 26, 2013
Messages
10,355
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

New member
Local time
Yesterday, 21:35
Joined
Oct 13, 2020
Messages
8
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

Lifelong Learner
Local time
Yesterday, 18:35
Joined
Mar 14, 2017
Messages
8,738
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

New member
Local time
Yesterday, 21:35
Joined
Oct 13, 2020
Messages
8
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

Lifelong Learner
Local time
Yesterday, 18:35
Joined
Mar 14, 2017
Messages
8,738
Research pass through queries in Access if desired.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:35
Joined
Jan 20, 2009
Messages
12,849
You probably need to install an upgraded version of the mySQL ODBC driver on the clients to match the server version upgrade.
 

isladogs

MVP / VIP
Local time
Today, 01:35
Joined
Jan 14, 2017
Messages
18,186
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

Super Moderator
Staff member
Local time
Today, 12:35
Joined
Jan 20, 2009
Messages
12,849
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

..forever waiting... waiting for jellybean!
Local time
Today, 09:35
Joined
May 7, 2009
Messages
19,169
See post#1, she can insert record but only 1 table has a problem.
 

Users who are viewing this thread

Top Bottom