Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2005
    Posts
    196

    Unanswered: Splitting databases - main advantages

    Hi, I have written a sales back up database in MS Access which our board have asked us to roll out across the whole of the company. There will be up to 300 staff using it at the same time. I'm trying to convince the powers that be that splitting the database is the best option. I know what splitting does, however, can anyone tell me their own personal experiences as to why it's far better than having a non split database that everyone is accessing (or trying to!!)? Cheers.
    Last edited by Fuerteventura; 01-04-06 at 09:36.

  2. #2
    Join Date
    Jan 2006
    Posts
    2
    Hi,
    Basically splitting the database gives you three advantages, speed, ease of management and data store options:

    Speed: Because the program element is seperate it can be stored on each users PC. This gives great speed advantages, as only data is being moved around the network while the screen layouts etc. can just be loaded locally.

    Ease of management: Because the program is a seperate thing to the data, you can make changes to the program without having to change the data store.

    Data store options: By splitting the data out you give yourself the option to move the data into SQL (or other data store) which will give you much improved security and data integrity compared to Access data files.

    Trust this is of help,

    Peter Merry

  3. #3
    Join Date
    Aug 2005
    Posts
    196
    Many thanks for your help, cheers.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think there is some confusion here...

    The default Access data engine is JET which is SQL compatible like most database engines available (including server based products)

    Splitting the database in an Access context normally means separating the data into data (the back end) and the user interface [forms, reports etc] (the front end). If you are planning to run multi user then at the very least you should split the 2 - there are lots of good reasons to do this - not least of which is data integrity, and ease of maintenance. There are those who insist that each user should have their own local copy of the front end.

    If you are rolling this app out ot 300+ users who will use it concurrently then unless you are using a server back end to store the data you are probably going to have a major performance / usability problem. If the applicaiton was designed using JET then although there is an upsizing wizard that will do most of the work, in my epxerience you cannot design stuff for JET and then hope it will work satisfactorily in a server back end, WITHOUT carefull initial design.

    You will get beenfits of security & data integrity, but thats often about it. Leveraging the power and scalability of a server product requires carefull design from the ground up. In many cases it means unlearning lots of Access / JET tricks to start all over with a new design paradigm.

    I'd be very carefull rolling this out to 300+ users, unless its done in a planned and scaled manner.

  5. #5
    Join Date
    Jul 2013
    Posts
    46
    I don't have the large scale with only 5 users at the moment. I have split my database with the Backend and Frontend on the server. I created a link to the Frontend for each desktop. Is there a problem with doing it this way or should I have separate copies of the Frontends on each desktop?

    I thought by doing it this way it would make it easier to make changes so everyone will have the latest version of the Frontend.

    Thanks,
    Bill

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    First off, having multiple users sharing a single, non-split database, sitting on a network drive, or using a split database, but having them link to a single Front End, on a network drive, is the sure way to repeated episodes of corruption, speed and timing problems, and all manner of strange, odd and curious behavior, including the problem you're having, now! Multiple users simply have to work off of a split database, with each user having their own copy of the Front End, which contains everything except the data/tables, on their respective hard drives, and a Back End with only the Tables on a shared drive.

    Being in forced retirement, I spend 8-10 hours a day here and on other Access forums/newsgroups, and over the past eight years have seen literally dozens and dozens of reports of non-split databases, or split databases, using links to a single Front End, causing these kinds of problems as well as massive data lose! The really insidious thing is that a non-split app can work for extended periods of time before the troubles begin! But once it does, they become chronic, which is to say they occur over and over and over again!

    The record, by the way, was a Non-Split db that had been working, without problems, for 12 years! It then started exhibiting all of the symptoms listed above and continued to do so until the app was split.

    If your Database isn't really important, which is to say if data-loss isn't important, and the app being down won't cost you production time loss, then by all means leave it non-split.

    Quote Originally Posted by billgyrotech View Post

    ...I thought by doing it this way it would make it easier to make changes so everyone will have the latest version of the Frontend...
    There are a number of hacks available to help automate the distribution of the newest version of Front Ends.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Jul 2013
    Posts
    46
    Okay I will go with your experience. You say there are ways to update the frontends. How can I do this the best way?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    although Ive not tried it myself in a live app, there is IIRC in the code bank something to handle automatic pull updating of a remote front end
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Here's a couple:

    Auto FE Updater

    Access Help and How-to - Microsoft Office Auto Update users Front Ends - UtterAccess Discussion Forums

    The first one, by Tony Toews, has been around for quite a while and is probably as slick as you'll find. It used to be free, but now has to be licensed, I believe.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Jul 2013
    Posts
    46
    How about if I make a file on the server for each computer. In that file resides a copy of the front end. Then I make a link for each desktop to their appropriate copy of the front end.

    That way I can make changes to the front end and update each copy that resides on the server by replacing them with the current version.

  11. #11
    Join Date
    Mar 2009
    Posts
    120
    Quote Originally Posted by Missinglinq View Post
    Here's a couple:

    Auto FE Updater

    Access Help and How-to - Microsoft Office Auto Update users Front Ends - UtterAccess Discussion Forums

    The first one, by Tony Toews, has been around for quite a while and is probably as slick as you'll find. It used to be free, but now has to be licensed, I believe.

    Linq ;0)>
    I tried using the second one. However when I open the FE after added the code to the Open Event, all that happens is it opens with the Module Navigation option and nothing else. The form never opens or anything and there is no updated version in place. IDK maybe I didn't do something right.

  12. #12
    Join Date
    Jul 2013
    Posts
    46
    I have come to realize that having an auto updater will eventually cause errors.

    I think the best procedure for me would be to manually replace the desktop versions when needed (Front Ends). That way I can make sure the users have the latest version.

    Just my opinion for whatever it's worth.

    Have a great day and thanks for the reply,
    Bill

Posting Permissions

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