Microsoft Access - Talking to the same database table from different geographical locations

Posted on 2009-02-19
Last Modified: 2013-12-05
Hi Experts,

I have created database with main form as attached, to be installed at the company headquarters.  However, in a different part of the company (geographically displaced ), I need them to be able to use and input only part of the form in red square and not be able to access (see) other part of the form/database. Since the job number is both the autonumber and the key field for main table, the people in headquarters obviously need to be able to see others input and they both need to talk to the same main table.  Questions are as follows:

1.  How do I get people on different geographical locations to speak to same database table?

2.  Should I create another database with only that part of the form displayed for people that are not atthe headquarters (in which case how do I get two databases to talk to each other), or should I use same database, but disable access to buttons etc (in which case what is the best way?)

P.S.  It is obviously very important that they both speak to the same Main table as the Job Number is both AutoNumber and key field.

Many thanks

Question by:LillyC
    LVL 77

    Expert Comment

    I see you have posted this into the ADP zone.

    Please confirm that you are using an ADP and not an MDB file for your frontend.

    Author Comment


    Sorry, that was a mistake, it is a normal MDB file.  

    LVL 77

    Accepted Solution

    For multi-user applications you have to split the database into front-end and back-end.  The back-end contains all the tables and the front-end contains everything else.
    You use the Database Splitter tool on the Tools menu to do this.
    Each user has their own copy of the front-end.  
    So you could produce two versions of the front-end which used a different startup form..

    I am just a little concerned about your phrase...'geographically displaced '

    All users must be on the same LAN to be able to use the same Access application.  You cannot connect a front-end to a back-end across the internet.  It's just not fast enough.  The user must be able to 'see' the backend file in Windows Explorer on their machine.  You can use Windows Terminal Services or an equivalent to connect remote users but obviously you must have a suitable server for that.

    Author Comment

    Hi Expert,

    Thank you very much, extremely useful.  I am not 100% sure what the company has, but I know two offices are some 180 miles apart. I will check with their IT technician, but I believe they have same LAN connected with wireless link.  So, it should work.  Just a few points of clarification, as I have not done this before:

    1.       When you say produce two versions of the front-end which used a different startup form., are you suggesting that I first split my original database as it is, save results, and then change the main form by disabling the buttons that other user should not access and split again?  Defacto, ending up with one back-end of tables and two different front-ends?  Or would it be better to redesign the main form and delete all other buttons and sections?

    Please see splitdbase image attached and confirm that is the tool that you are talking about?  Is it straightforward to use?

    2.      Is it at all possible to somehow simulate this situation on a single laptop, so that I can test it? If not, I have two laptops that are both connected to Internet via wireless network connection.  Would it be possible to simulate it with two of them?

    3.      Finally, please see publish image attached.  Am I suppose to use this document management server and package and sign features at all?  What exactly are each of them for?

    So sorry for many questions, I am very grateful.  Answer of these should be the end of it.

    Many thanks


    LVL 77

    Expert Comment

    1 There's no need to split twice.

    Once the app is split you can copy the frontend and modify the copy to create two versions.
    You need to look at the Linked Tables Manager tool as well. You will use this to link  to a 'test' backend and to set the links to the production backend when you release a new version of the frontend.

    3  Maybe not.  One involves linking your database to a Sharepoint site and the other is about applying digital signatures to code.  These are more policy issues for your company rather than technical questions.

    2 I'm afraid everything you include about your network config makes me feel very pessimistic about the chances of this application operating succesfully.  Access is more sensitive to network performance and reliabilty than most applications because there is a constant transfer of data occurring.  I would not recommend to anyone that they should run Access across a wireless link, although all developers will have somebody somewhere  for whom it works OK .  

    Apart from the wireless issue, there is no reason evident in this thread why you should not be able to test the app on your LAN on two laptops.
    I don't know why the internet would get involved here.  I sincerely hope it doesn't.

    But it sounds to me that you need a serious discussion with your network people to see exactly what you do have and whether what you are proposing is feasible.  Otherwise you risk telling your company you are going to implement something that actually cannot be achieved.


    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    746 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

    15 Experts available now in Live!

    Get 1:1 Help Now