MS Access: How to chnge a field length? (From very experienced user)
[AHU Key] field is present in 38 tables.
Its size is 15
There are 56 relationships among the above tables.
Purpose: To change size to, say 50.
Why is this question here? Please see the attached image.
Finally the question itself:
Can anybody suggest a safe method of changing lengths of [AHU Key] in all 38 tables?
If anybody doesn't acknowledge what it is about, then please do not bother. Also please do not reply with trivialities like "delete all relationships and then restore them."
P.S. Live situation is about 4 times more complex (plus [CHi Key], [Hyd Key], [Met Key]).
-.jpg
Its size is 15
There are 56 relationships among the above tables.
Purpose: To change size to, say 50.
Why is this question here? Please see the attached image.
Finally the question itself:
Can anybody suggest a safe method of changing lengths of [AHU Key] in all 38 tables?
If anybody doesn't acknowledge what it is about, then please do not bother. Also please do not reply with trivialities like "delete all relationships and then restore them."
P.S. Live situation is about 4 times more complex (plus [CHi Key], [Hyd Key], [Met Key]).
-.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
aikimark
I would explore importing the data from the tables into a new database, changing the key lengths and then importing the table relationships.
ASKER
Both comments are interesting.
Too busy though. I'll be back tomorrow(?).
The key word in my question is "Safely", meaning "guaranteed without loss of 'cascades', attributes and what not."
Yes, I know also: "Just concentrate".
Too busy though. I'll be back tomorrow(?).
The key word in my question is "Safely", meaning "guaranteed without loss of 'cascades', attributes and what not."
Yes, I know also: "Just concentrate".
I'm with LSM
I 'm not sure how you defined:
""delete all relationships and then restore them.""
...as "trivial".
You asked for "Safe":
<Can anybody suggest a safe method of changing lengths of [AHU Key] in all 38 tables?>
So by your own definition, the most Safe method is also the most trivial..
;-)
Any "automated" system to do this would have to be fairly sophisticated, and include things like iron-clad error handling and RollBacks in case anything were to go wrong.
JeffCoachman
I 'm not sure how you defined:
""delete all relationships and then restore them.""
...as "trivial".
You asked for "Safe":
<Can anybody suggest a safe method of changing lengths of [AHU Key] in all 38 tables?>
So by your own definition, the most Safe method is also the most trivial..
;-)
Any "automated" system to do this would have to be fairly sophisticated, and include things like iron-clad error handling and RollBacks in case anything were to go wrong.
JeffCoachman
ASKER
I am not against delete and restore (although I’d like to be). I am just looking to how to do it safely, i.e. programmatically, fool-proof with a ready-to-use tool about which I should have known but I don't. Otherwise "It has been working for ages, and now it does not" complaints ensue. And the user does not apparently care whether you ever missed anything in course of "delete -- restore".
E.g. a conceivable answer (I know, it's just a dream) might be: "Use <some utility> to export all relationsips to a file, then delete them – that’s easy, – then change all field sizes you want, and then use the same utility to restore the relationships."
I'd like my "thank you" answer in this case to include "Wow! I did not know about this tool! Shame on me!"
>>...to be fairly sophisticated...
So be it. Otherwise I would not have put this question here.
E.g. a conceivable answer (I know, it's just a dream) might be: "Use <some utility> to export all relationsips to a file, then delete them – that’s easy, – then change all field sizes you want, and then use the same utility to restore the relationships."
I'd like my "thank you" answer in this case to include "Wow! I did not know about this tool! Shame on me!"
>>...to be fairly sophisticated...
So be it. Otherwise I would not have put this question here.
If my suggestion works manually, then we can automate that. You only need to test it with a subset of the relationships
I don't know of any tool that will do this, but aikimark's suggestion sounds like the easiest thing to try. Note the link I provided earlier shows how to work with relationships between databases, so that should give you a good head start on the VBA.
<but aikimark's suggestion sounds like the easiest thing to try. >
Yep, ...let's see what the big guy has to offer.
;-)
My feeling is that this sounded like a One-shot deal.
<Can anybody suggest a safe method of changing lengths of [AHU Key] in all 38 tables?>
...If so, then you could have probably done this already (Remove-->Edit-->Restore) manually.
Jeff
Yep, ...let's see what the big guy has to offer.
;-)
My feeling is that this sounded like a One-shot deal.
<Can anybody suggest a safe method of changing lengths of [AHU Key] in all 38 tables?>
...If so, then you could have probably done this already (Remove-->Edit-->Restore) manually.
Jeff
thanks for the vote of confidence? ;-)
When you change a field size in the GUI, it actually does an Alter Table (tabledef equivalent), adding a new column, updates the table, copying the current column data into the new column, deletes the current column and then renames the new column as the current column.
When you change a field size in the GUI, it actually does an Alter Table (tabledef equivalent), adding a new column, updates the table, copying the current column data into the new column, deletes the current column and then renames the new column as the current column.
ASKER
Thanks a lot, LSMConsulting, for your 38267522. It's what I meant in my original question and in 38272810.
"Wow! I did not know about this article! Shame on me!"
Let our users give their equipment unit names like: "Air Handler on the third floor" rather than "AHU3rd". They insist.
It's good to be lazy!
ASKER
...they insist...
and are warned.
and are warned.