Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-02-2016, 01:25 PM   #1
HadererDirndl
Newly Registered User
 
Join Date: Jan 2016
Location: Portland, Oregon, USA
Posts: 19
Thanks: 7
Thanked 0 Times in 0 Posts
HadererDirndl is on a distinguished road
"Release" and "deployment" best practices

A question for the experienced programmers on how to "release" updated versions of the same data base to the users frequently:

I am working on expanding the functionality of an existing Access data base (.accdb), which is being used on a daily basis by 1-5 users (never concurrently). I want to provide the new functionality roughly every two weeks. I never do the new development work on the data base instance that is being used; instead, I make a copy of it and work on it offline. Then, every two weeks, I delete all the tables from the "new" version, import all the tables from the "existing" version into the new version as applicable. This cannot be the best way to do this:
  1. I have to take the db offline while I do the imports, so nobody can do work that would then be lost. The unavailability of the data base is causing some productivity problems for my co-workers
  2. This seems a pretty risky way to do it, because there is a lot of manual exporting and importing involved
  3. One of my table has multi-value fields, which makes this extra difficult

I wonder if anyone has any "best practices" to share? Is there a better way to do this?

Thank you!

HadererDirndl is offline   Reply With Quote
Old 09-02-2016, 01:30 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,965
Thanks: 13
Thanked 4,055 Times in 3,991 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: "Release" and "deployment" best practices

Search on splitting the database. Here's one link:

http://allenbrowne.com/ser-01.html
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
HadererDirndl (09-02-2016)
Old 09-02-2016, 02:02 PM   #3
HadererDirndl
Newly Registered User
 
Join Date: Jan 2016
Location: Portland, Oregon, USA
Posts: 19
Thanks: 7
Thanked 0 Times in 0 Posts
HadererDirndl is on a distinguished road
Re: "Release" and "deployment" best practices

Thank you, I didn't realize that was what "splitting" deals with. I assume that wouldn't work with table schema changes though?

HadererDirndl is offline   Reply With Quote
Old 09-02-2016, 02:12 PM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,965
Thanks: 13
Thanked 4,055 Times in 3,991 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: "Release" and "deployment" best practices

Correct. Typically those are rare, and you normally need to get people out of the back end while you make the changes.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-03-2016, 07:30 AM   #5
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 975 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: "Release" and "deployment" best practices

Quote:
Originally Posted by pbaldy View Post
Correct. Typically those are rare, and you normally need to get people out of the back end while you make the changes.
We haven't had that sort of luck. Nearly every new version we deploy requires changes to the backend which we don't have direct access to. When we deploy a new version the AutoExec runs codes that:
  1. Checks a version number in the backend to see if the backend is up to date.
  2. If the backend needs updating it checks for a lock file and if none exists it updates the backend.

Maybe you don't need to do this since you have direct access to the backend but it would minimized the system down time. After a while you will have a collection of code that modifies the backend in various ways and this becomes less difficult.
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve

Last edited by sneuberg; 09-03-2016 at 07:59 AM.
sneuberg is offline   Reply With Quote
Old 09-03-2016, 07:48 AM   #6
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,965
Thanks: 13
Thanked 4,055 Times in 3,991 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: "Release" and "deployment" best practices

In my experience changes to the back end are not needed nearly as often as changes to the front. I do have clients that are remote. I typically send them a one-time db that makes the back end changes when they are required, rather than incorporate it into the front.

In-house I don't have the problem at all, since we use SQL Server. No need to get people out to make changes.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-03-2016, 08:08 AM   #7
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 975 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: "Release" and "deployment" best practices

Quote:
Originally Posted by pbaldy View Post
I do have clients that are remote. I typically send them a one-time db that makes the back end changes when they are required, rather than incorporate it into the front.
My sister wanted to do it that way but I argued that if we did it that way we could end up with clients using a frontend that wasn't compatible with the backend. With our clients you could count on that happening. You've never had any problems like this?

__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve

Last edited by sneuberg; 09-03-2016 at 08:15 AM.
sneuberg is offline   Reply With Quote
Old 09-03-2016, 08:39 AM   #8
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,965
Thanks: 13
Thanked 4,055 Times in 3,991 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: "Release" and "deployment" best practices

I haven't, but your way is probably more foolproof. I don't work with that many remote clients. Most of my work is for my employer, where I have complete control. I am surprised you have changes to the back end that often. Normally I'd suspect a denormalized database which needed new fields all the time, but I don't see you doing that.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-03-2016, 09:02 AM   #9
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 975 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: "Release" and "deployment" best practices

Quote:
Originally Posted by pbaldy View Post
I am surprised you have changes to the back end that often. Normally I'd suspect a denormalized database which needed new fields all the time, but I don't see you doing that.
The system is being developed incrementally which is probably a smart move by the client as they seem to have difficulty in expressing their requirements.
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
Old 09-03-2016, 02:14 PM   #10
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,105
Thanks: 81
Thanked 1,601 Times in 1,485 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: "Release" and "deployment" best practices

Quote:
we could end up with clients using a frontend that wasn't compatible with the backend.
I claim this as nothing special, but here is how my last big DB handled this case.

1. We had a BE/FE split.

2. The version number of the FE was stored in the startup form and we had all the usual things to prevent it coming up bypassing the startup form.

3. The BE technically didn't have a stored version - but we had a history table with a special flag that was set when we had a mandatory update for the FE. We had a query that came up with two results - the version number of the most recent entry in the history table - which was the current version; and the version number of the most recent entry in the history table for which the "mandatory update" flag was set - which was the oldest version of the FE that you could run.

4. Everything was fine if the FE's stored version matched the BE's table entry. We had rules on formation of the version / generation number so that you could have a FE version less than the most recent history entry but NOT less than the most recent "mandatory update" version entry.

As you might guess, if the BE had to change, we took down the BE and the FE to make the changes, then posted the new files and set up a "mandatory update" entry for that version number. On the other hand, if we had a FE-only change, we just broadcast a note to the users (and also displayed a warning banner on the opening form) to let them know that a new version was available.

Since our users had to be able to "see" the shared folder on our DB server, we just let them copy the newest FE when they got around to it, and we added some things to the opening menu's Form_Open routine to prevent it from allowing the FE to start up if you had an FE/BE mismatch OR if you were trying to directly open the shared copy (since of course it can ask the question CurrentDB.Name and get the fully qualified path of the file you just opened. If you opened the file from the shared folder, your session got tossed.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Tags
deploy , release , update , upgrade

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Input " Like A* " in Validation Rule Property but it shows " ALike "A*" " AccessPractice Tables 2 06-24-2016 05:37 AM
If "TableA.FieldA" exists in "TableB.FieldB" then return "1" else "2" shdale Queries 8 10-29-2014 05:20 AM
Expr1: Format("012P","0000") Why I get "0001" amolin General 4 10-10-2012 08:33 PM
Query to Convert "Name" field to "Last Name" "First Name" "Middle Name"? derekbeck Queries 5 03-26-2012 03:23 PM
Qry To Exclude Record If All Fields On Page Is Blank Except Field "xx", "yy" And "zzz vancey Queries 0 03-17-2011 01:43 AM




All times are GMT -8. The time now is 10:45 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World