Question Access with MySql

[TexRanger]

I am trying to build a shipping information system, which will enable the company to track all its export and import consignments. This particular system is to be developed in MS Access 2007/2010 and MySQL database, should i

1] Design & develop unbound forms with Passthrough Queries
or
2] Design Normal forms bound to tables. but doing this results in "ODBC - Insert on linked table failed" error, every time i tried inserting a record through the bound form.

my first preference would be to go with bound forms, any help on how to resolve this Insert error would be greatly appreciated.

many thanks in advance

 

[gemma-the-husky]

i would design for bound forms

if the form is generating an error - try opening a table, and entering directly in the table. i expect this will fail also.

in which case, it is likely to be an error with the tables/ODBC link/MySql database etc etc.

 

[RainLover]

I would have to agree.

Also if you exported tables from Access to MySQL check the Data Types. Some Types but not many do not export too easily. Nothing you can't fix though.

I see you have used a Combo Box. Lookups in the Tables of MySQL do not work.

 

[TexRanger]

Successfully inserted a record from access into the table (attachment-1 & 2). however doing it from the forms fails miserably. I guess, i have to use the passthrough at the time of insert/update into the table.

 

[RainLover]

Stay with the BOUND Form.

Is your problem with the Combo Box?

Run some tests to see where the save fails. Just write to one field at first, then two etc.

 

[Pat Hartman]

I have used bound forms against Oracle, DB2, Sybase, SQL Server, Pervasive, and one more odd-ball whose name escapes me at the moment. These are all ODBC compliant databases as is MySQL. They all have little quirks but they all support bound forms. There is some system setting or connection issue that is causing your problem.

When using bound forms against ODBC linked tables, you need to limit the number of rows and columns you select. Novices usually bind forms directly to tables and Access linked to Jet/ACE works fine that way but once you move to a "real" relational database where the query is being processed on the server, you need to limit the number of rows you select to take advantage of that. The mistake people make is continuing their "Access" techniques when developing a client/server app and when their forms are too slow to work with, they resort to pass-through queries and thousands of lines of code when all they needed to do was to restrict their selection criteria.

I have forms linked to tables with millions of rows that provide snappy performance because I am not trying to drag millions of rows over the wire, my selection criteria brings back one or at most a few rows at a time. That's all the user can work with anyway.

 

[TexRanger]

Dear All,
After a lot of investigation into the matter, it just dawned on me, that it is the only at the time of insertion of new record that the whole thing fails with a "COULD NOT INSERT into [table name]-ODBC error", now i just need to get around this problem.

thanks for all your support

 

[RainLover]

Have you tested to see if you can write to any of the fields from the table in the front end.

The error message may be misleading.