Linked Tables: Referential Integrity & Auto Number Primary Key

  • Thread starter Thread starter orbojeff
  • Start date Start date
O

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
 
You need to find how to do those things with MySQL, which is not the
focus of this newsgroup.

HTH
 
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?
Click to expand...

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?
Click to expand...

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?
Click to expand...

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]
 
Back
Top