Solved

Remote/Offsite Access upgrades

Posted on 2014-09-06
12
231 Views
Last Modified: 2014-09-07
Have a question regarding an Access upgrade that would be coded offsite while the database remains in use.
Hopefully I have explained adequately!

Scenario
- 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.

Thanks
0
Comment
Question by:DanielT
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 80

Expert Comment

by:David Johnson, CD, MVP
ID: 40307693
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!
0
 
LVL 2

Author Comment

by:DanielT
ID: 40307709
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.
0
 
LVL 80

Expert Comment

by:David Johnson, CD, MVP
ID: 40307736
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?
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 2

Author Comment

by:DanielT
ID: 40307751
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?
0
 
LVL 80

Expert Comment

by:David Johnson, CD, MVP
ID: 40307758
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.
0
 
LVL 2

Author Comment

by:DanielT
ID: 40307770
Thanks for feedback; we'll see where it goes from here!
Will leave it a bit for other input - not a rush (yet)
0
 
LVL 12

Assisted Solution

by:pdebaets
pdebaets earned 300 total points
ID: 40308253
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 http://www.peterssoftware.com/beu.htm. 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 http://www.peterssoftware.com/fsd.htm. 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 - http://www.peterssoftware.com/tll.htm) 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.
0
 
LVL 9

Accepted Solution

by:
Armen Stein - Microsoft Access MVP since 2006 earned 200 total points
ID: 40308629
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
0
 
LVL 2

Author Comment

by:DanielT
ID: 40308767
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).
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 40308778
For some examples of back-end schema change scripting, please see http://www.peterssoftware.com/beu.htm.
0
 
LVL 2

Author Comment

by:DanielT
ID: 40308793
Oops! Sorry about that.
I see you already posted the link so my question was redundant.

Thanks!
0
 
LVL 2

Author Closing Comment

by:DanielT
ID: 40308802
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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question