• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

Updating the definition of a live MS Access Database

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?

  • 4
  • 3
  • 2
  • +3
4 Solutions
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.
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.
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.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

PantoffelSlippersAuthor Commented:
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?

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

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

Jeffrey CoachmanMIS LiasonCommented:

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

PantoffelSlippersAuthor Commented:

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!
Jeffrey CoachmanMIS LiasonCommented:

Thanks for the clarification.

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

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"
PantoffelSlippersAuthor Commented:
PantoffelSlippersAuthor Commented:
Thank you Experts
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now