Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008

    Unanswered: MS Access database - gradual conversion to the server based system


    I am a person with some MS Access experience but new in server based applications.

    A) I have a large MS Access 2003 database. It consists of front-end and back-end. It was used by about 15 people on LAN. After two years it became quite huge - plenty of queries (about 150), forms (about 100), reports (about 60)
    and huge VBA code in FE and large amount of data in BE (about 100 tables; 25 MB).

    B) I would like to convert it gradually from the filebased system to server based system. For the gradual conversion I got an idea to make a middle step or perhaps the final step. It would be a conversion of the BE only into some server based system (free or very cheap) like MySQL, MS SQL Server 2005 Express Edition or PotgreSQL. Then connect the present MS Access FE with new server based BE.

    C) I have tried an example (about 20 tables from the current database) - conversion to MySQL. It was relatively easy. I put it on the server and linked it via ODBC. It started work. But then problems raised - connection
    speed firstly. I have more forms based on entire tables or on the queries (all placed in the FE). It looks like even forms based on queries MS Access downloads all data and halts the net. I also use VBA - define query, open recordset, cycling through it and doing some updates, copies, etc. VBA is also placed in the FE.

    I am looking for the solution of which way I shall focus to continue. Going straight into MS SQL Server or Oracle is very expensive way for me now. Also I have not any experience with it, it would last ages to develop the database again into the same level probably and I need to keep it working and developing. So therefore I think about gradual conversion and about a middle step (perhaps the final) - conversion and putting the BE only on the server as a server based system. Then set the connection between MS Access FE and new BE. I am ready to make changes in the FE and new BE. I think / guess these will be mainly - transferring queries and VBA code on a server side to minimaze data flow through net, place procedures and
    functions on the server side, etc.

    The questions are:
    1) Is that a good idea to make this step (only BE conversion to server and keep MS Access FE + update) or completaly bad ?
    2) Once only the BE converted into server based system - is it at least somehow practical / possible to keep the database user friendly and securily running + still easy for further updating and developing ?
    3) What system (middle step) would you recommend me to convert the BE into ? Would it be MySQL, PostgreSQL, MS SQL Server 2005 Express Edition or any other ?
    4) Is the idea of transferring queries and VBA on the server side correct ?
    5) What system would you suggested to be the final step of conversion (both FE and BE converted) - Oracle or MS SQL Server or any other ?

    I would welcome some guidance which way to focus.
    Thanks very much if you have any to share.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    if you are going to convert to a server back end then in my books there is only one strategy
    if you are on a budget then you are limited in what you can use.
    I forget the limits of Server Express edition.. there used to be a limit on the number of concurrent conenctions.. you may well blow it with 15 concurrent users (IIRC it used to be 4)

    assuming you can use Server Express then you have a partial two stage process.. use the migration/upsizing wizard to port the data into SQL server and leave the app largely untouched. In any event you will need to revisit the app and thoroughly check the changes./ the up sizing wizard does a pretty good job but it doesn't do everything.

    you then need to rewrite the front end to take advantage of the server back end. at the very least that means using pass through queries. Ideally you should rewrite all your forms to use unbound recordsets.

    You can attach tables (link) tables form any SQL source so they look like standard Access.JET tables.. but I'd be tempted to resist this as you get the worst of both worldds.. the overhead of the server and the overhead of JET
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2007
    Adelaide, South Australia
    Agreed. I have several applications in place which were done with attached tables back to SQL because of budget limitations.

    Although the overheads reduce the performance, there are still some advantages, one being the ability to hold more data, another being a higher level of data security.

    To get full advantage of using SQL as a backend can only be reached by doing as Healdem suggests, but it's essentially a complete re-write.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Dec 2004
    Madison, WI
    Here's what I personally have prioritized when I was in your situation:

    1. I'm a big fan of moving everything I can to a server db regardless of how small the database is. There are many advantages to having a db on a server. Backups is a big one (especially point-in-time backups.) But it does mean investing "some" amount of time into learning how to use the server db. I would use SQL Server 2000 or 2005 (versus Express) just to avoid any problems you may have with limitations on SQL Server Express. It took me less than a few days to convert MSAccess tables into SQL Server, establish an ODBC connection, link them into the mdb, and get everything up and running when I first learned how (although establishing security on the server db probably took the longest.) I personally prefer SQL Server 2000 (which you could search and find) as you can get this rather cheaply. I found I could pickup learning SQL Server 2000 very quickly (again, within a few days) so the $ spent was beneficial for the licensing. It took me a lot longer to pickup MySQL and Oracle where MySQL may be free but I spent a fair amount of time learning it and dealing with problems. Also - I did have problems as well with MySQL and ODBC connections. And Oracle, well, I never really "got" how to use Oracle very well. Plus, the MSAccess upsizing wizard works superbly with SQL Server (it takes 1-2 minutes to convert mdb tables into SQL Server) so I'd personally try to stick with SQL Server versus MySQL or another backend. (I think I would have understood MySQL/Oracle better if I had a lot of unix experience.) If you've adapted fairly well to MSAccess 2003, you'll find SQL Server 2000 fairly easy to use. If you've adapted well to MSAccess 2007 or are new to the Server world, you may find SQL Server 2005 is a good choice. If you're not an MSAccess fan, you may have better luck with MySQL, Oracle, or another db server. But definately factor in the amount of time you're anticipating learning it (by trying out the demo versions) along with the cost of it.

    2. Since it sounds like your MSAccess app is fairly complex or could be, I'd worry about re-writing some of the queries to stored procedures/views (ie. server-side) at a later time (especially since these could be done at any point and done form by form.) At this point, I'd simply link the tables into the mdb as nothing would really need to be changed vba code-wise. And there's nothing wrong with using linked tables - they work very well. It's the table/relational design (and MSAccess form design) which can bring down a system to it's knees when accuring mass amounts of records. Designing a good structure (along with good form designs) in my opinion is essential or you're just designing work-around after work-around.

    3. Once SQL Server is up and running, the db is converted and the tables are linked into the mdb, I'd then (personally) focus on converting some of the forms which load/function slowly into unbound forms (if I were dealing with recordsets in the millions.) 25 meg worth of data in an mdb file doesn't sound like it would require unbound forms. This though would mean an extremely fast-running mdb file (if I had slow connections to the network, it would greatly help). But this is after I've exhausted other things I could do with the form design to increase speed and function. Again, I'm not sure then if I would really focus on converting the queries/code into stored procedures yet as this is probably one of the more time-consuming processes (depending on how quick you pickup the coding and setup.) Once you've designed unbound forms (if need be), I think you'll find the speed of the forms extremely fast and you need to look at whether you want to focus on improving the entire application or convert queries to server-side scripts. Designing unbound forms can be very time-consuming in itself so don't make this decision lightly. I've had some extremely fast good running applications (with 5+ million records) without using stored procedures/views but instead just linking the SQL Server tables and then designing unbound forms. But I'd only go the unbound form route if I were dealing with extremely large recordsets. I've gotten along just fine with many (100+ users) using an mdb with linked tables and bound forms without going any further. But this is your decision on where you'd want to go from this point. It's always good to learn how to write server-side scripts stored procedures/views/triggers on SQL Server but I wouldn't necessarily say this is a necessity to get a good fast application up and running. I might though instead focus on unbound forms to increase speed of forms.

    4. Since data security/backups are a priority for me, I might focus on learning how to do tranlog backups with SQL Server, indexing, good relational designs, security and so on. Some of it is similar to MSAccess but there are a few differences. There's a lot of good stuff to learn about SQL Server and since this is housing the data, I might focus on things I can do to improve the safety of the data (ie. if somebody were to delete a mass set of records, how to restore the data using Point-in-Time restore of tranlogs so I could restore all the data from say 10:00 am this morning.) Again though, this sort of thing is a priority for me and I think you'll need to decide whether this kind of stuff is a priority for you or other things are a priority.

    5. One other step I forgot above is to make it so I could easily update the mdb code without affecting users. For example, it's very time-consuming running around and have everyone close out of the mdb because 1 person has that mdb open and locked (or mde which I prefer to use) and you need to copy new code. I very much like the "cloning" script which is found in the code bank ( - if you're interested). I use this technique because it's then easy for me to update my code and copy it to the network "without" having other users close out of the mdb/mde file. Whatever method you use, I think it's important that you establish a system whereby you can easily make updates to the code and copy that new code for users to utilize without going through a bunch of hoops in order to copy that new code. Especially since you don't want to be spending time running around getting everyone to close out of the mdb just so you can copy and have them test your new code each time.

    Again, this is what I find important. Your priorities may be different.
    Last edited by pkstormy; 11-27-08 at 21:26.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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