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.
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.


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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

764 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