Solved

Remote/Offsite Access upgrades

Posted on 2014-09-06
12
232 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 81

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 81

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 81

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

738 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