Solved

Remote/Offsite Access upgrades

Posted on 2014-09-06
12
226 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 78

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 78

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
 
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 78

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Backup Your Microsoft Windows Server®

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

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now