Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2017

    Unanswered: Access Project - Solution Advice/Puzzle

    Hi Everyone,

    First of all I would like to introduce myself as this is my first post! I'm J a Business Intelligence Analyst who primarily works on Access and general VB project work.

    So right to business. We have a new client at work who would like there telephony agents to record key information about customers while they are on the phone. Now we've done so many of these I've lost count, this is the model below:

    Standard Solution Model:
    We use Microsoft Access 2010 and split the database creating a ACCDE on a network drive for everyone to access while the back end is in another folder on the same network drive.

    Now the standard model for many projects is absolutely fine up to around 40/50 people but the new clients operation has a 100 users which I feel would make the Access app run extremely slow and open up a whole host of potential data corruption problems etc. Now due to reasons I don't even know this solution has to be access front end, which is fine as most of the work will be in the back end. I tried requesting SQL server solution as the back end which would solve all of my problems but this has been denied, great! This particular job is a open up the storage room and see what we have lying around deal with a 0 budget.

    So what do I have available:
    • Microsoft Access 2010 with unlimited licenses
    • A shared network drive which I can use for the original model or another solution
    • A spare desktop which I can use for whatever I need it for. (Used to run a task scheduler on it)
    • I'm allowed to install any free software on IT approval such as SQL Express,MySQL etc.

    Now I know this must be such a random question to ask but based on what I've explained and what my requirement is vs what I have available to use I don't suppose anyone has any creative solutions. I did have an idea that I could use the spare desktop as a dedicated server and host my back end there but I'm more of a front end developer with little experience so don't even know if what I'm saying is feasible.

  2. #2
    Join Date
    Aug 2017

    Individual local "copies"

    My view is that splitting the database into front-end back-end is to allow users to load exclusively the front end application.
    For several of my clients I deploy the application to the user's personal drive or network folder.
    A desktop short cut executes a batch command that copies from the network the source application file to the user's \\folder . For the batch file to work, each user must have the same path to a personal folder. So it could be on the C drive or on what ever virtual network drive (e.g. U). The virtue of this setup is that I can upload to the source application file folder updated application files without having to update each user's system. The batch file always copies from the same location. The only downside is the constant copy/overwrite, and the extra storage required. Since the application never stores anything of permanence, this works without problem.
    With 100+ users sharing a backend however, your application needs to be able to handle potential record contensions - another topic for another thread

  3. #3
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    With that many users, the question is not, "What is the impact if the database corrupts?", but rather, "What is the impact when the database corrupts?"

    We have an application written in Access 2000. While not mission critical, is important enough to send the Finance Manager scurrying to the IT Director when it crashes, locks, disappears or is otherwise unavailable without warning. After nearly 20 years, it's scheduled to be replaced! If this project is mission critical for you, I would go back to the bean-counters and ask how much it will cost for them to be without it for (*reaches into the air*) half a day every couple of weeks. Then compare that annual cost to a couple* of SQL Server licences, and see which is lower. I'm guessing that it will be the SQL licences.

    I also second Neil's point about local copies. We've used this approach for a couple of different requirements, one of which was to allow the whole IT department to log our time, hour by hour. Because we were only creating records, and occasionally editing our own timesheets, there was no need to worry about record locks or concurrent updates. I don't think that this will help you a great deal, given the description of the work above. You'd almost need to have the desktop copies just holding forms, user-specific data and empty tables, and then write processes to copy records down from the network as required (e.g. pulling down a customer record), while putting a lock on the network file's record. Before long, you'll have created a mainframe/dumb terminal setup in Access, which is really reinventing the wheel!

    You'll need two (at least) so that you can mirror the production server for DR. They might spring for three (I have no idea how licence costs are calculated, other than Oracle's ["How much have you got?"], but sometimes bundles cost less than singles), in which case you can have a development server as well.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  4. #4
    Join Date
    Jun 2007
    Maitland NSW,Australia
    You put the back end on your network drive. And you put your front end on your user's computer with the front end linked to back end on the server. You do not put a front end on a network folder as this slows the network traffic when several users are accessing the same front end. Also if you temp tables to store some data then these temp tables should be in the front end on the user's computer.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts