Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009

    Unanswered: Is it time to move on from Access?

    Hi All,

    I have been thinking about this post for several days as I wanted to ensure I got all the details across without boring the pants off the reader! However, there really isn't a way to explain without writing a fairly long post, sorry!


    I am not a database engineer, but I do have a software engineering degree.

    I own a company which has grown over 5 years from 3 database users to 28 users.

    I wrote an Access database, back end front end, some 5 years ago and have continued to add functionality as required. It has run my business very well and includes staff wages, invoicing, 3950 construction projects etc. I use queries, reports, forms and a lot of code behind the scenes.

    I love the product, easy to use and I am fairly competent with the programming language.

    But I am now getting a few problems, such as slow searches, occasional crashes and the database locking seems to get messed up sometimes. The database really needs to be rebuilt as the logical data model has a lot to be desired mainly because I have bodged many extra functions into the database over the years.

    Due to rapid expansion, I now have upgraded to a top notch server running linux, with the 28 workstations all with windows OS. Just opened another office which I have set up a VPN to the server, but I do not seem to be able to get the front end to communicate through the VPN. The back end isn't as happy sitting on the Linux server and this shows up on the workstations by frequently losing connection over the network, but runs smoothly when I swap back to the old windows server. The back end file size is only 20Mb.

    As an experiment I converted the back end to MY SQL. It seems to link perfectly with my Access front end, although I have not checked to see if this will solve the VPN connection problem.

    I suspect the amount of users will go up to around 35 staff over the next two years. However, the database is mainly used by staff to find the relevant project, click on the link for that project which opens the correct folder. They then design a sprinkler system and then make notes in the database. Therefore, while the database front end is open on all machines, the interaction with the database is low.

    MY Question
    It really is time to start a total rebuild but I am unsure if it is time to move on to another database system. Frankly, I am very comfortable with Access and don't really have the time to learn a new database system. Cashflow is good but I am not awash with spare finance, so I really can't afford to hire the services of an external company.

    So here are my questions:

    Is Access suitable for my growing business?

    Is it a better solution to use MY SQL for the back end?

    If not, what system should I move on to?

    Of the possibilities, which solution will be most like Access especially with regards to the use of Visual Basic as I have pages of code that I have written over the years?

    Thanks for any guidance.


  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    What follows is only a personal opinion about Access.

    I'm a professional developer and I work with Access (among other products such as C++, C#, Delphi) since its version 1.1 (1993). From version to version Access was improved (except for the version 95 wich was instable to the point of being a failure). The constant improvement in the product crashed with the outcome of Access 2007 (12). That version was a major failure: you could lose your database when compacting it, many bugs crashed any application developed with it, and although several patches and service packs corrected the major problems, I could go on for pages and pages.

    Then came Access 2010 (14) wich is more stable. However this version still causes many problems. It's slow, sometimes very slow. I made tests running the same application on the same or a similar machine with both Access 2003 (10) and Access 2010 (14) and the result is flagrant: an application running with Access 2003 is 1.5 to 2.8 times faster than the same application running on Access 2010. Moreover, the presence of many incoherencies such as the infamous "multi-value columns" are almost impossible to understand.

    With the transition from a standard menu-driven application to the "ribbon" (which I loathe!), several useful features dissapeared from the product, such as the interface that allows to create toolbars, menus and pop-up menus or the possibility to save a project as an .mda, accda (wizard) file. Even the VBA Editor is less stable and Access often crashes if you let the Local window open while debugging, for instance.

    My feeling is that Microsoft tries to steer developers away from Access (or from anything that has a connection with the late Visual Basic for that matters). We're in the .Net era and there will not be any return. If you take the time (if you can afford it), you'll discover that you can do almost everything you use to do in Access with VB.Net. The only feature for which I was not able to find a direct replacement is the subform. With a good knowledge of VB.Net everything else can be done with that language and IDE.

    Although I found it a little sad, I don't care too much because a) I'm 60 and will be a pensioner in 5 years while Access 2016 (wich, from the preview I could test is not worse than the 2010 version) will be supporte to 2022 at least. b) Because Access is not the only development system I use.

    My piece of advice is to go on developing in Access for "light" or non-essential project but to prepare for a transition to something else in the future. If you can afford the time and work it necessitates, you should invest in VB.Net which is the environment that's the most similar, although there are big differences between VB-VBA and VB.Net ("Visual Fred"). For the existing projects, Access can continue to be used for several years.
    Have a nice day!

  3. #3
    Join Date
    Jul 2009
    Thanks for your thoughts Sinndho.

    Very interesting and I think I will have a look a VB.Net.

    It does concern me when you say that you were unable to find a replacment in VB.Net for a subform as I use subforms a great deal in Access.


  4. #4
    Join Date
    Mar 2009
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    I'm nearly certain that the reason that you're having problems running a FE/BE (Front-End/Back-End) Microsoft Access problem via VPN is actually due to the problems with the speed of the telecom link (which may be aggravated by the VPN). It is easy to test this assumption by using RDP to connect from a PC that isn't on the local network that hosts your BE to a PC that is on the local network using it's own copy of the FE. This can be either from home or from the other office using VPN to access it. If the remote access speed is the problem, this will fix the issue.

    If this turns out to work splendidly, then Access isn't the problem per-se but the "chatty" design of the application that forces many "round trips" of data from the FE to the BE is being strangled by the speed of remote access. The work-around for this is to set up an RDP based solution such as Citrix, RDWeb, or Microsoft Terminal Server with copies of the FE for each session. Your users can connect and use the app just like they were sitting inside your office next to the BE server.

    Moving your BE to a client server database engine like Microsoft SQL Server or MySQL is a good idea. Be forewarned that Oracle isn't going to leave MySQL free to use forever, if you are starting from a "clean slate" you should investigate PostgreSQL or MariaDB and check to see what Oracle's licensing terms will be for MySQL if those have been announced. Once you make the migration to a client/server database engine you can continue to use your existing MS-Access FE and can simultaneously start to investigate using other programming tools that suit your needs.

    I use Microsoft Access for personal use, and support it for some clients to use with workgroups (up to a dozen or so people). Access can support many more users (thirty should be possible), but at least for me it has always become a bigger problem than a solution as you continue to add more users.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 8
    Select TOP 3 [table].* From [table]
    hope this help

    See clear as mud

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

Posting Permissions

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