Remote/Offsite Access upgrades

Have a question regarding an Access upgrade that would be coded offsite while the database remains in use.
Hopefully I have explained adequately!

- Database is split into front end and back end - code and data
- It will otherwise be business as usual during coding, form and database upgrades
- Mix of Access 2003 and 2010 clients

After development, I will need to upgrade the Access database front end and back end for the upgrades which will involve minor changes to the database schema as well as new coding and form changes. My question involves how to best integrate this back into the client's workflow (mainly as it related to data integrity since the code is all in the font end).

I know there will need to be provision made for "old data" that now has new fields to reference (will set defaults and populate - or filter from results) but how will it be best to ensure the customer's data remains intact and accurate? Will I need to carefully mirror the database changes made during development when onsite with the customer's "current" data (can be offline for that time) or is there a better way? It seems potentially "troublesome" to use the new table structures and import data from the "live" data set but this is the only other way I can think of.

So, in summary - what is best?
1) Modify database structure to mirror development changes when onsite on existing back end
2) Use new database back end and import all table data when onsite from the previous back end data
3) Hopefully - a better option??

Please advise according to experiences and not (just) a link to an overly long, related article.
Appreciate it.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
you should have your own private test database which can be a copy of the 'live' database under a different sql server. This way you can test and roll back until all the quirks are worked out.  You should never be using the live database for development EVER!
DanielTAuthor Commented:
Yep - know that about the "live database".

My comments would be applicable to after development is done and the changes would then need to be integrated onsite.

This is a small client - no SQL server here.
Pure Access with database split into front and back ends, shared with only 2 or 3 users.
David Johnson, CD, MVPOwnerCommented:
safest way is to import the data after the tables are created.. how are you going to handle data with nulls in the data after the changes? Perhaps you should use new tables for the added information with links to the older data.  you may have to run a cleanup data function to get the data correct.. or how are you going to deal with records that don't have the new fields populated?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DanielTAuthor Commented:
Thanks David,

Understood. Good question.
The changes made will allow setting defaults on prior records there will be no null values. At minimum the values will be set to "unknown" (string/text fields) or to a specific staff member (the manager) so I will assign those to prior data by using an update query at implementation.

Seems a little messy.
Was hoping there was a better way.

Would SQL not have similar issues? You'd still need to deal with structure differences and how that affects views/queries, forms etc, right?
David Johnson, CD, MVPOwnerCommented:
it is all a relational database.. I'm a generalist in this area and not a specialist. Perhaps an Access Guru will chime in.. try a request for attention and widen the scope a bit.
DanielTAuthor Commented:
Thanks for feedback; we'll see where it goes from here!
Will leave it a bit for other input - not a rush (yet)
The way I handle this situation (and I do it quite often) is to create a script to handle the back-end changes. For examples of this, please see Then I can test these back-end changes on a copy of the database to make sure the script runs OK. Next I sign on remotely to the end user's network (I usually do this kind of thing remotely), and I trigger a shutdown of the application on all of the client computers. I do this with our free Force Shut Down utility at Once the backend db is free I transfer the new front end to the client's computer, save it to the "master" folder, and open it. As part of the startup routine, the tables are relinked (see our TableLinker Lite utility - and the backend update script runs. then I compact and repair this new "master" file in the "master" location on the client's network and un-trigger the Force Shut Down utility so that users can sign on again.
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
If your structure changes are relatively minor, I agree with pdebaets that scripting the database changes is the most reliable and testable way to go.  If you only have a few structure changes, you can even forego the script and just carefully follow a checklist of changes once you're ready to apply them to production (after a backup!).

If your structure changes are major (populating new tables, moving data from one table to another - basically anything where you'll need update or append queries to set everything right) then you're talking about a data conversion, not just a simple structure change.  In that case I agree with David Johnson - you need a repeatable, testable conversion routine.  For this we create a conversion FE that links to both the old and new BE databases.  We have a conversion macro (yes, a macro - it's one of the two places we use them) that runs a series of queries to migrate all the data from the old BE to the new BE.

Hope this helps,
Armen Stein, Access MVP

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DanielTAuthor Commented:
Scripting makes sense, actually although my head was there on this one! It may be that I will do the onsite (or remote) changes manually with the system offline but I do like the scripting idea.

Do you have any quick samples of this coding/scripting?

This will only be adding a few fields to a couple of tables but there will be new tables added that will be linked by FK's to main data table. That and then setting some appropriate default values for data already entered prior to structure changes. (est 3-4K records).
For some examples of back-end schema change scripting, please see
DanielTAuthor Commented:
Oops! Sorry about that.
I see you already posted the link so my question was redundant.

DanielTAuthor Commented:
Awesome answers!
Thanks to all who assisted.

pdebaets gave a bit more detail which was appreciated and useful but ArmenStein's answer will likely be most applicable in this particular case for "carefully follow a checklist of changes". Have assigned points and best answer accordingly.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Legacy OS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.