View Single Post
Old 10-06-2016, 05:07 AM   #3
Super Moderator
Join Date: Feb 2013
Location: UK
Posts: 11,233
Thanks: 40
Thanked 3,643 Times in 3,513 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Need some advice

moving to ms sql or MySQL will not necessarily improve performance (it may even be worse) although you have better security options for the data - and requires more admin to maintain it.

1gb is not too large so I don't see the need to split the back end if that is the reason for doing so - have you compacted the backend yet?

To take advantage of mssql or MySQL server side processing (which is what will improve performance, everything else being equal) requires a major rewrite of all data handling routines from combo/listbox rowsources to queries/forms/reports and handling of recordsets within vba code.

An alternative to consider which requires virtually no change at all is to put the backend and all user front ends on terminal server or citrix. This provides additional security to gain access to the server and performance will be almost as good as if the backend was on your own machine.

Decide first which is more important, data security or performance. If the latter before looking to invest in additional IT resources look at how the front end works:

  • forms with just an unfiltered table as a recordsource will be slow compared with one which has criteria applied
  • tables with lookups and multivalue fields will also be slow (and will not migrate to any other db)
  • fields regularly used in criteria, grouping, sorting should be indexed
  • using domain functions in queries will be slow
there are plenty of other suggestions as well, just google 'improve access performance' or similar

A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
letsaccess (10-06-2016)