Solved

Updating the definition of a live MS Access Database

Posted on 2008-06-23
14
281 Views
Last Modified: 2013-11-29
Hi Experts,

I'm facing an awkward MS Access problem.

I developed an Access database that I will now deploy to a shared folder.  About 10 users will have access to it.  My problem is that no machines in the production environment have Access installed which means that I will make use of the Access run-time.  (Thanks MS!!!).  The problem comes in here:  the defintion of the database will still need to change over the next few weeks or even months meaning new tables, queries, forms and reports have to be added still over time.

I will have to make these changes on my development machine off-site and then apply changes.  Obviously I cannot just replace the AccDB file on the production machine because it contains live data.  I also cannot make changes to the live database because there is no Access installation allowed and I will only be there a few hours each month.

Does MS Access 2007 provide a way in which I can transfer the changes in defition from my database to the live one?

Thanks
0
Comment
Question by:PantoffelSlippers
  • 4
  • 3
  • 2
  • +3
14 Comments
 
LVL 19

Accepted Solution

by:
frankytee earned 100 total points
ID: 21852919
you need to split your access db into 2 seperate access dbs.
1st one is your back end data file (tables)
2nd is your front end (code, queries, reports, modules etc)

your front end connects to your back end via "linked tables". select the "tables" tab, then right mouse click the db window and link to your back end.
you develop the 2nd file (front end) with the new reports, code etc and after you test it with your own test version of the back end db, you then rollout to production and connect it to the production back end db.
0
 
LVL 19

Expert Comment

by:frankytee
ID: 21852925
so using above, you replace the production FRONT END file with your latest version of the FRONT END after UAT is successful. the only time the back end file gets updated is when you need to add new tables, new fields etc. you can code this as well or if its only small changes you just manually modify the back end file when required.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 100 total points
ID: 21852927
You should deploy a multi-user system by splitting the database using the tools provided, (database splitter and link tables manager) and then giving each user their own copy of the front-end file.  Doing this reduces the risk of database corruption.  It also means that you can issue a new front-end database without having to be concerned about the backend tables.

Of course, if you have to change the tables you really have no option but to close the system down while you get all the data into the new file.  Therefore you should try to complete all your table changes as early as possible so that you don't have to go through this more than necessary.
0
 

Author Comment

by:PantoffelSlippers
ID: 21853100
Thank you experts!

I never thought of that!  It gives me something to play with for a while!

Peter57r:  would you mind elaborating a little bit on your comment regarding the multi-user interface, database splitter and tables manager.  Also, if I give each user a local copy of the front-end db, how would I redistribute updated copies? Would this have to be done manually each time?

Thanks
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 100 total points
ID: 21853575
Descriptions of the Database Splitter and LinkedTables Manager can be found in Help.

You should put the current file in the folder where you want to keep the backend tables and then do the split.  That should link the front-end into the backend using the correct path, and you can then copy the front-end anywhere that can see the backend machine.

