Re: MySQL and ACCESS
Posted by:
Tim Dodge
Date: April 12, 2005 08:39AM
Hi,
A little caveat that I have encountered and still not able to resolve. I am in a similar situation where I am splitting off the data tables to a mysql back-end. In Access there are several instances where I use a form with a subform, when adding a new record. A one-to-many relationship is the example of Purchase Orders. Supplier's Info in the main form and stored in a tbl_purchase_order, with purchase order details in the subform whose data is stored in tbl_purchase_order_details. The link between these tables, and the form/subform is the PurchaseOrderID field, which is the Primary Key w/AutoNumber format in tbl_purchase_order, and PurchaseOrderID is number in the table tbl_purchase_order_details.
When exporting these tables to mysql, and then linking to them, PurchaseOrderID field in the tbl_purchase_order, is still the Primary Key, but the format is now number, instead of AutoNumber. I have also changed the PurchaseOrderID field properties in mysql so that it is unsigned, not null, and auto_increment, but to no avail.
Here lies the problem - after adding supplier information in the main form, I go into the subform to add the purchase order details, all data in the main form shows #Deleted. Unfortunately, at that point the link between them is broken, because the PurchaseOrderID is unknown at that point. What is saved at this time is the new record in tbl_purchase_order, and has the correct unique ID per the auto_increment criteria, but the purchase order details are not saved.
This same table(s) were exported to a MS SQL server backend, and that worked flawlessly. The format in Access still shows the PurchaseOrderID field still as the Primary Key, and also as an AutoNumber, and therefore the process works identical to that when the tables are inside the access files.
It was suggested that this is a bug in the version of the mysql server that I was using, and to use an older mysql server version (4.0.18), but same problem. I did not try to use an older ODBC driver verison (I am using 3.51.X).
I am beginning to think I have a fundamental table(s) design error, or a critical field is missing that is required by mysql to interpret MS Access AutoNumber (auto_increment) fields correctly.
Or maybe the form/subform process I have described does not work using a mysql server?
Any suggestions out there? I can't be the only one that experienced this basic migration issue?
Tim