MySQL Forums
Forum List  »  Microsoft Access

Posted by: Tim Dodge
Date: April 12, 2005 08:39AM


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?


Options: ReplyQuote

Written By
April 02, 2005 09:10AM
April 03, 2005 08:37AM
April 12, 2005 08:39AM
May 25, 2005 08:24AM
May 26, 2005 07:17PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.