The most common way to avoid this sort of problem is by using a split front end-back end database. The tables reside in the back end, and everything else--queries, forms, reports, and modules--in the front end. You link the back end tables into the front end, using File|Get External Data|Link Tables, and after that the front end has access almost exactly as if the tables were really defined there. Your users open the front end database, and basically ignore the back end.
The majority of functional enhancements don't require table changes, so this makes maintenance quite easy. To distribute enhancements, you merely distribute the front end database, replacing the queries, forms, reports, and code but without replacing the tables. (In some cases, the links in the front end database may need to be recreated or refreshed, which can be done manually or with VBA code; it only needs to be done once.)
In your case, you made table changes as well. Usually, enhancements which depend on table changes involve adding tables or columns, or sometimes enlarging existing columns. (Deleting columns, changing a column datatype, and creating or changing relationships are only rarely required.) If you only need to add tables, columns and relationships, this is easily done manually in the target table, and takes little time. It can even be done in one-time upgrade code, though that takes more effort so it's only worth doing if there are many legacy back end databases to be modified.
There is also a valuable characteristic with this method, namely, that the old front end database will usually continue to work with the upgraded back end, at least until the new front end is implemented. Thus, you can take your time upgrading the back end tables in preparation, then roll out the new front end database to everybody at the same time. That last step is easy, since all it involves is replacing the old front end with the new one.
The front end-back end split database model, then, is a good solution for these kinds of enhancements.
If you have to change column sizes or datatypes, it takes a little more care, because the old front end database probably won't be compatible with the revised tables. You can still use the split database model, but you have to ensure that the changes to the back end are coordinated with replacing the front end. Generally, you'll either have to manually upgrade each user individually, or provide upgrade code that runs automatically the first time the new front end is run. Though it's more work, you may still prefer this to copying the old table data into the new database.
BTW, although it doesn't sound as if this is a multi-user database, using a split database model is a big improvement for those. You leave the shared back end on the network, and distribute the front end onto users' machines. Because the queries, forms, reports, and code are then local to the user's machine, the application runs faster than if you load all these from the database on the network.
If you think a split database model might be worth investigating, check the help files. The actual act of splitting can be done easily with the Database Splitter utility on the Tools|Database Utilities menu. Rick Sprague
