Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Updating the definition of a live MS Access Database

Posted on 2008-06-23
Medium Priority
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
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
  • 4
  • 3
  • 2
  • +3
LVL 19

Accepted Solution

frankytee earned 400 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 400 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.
Independent Software Vendors: 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!


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 400 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 200 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?


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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

609 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