Updating relationships

[Les Isaacs]

Hi All

I have a complex access2010 accdb in which the key field in the 3 main tables are text fields (e.g. staff_name). I understand that numerical keys are preferable (quite apart from the problem of needing to have two John Smiths!), so I intend to add new numerical keys - probably with data type 'number' and field size 'long' (unless anyone suggests otherwise).

Does anyone know of any tools that are useful for any of this - i.e. resetting the relationships between the tables, resetting the joins in queries and in the sql for datasets, etc? Otherswise it's going to be a long job!

Thanks for any help.
Les

 

[CJ_London]

just open the table in design view and add a primary key field as autonumber.

then in each child table, also add another family key (or foreign key if you prefer) which will be of type long.

To populate this family key you will need to run an update query - something along the lines of

Update tblFamily INNER JOIN tblParent ON tblFamily.staffname=tblParent.staffname
SET tblFamily.ParentFK=tblParent.StaffPK

 

[Les Isaacs]

Hi CJ_London

I realize I could do some of what needs doing manually, but there are a lot of tables involved so I was hoping for a tool to help me! It's the joins that is going to take most of the time.

Thanks
Les

 

[CJ_London]

no tool available so far as I know

you could write something in vba to loop through all the tables to dynamically create queries to add the extra fields and modify the relationships

take a look at this link for the type of queries required

http://www.w3schools.com/sql/sql_foreignkey.asp