Updating the definition of a live MS Access Database

Posted on 2008-06-23
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?

Question by:PantoffelSlippers
  • 4
  • 3
  • 2
  • +3
LVL 19

Accepted Solution

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.
LVL 19

Expert Comment

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.
LVL 77

Assisted Solution

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.

Author Comment

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?

LVL 77

Assisted Solution

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

LVL 15

Assisted Solution

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



    CheckVersion = True

End If


   Exit Function


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

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

    Resume exit_handler

End Function

Open in new window

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21859890

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.


Author Comment

ID: 21862741

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!
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21862902

Thanks for the clarification.

LVL 19

Expert Comment

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.
LVL 11

Expert Comment

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

LVL 11

Expert Comment

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

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"

Author Closing Comment

ID: 31470051

Author Comment

ID: 21995818
Thank you Experts

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 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

16 Experts available now in Live!

Get 1:1 Help Now