Yes you have to re-issue the front-end for each new version.  There are several free tools on the net which allow you to place a single updated copy in a central shared folder and automatically distribute it.  (Of course, initially you have to install that software on the user's machine to allow this work).
This is just one...
http://www.granite.ab.ca/Access/autofe.htm

0
 
LVL 15

Assisted Solution

by:cquinn
cquinn earned 50 total points
ID: 21855475
I re-issue front ends with a link to a batch file sent by email.

To stop users continuing to work with outdated front ends I hold a version number in a table in the back end, and hold the front end version in a constant in a module.  In the autoexec macro of the frontend I compare the version held in the front end with the one in the back end and stop the system if they don't match.  When sending out the new front end I change the version number in the back end, then send an email with a link to a batch file

The batch file is like this - adjust it to match your paths, servers etc,:

@Echo off
Echo Now Installing Database v2.05
if Exist "C:\Access Systems\Database.ade" copy "\\Server\Share\Access Systems\Database\v2.05\Database.ade" "C:\Access Systems\Database.ade" >nul:
Echo v2.05 Installation Completed
pause
Public Const CurrentVersion = "5.35t"
 

Public Function CheckVersion() As Boolean
 

On Error GoTo err_handler

Dim dbVer As String

dbVer = Nz(DLookup("CurrentVersion", "tblSystemData"), "")

If dbVer <> CurrentVersion Then

    MsgBox "Invalid Program Version - please check your email or contact Support" & vbCrLf & "FE v " & CurrentVersion & vbCrLf & "BE v " & dbVer, vbCritical + vbOKOnly, "Invalid Version"

    Application.Quit

Else

    CheckVersion = True

End If
 
 

exit_handler:

   Exit Function
 

err_handler:

    MsgBox "(" & Err.Number & ")" _

           & vbNewLine & vbNewLine & Err.Description, vbOKOnly + vbInformation, "Version Number Error"

    Resume exit_handler

End Function

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21859890
PantoffelSlippers,

Another line of reasoning would ask why you are even "Deploying" your Application when you know there will be many more design changes?

JeffCoachman
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:PantoffelSlippers
ID: 21862741
JeffCoachman,

A valid point that you make.  Why deploy something that is not finished?

This application (AccessDB) is part of a larger system that is implemented over several phases.  Users need this database to perform their duties now.  In several months time, the same users will have more / different duties which will require changes to the database.  They need to process all the data with their first duties before they can start with the 2nd set of duties.

My Access DB is really a very small part of this system.  Almost not even important at all.  So, I wish to keep it as simple as possible.  I dont want to keep 5 different Access DB's for 5 different phases / functions.  This is also almost a sideline project for me.  One of those small things that you have been tasked with that is not really important.

I really don't want this little sideline project to become one of those little projects that gets way out of hand, out of scope and ends up costing you double the amount of time that you intended.  We ll had those!

Just keep it simple!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21862902
PantoffelSlippers,

Thanks for the clarification.
;-)

Jeff
0
 
LVL 19

Expert Comment

by:frankytee
ID: 21863924
> I dont want to keep 5 different Access DB's for 5 different phases / functions.  
this would be a matter of version control and making your access db "scalable".  design your tables/code with the next phase in mind (add whatever additional fields, tables etc) . when going through your phases avoid hardcoding and place those parameters in tables etc.  
it may take you longer to develop initially but it means less headache moving forward as when the next phase comes in it minimises/eliminates redesign and recoding.
0
 
LVL 11

Expert Comment

by:RgGray3
ID: 21873796
Another way to maintain an "application under development"  (of course AFTER you do split it) is to Replicate the Back end MDB and distribute the front end.

Replication will allow you to make changes to your back end by making them in the design MASTER of the MDB
you would then replicate the changes to the working data when you have finished testing

You distribute the front end by copying the finished production  FE to a distribution point on the network and either informing the users to copy down (or give them a short cut to do so) the new FE
or if can automate it in a number of ways...

The easiest way would be to have a table in your FE with the version number as the only record
(assuming that you have an orderly network environment and everyone links to your data using the same network path)

you would then link to the distribution copy of the FE  version number table and simply compare the two version numbers
if the version on the distribution point is larger than the local one....    MsgBox "A new version has been posted" ....  yadda yadda


Rich
0
 
LVL 11

Expert Comment

by:RgGray3
ID: 21873830
Mea Culpa....
Just reread the question and realized that you are working in 2007....
Sorry... MS has decided that replication is not of any real value..... and removed it from Access

HOWEVER...
If you have an earlier version of Access (for the developer only...)  you can create your replica set for the back end...  and manage the data in the older version of access while developing the FE in 2007......    

If that is not a possibility ....  sorry....
But if possible...  Replication is the easiest way to make, test and deploy data structure changes in a "work in progress"
0
 

Author Closing Comment

by:PantoffelSlippers
ID: 31470051
Thanks
0
 

Author Comment

by:PantoffelSlippers
ID: 21995818
Thank you Experts
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

707 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

12 Experts available now in Live!

Get 1:1 Help Now