Twilight Zone part I

[The_Doc_Man]

Access 97 context.

I am in the process of reworking the relationships of my database, from using a 30-byte name as primary key to using an (auto-) number as primary key. Shorter keys, smaller foreign keys, smaller tables overall. So I've gone through the list of my relationships. Went into the analyzer. Selected the relationships object. Dumped it to my printer. Visited all references to the name field. All tables were updated to include a new field for the item number.

I populated all item numbers in the associated tables and tried to convert the relationships from the text key as primary to the number key as primary.

But when I open the table exclusively, I cannot remove the primary key. It says I have a relationship that must be removed before I can do that. Opening the relationships window, I look around but there is nothing there. No lines lead to the field in the relationships view. No entries name the offending field when I use the table documenter.

Has anyone seen this?

 

[pcs]

doc_man

this happened to me some time ago.

as i recall, i had to import everything into a new db.

something was awry with the db container itself...

hth,
al

 

[KevinM]

Can you see the tables in the rel window?

I have come across something similar and had to add the tables in the rel win and the old links appear.

I think this occurs if you use the Combo LookUp Wiz when you create a rel in the table design view.

 

[The_Doc_Man]

To delete a relationship, DO NOT delete the tables. You need to delete the relationship line.

Actually, that is what I did, Pat. Clicked on the line, deleted it, drew the new relationship between the number fields.

 

[The_Doc_Man]

Will any of the queries defined using relationships based on the old text key (which will still be "indexed, no dups") get in the way? I'll be retrofitting those queries as I go.

I don't see any reference to them in mSysRelationships, so I don't think so. Just double-checking.

 

[The_Doc_Man]

Thanks, Pat. Worked just like you suggested. (But I knew it would...)

Despite my years of database experience, would you believe this is the first time I've ever tried to change keys like this. Normally I choose pretty good keys and keep things optimized. However, this was a special case.

I'm in the process of reworking someone else's database that I've inherited. While my predecessor did some nice work, some of his choices were ... shall we say, less than optimal.

In this particular case, once I can complete all the changes, I will be able to save a net of 26 bytes per record in some child tables. Literally THOUSANDS of records involved, so it is a worth-while change, if I can just live through it without finding my predecessor and wringing his scrawny neck for some of the things he left for me to clean up. (Now you know where I get my other title... Sr. Systems Janitor.)