I've got a database that's been in use for 10 years and there's ~ 40 copies of it out there now. I have dtmInitialAppearanceDate in the database already and have it stored as short date in the column definition. Some newer customers are asking that I now also store the initial appearance time. As I see it, I have two choices:
1. Use VBA to issue an alter table from the front end to the back end to change the format for dtmInitialAppearanceDate from short date to general date and include the date and time in one column.
2. Use VBA to add a new column dtmInitialAppearanceTime and set its format to only include the time portion.
Any modifications to the back end from the front end is going to need to be done via VBA and there is a non-unique index on the field already which may make doing an alter table harder?
Any recommendations? Storage space isn't an issue yet. Largest customer database is only up to 76MB. If I add a new column, existing queries can be left alone and I can just modify the ones that need to account for the time. What other things should I consider when weighing the decision? If the decision is to go with #1, then I'll need help with the best way to alter the table definition.
Thanks
Lution
1. Use VBA to issue an alter table from the front end to the back end to change the format for dtmInitialAppearanceDate from short date to general date and include the date and time in one column.
2. Use VBA to add a new column dtmInitialAppearanceTime and set its format to only include the time portion.
Any modifications to the back end from the front end is going to need to be done via VBA and there is a non-unique index on the field already which may make doing an alter table harder?
Any recommendations? Storage space isn't an issue yet. Largest customer database is only up to 76MB. If I add a new column, existing queries can be left alone and I can just modify the ones that need to account for the time. What other things should I consider when weighing the decision? If the decision is to go with #1, then I'll need help with the best way to alter the table definition.
Thanks
Lution