Linked Tables: Referential Integrity & Auto Number Primary Key

[orbojeff]

I've created an Access DB where the tables are linked via an ODBC
connection to a hosted MySQL DB

I've created relationships between tables, but I'm unable to Enforce
Referential Integrity and Cascade Updates or Deletes.
How can I accomplish this?
Is there a setting I need to change on the MySQL DB?


When I insert a new record in a form I would like the Primary Key to
Auto Increment
I cannot set the Field Data Type to AutoNumber.
Do I need to use an Event Procedure in my form? (If so would that be
"Before Insert"
What is the easiest/simplest way to handle this?


Thanks
Jeff

 

[Larry Daugherty]

You need to find how to do those things with MySQL, which is not the
focus of this newsgroup.

HTH

 

[John Vinson]

I've created an Access DB where the tables are linked via an ODBC
connection to a hosted MySQL DB

I've created relationships between tables, but I'm unable to Enforce
Referential Integrity and Cascade Updates or Deletes.
How can I accomplish this?

In MySQL. Access has no way to enforce these relationships, since you
can open the MySQL database from anywhere, not just from Access;
therefore there's no option for Access to create relationships that it
would be uable to enforce.

Is there a setting I need to change on the MySQL DB?

I would check with the MySQL documentation (or website or online
help); it could be something like running a PassThrough query like

ALTER TABLE Mytable
CONSTRAINT FKSubtable
FOREIGN KEY ([primarykeyfield])
REFERENCES subtablename [linkingfieldname];

but I'm not certain of the MySQL syntax.

When I insert a new record in a form I would like the Primary Key to
Auto Increment
I cannot set the Field Data Type to AutoNumber.
Do I need to use an Event Procedure in my form? (If so would that be
"Before Insert"
What is the easiest/simplest way to handle this?

I don't know if MySQL has a datatype for autonumbers; if not, you can
certainly use the Form's BeforeInsert event. Something like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!primarykeycontrol = NZ(DMax("[primarykeyfield]", "[tablename]"))+1
End Sub

This can get more complicated if it's a multiuser application; you
*may* be able to prevent duplicates by immediately saving the record
(provided there are no other required fields). To do so put

Me.Dirty = False

immediately after setting the value of the field.

John W. Vinson[MVP]