Editing foreign key names in MS Access

[guru_ross]

I have an existing Microsoft Access database with foreign ke
constraints. I would like to edit/specify the names explicitly for th
foreign keys. The names are plainly visible in the MSysObjects an
MSysRelationships tables, however they do not appear to be editable i
a standard table view (with the database opened in Exclusive mode)
Short of deleting the relationships and issuing ALTER TABLE commands
is there a more direct GUI approach to changing these constraint objec
names (for example, MS SQL Server provides the name in an editabl
textbox in the FK Properties dialog)

 

[Douglas J. Steele]

Not sure I follow your issue. You can change the name of foreign keys. For
instance, if the FK in table Child is named ParentId, you can change it to
FKId using:

CurrentDb.TableDefs("Child").Fields("ParentId").Name = "FKId"

 

[guru_ross]

Not sure I follow your issue. You can change the name of foreign keys
For
instance, if the FK in table Child is named ParentId, you can change i
to
FKId using:

CurrentDb.TableDefs("Child").Fields("ParentId").Name = "FKId"

Yes, I could change it programmatically or via an SQL statement usin
ALTER TABLE, however I am looking for a simple GUI method to do thi
(i.e. something within the standard MS Access database application)
Call me lazy; I am hoping to avoid writing 30 ALTER TABLE statement
(this time) and more of them in the future as this comes up for othe
databases. Thanks

 

[guru_ross]

Additional Info:

I am specifically referring to the actual relationship between tw
tables, not the fields used for those relationships. For example, b
default, if you create a FK between "Table1.ID1" and "Table2.ID2", the
Access creates a FK named "Table1Table2". I want to rename that t
"FK_Table1ID1_Table2ID2" without deleting the existing relationship
and writing SQL statements to recreate the foreign key constraints wit
explicit names

 

[TC]

Well, write some code to automate it! Suitable VBA code could
automatically determine the names of all the fields in all the tables,
filter for those involved in relationships, and display a list where
you could enter the new name(s) required. This would change it to a
simple data entry task.

But personally, I'd be waiting for this to happen waaaaay more times
than 30, before I went to all that bother. Cut & paste is a marvellous
thing!

As for the MSys tables, you won't be able to edit those - ever. Those
are system tables which are solely for Access & Jet. Since they are not
documented, you'd be unwise to mess with them, even if Access let you.

HTH,
TC [MVP Access]

 

[guru_ross]

As for the MSys tables, you won't be able to edit those - ever. Those
are system tables which are solely for Access & Jet. Since they ar
not
documented, you'd be unwise to mess with them, even if Access let you.

Alright, I guess that's that. Maybe I will write a small stand alon
app that can list out all relationships and allow renaming (by deletin
the selected one and recreating it with the user-indicated new name). I
could be a fun little side project. Thanks

 

[Jerry Whittle]

If you are messing with the MSys tables, I hope that you have recent backups.
VBG!

If you have Referential Integrity enabled in the Relationship window between
the tables and Cascade Update selected, changing a value in the Primary Key
will automaticly cascade to the FK values.

Having a Primary Key with "meaning" and / or can be changed at management's
whim is a very good argument for just using Autonumbers to link your tables.

 

[guru_ross]

Thank you for the reply, however you have missed the point entirely. A
stated, I was looking for a way to edit the name of <b>th
relationship</b> between two tables, not the fields or the table
themselves.
And yes, I always make backups. And no, Autonumbers are a horrible P
for an enterprise solution, but that's not what this thread is about

 

[Tim Ferguson]

Alright, I guess that's that. Maybe I will write a small stand alone
app that can list out all relationships and allow renaming (by deleting
the selected one and recreating it with the user-indicated new name). It
could be a fun little side project. Thanks.

The Help File says that the .Name property of the Relation object is read-
write before the object is appended to its collection, and read-only
afterwards.

Tim F

 

[guru_ross]

guru_ross <guru_ross.21hc9z (AT) no-mx (DOT) forums.yourdomain.com.au
wrote in
(AT) no-mx (DOT) forums.yourdomain.com.au:

The Help File says that the .Name property of the Relation object i
read-
write before the object is appended to its collection, and read-only
afterwards.

Tim F

Good info, thanks.

For completeness and other people's reference, I am including what
always knew would work, but was attempting to avoid due to it'
cumbersome nature.


Code
-------------------
--- Assumes default constraint already created within Access GUI
ALTER TABLE Table1 DROP CONSTRAINT Table1Table2

--- Now recreate the constraint with the desired name for future reference
ALTER TABLE Table1 ADD CONSTRAINT FK_Table1ID1_Table2ID2 FOREIGN KEY (ID1) REFERENCES Table2 (ID2)

 

[TC]

That doesn't matter. He just reads the existing relationship, takes
note of its characteristics, deletes it, then recreates it with the
same characteristics (but a new name). He can do all that through code


TC [MVP Access]

 

[TC]

It would be an excllent way of learning more about the relevant parts
of the objet model.

Here is a very rough start:

(untested)

dim db as database, td as tabledef, fld as field
set db = currentdb()
for each td in db.tabledefs
debug.print td.name
for each fld in td.fields
debug.print vbtab; fld.name
next
next
set db = nothing

Check out the Relationship object for information about the
relationships. You can easily get the name of the next relationship,
the fields and tables comprising it, whether it is enforced or not, and
so on.

HTH,
TC [MVP Access]