Can't insert to some tables through ODBC

[morsagmon]

Hello.

I'm connecting to MySQL through ODBC 5.1.
I'm using MS-Access 2007 as the front end to access the DB.

Trying to insert rows to some of the tables, yields an error:
ODBC - insert on a linked table failed.

The tables that won't allow me to insert records are the "one" tables of a "one-to-many" pairs. For example, I can add rows to "Order Lines" table, but not to "Orders" table.

There are no FK defined on any of the tables.
There are no relationships defined on this Database project (there are defined on another Database project, which is closed).

Any ideas?
Thanks!
Mor

 

[gemma-the-husky]

it might be to do with no primary key on the tables that fail

if there is no primary key, i think an ODBC table becomes non updateable

if you just open the table directly, can you do anything with it? you may get more info if you try that?

 

[Galaxiom]

I have ODBC connections to SQL Server tables with no primary key.
I can add and edit records directly or via queries.

However I do have other SQL server tables where the linked table does not have a new record line. I can only append records with an insert query. I could find nothing in the table properties that would suggest why this would be so.

 

[morsagmon]

Thank you guys.

I have a primary key defined on every table.
Also, I am able to insert into the problematic tables via the same ODBC driver from Excel, for example, let alone directly into the tables using MySQL tools.
It's just from Access that I'm getting this error.

Thanks!

 

[gemma-the-husky]

is there possibly some constraint in one the table affecting the insert?

eg, required fields, or 1 date must be after another - or even just a RI problem - I think you might get such an error then.

maybe a timing issue - delayed write, or similar error. I recollect vaguely we have queries failing from to time with a similar unhelpful manner, but I cant recollect what the resolution was on ewvery occasion

 

[morsagmon]

Dave,

The tables are very simple on the DB side, no such constraints, no FK.
Again, I could insert records freely from other clients through ODBC (Excel, Magic UniPaaS).
Thinking of dates - I will double check that one thing that comes to mind.
What is an RI problem?

Thanks!

 

[morsagmon]

Problem solved!
Apparently, when there's a Date-type field, and you leave it blank (null?) - it rejects the write operation.
Once I enter a valid date value - the operation completes OK.
Many thanks to you all for your ideas and support!
Mor

 

[Galaxiom]

What is an RI problem?

Referential Integrity. When RI is implimented a child table cannot be updated or appended to with a record having a value in a field that is not included in the RI related field of the parent table.

 

[morsagmon]

Galaxiom,
Thanks. Yes, I'm familiar with referential integrity, didn't recognize the "RI" shortcut...

Best!

 

[Galaxiom]

I figured you probably did know that. Sometimes I just post for the sake of completeness so anyone who comes along later searching for an answer doesn't need to wonder.