Change an access Primary key with relationships

Question

I have a database in access 2007 accdb extension , there are more or less 30-40 tables with related primary key "local_number". it is a text primary key with 10 lenght.

How can I change the length of this primary key to 30 WITHOUT delete previosly all the 30 relationship.

1. A similar question. I need add a compose primary key to my PK "local_number". I need Add a "Date" as composed PK. Then access just allow this if I first delete all relationships.

How can Avoid this warning and change my PK ignoring this message.

Reason of compose PK in each table is requirement of a historical data change in records.

update, Someone know if this is possible or i hace to delete all relationships?

Answer 1

you can use access database object model and VBA code that will automatically

1. add new fields in corresponding tables (both PKs and FKs)
2. Based on the already existing relations, update all FK values with corresponding PK values,
3. Break the existing relations
4. Create the new relations

To know which relations have to be replaced, you can browse your database objects.

This code could inspire you (written on the fly/may be buggy/check corresponding help):

for i = 0 to mydatabase.Relations.count - 1
    Debug.Print mydatabase.relations(i).name
    for j = 0 to myDatabase.relations(i).fields.count -1
        debug.print myDatabase.relations(i).fields(j).name
    next j
next i

Answer 2

1. Make a copy of the database with existing relationships.

2. Delete all the relationships in the original copy.

3. Then use code to copy the relationships from the backup to the original copy.

I'm assuming you retain the same name for the PK field that's being edited, but even so, you can alter the code from the MS Knowledge Base at the cited URL to account for that.