0

I'm looking for some advice or pointers to good resources with regards to the following set up that I have in a small office.

Currently I am running an access split database with files in a shared folder across a wifi network, with 2-4 other machines accessing the database. The hosting PC is a pretty low spec machine running XP, and there are a few performance issues and things can slow down when running macros on Excel/Word files from the shared folder.

I intend to upgrade the hosting machine but would like to know what type of setup options I have in respect of performance and backing up the data and files.

As far as my limited understanding is, I can continue with the current setup or move to using a server and switching to an SQL server backend (which would be a separate issue in itself) for the database.

I feel that a dedicated server may be going over the top with the current size of the office, but should be considered if we expand further down the line?

If I am correct with the above, what type of spec machine should I be looking for to cope with my requirements ease. As with any small business price is also a factor!

Are there other options that I am not taking into consideration?

2
  • @John Gardeniers isn't this question related to servers and server set up?
    – noelmcg
    Mar 31, 2011 at 22:21
  • there is no reason for the 'server' tag to even exist on this site, as it's far too general to be of any value. And no, this isn't about server set up. It's about client/server design. Mar 31, 2011 at 22:28

3 Answers 3

1

SQL Server Express Edition is free, and will probably work well for hosting the data used by an Access front-end. I've seen some very rare cases where a (tremendously) poorly-designed Access database performed substantially worse after migrating the back-end to SQL Server, but unless you've got forms bound to tables with 20,000+ rows and no filters, you'll probably be okay. In fact, odds are it will perform better, thanks to the caching and locking handled by SQL Server.

You'll also get much better reliability and security compared to an Access mdb/accdb file.

I'd suggest downloading 2008 R2 Express - which can run on XP SP3 - and giving it a try with a test copy of your Access database. The Upsizing Wizard included in Access usually makes this pretty painless.

2
  • i wasn't aware of SQL Server Express Edition, thanks for the heads up. Will try and do some homework on it and see how i could migrate my access db to it. It would seem to be the natural progression of upsizing a database.
    – noelmcg
    Mar 31, 2011 at 20:46
  • +1 on trying out SQL express. Only caveat with a small shop is a max DB size of 10GB with the 2008R2 SQL express (older versions are 4GB max).
    – JGurtz
    Apr 4, 2011 at 19:04
0

Having an in-house server is definitely a need if there is a possibility of growth at a company. With less than 5 users, peer-to-peer or workgroup style networking and security is pretty doable. Any more users than that and becomes much more manageable to have Active directory and server grade hardware.

Take a look at ML350 G6 server series from HP. The price might surprise you and they can be expanded nicely as the business and budget allow. In this day and age I always recommend running VMWare ESXi and the free version works out perfectly for SMBs. These HP servers have an sd card slot where you install ESXi. Hard drives are used just for the virtual machines.

3
  • will check out the servers and will try to get some background knowledge into setting one up. At the moment we haven't quite reached 5 users! So may upgrade the main pc first and then look to the expansion of an in house server in the near future. Would you be aware of any good online resources / books for getting started?
    – noelmcg
    Mar 31, 2011 at 20:42
  • hp.com Click on large enterprise, then servers, then look for ML series. After you see all the models take a look at the html specs. This page will let you see what all the available options are. We typically use CDW as our HP reseller so once you have a model number give them a call and see if they can hook you up! Typically, you need a server model, hard drive model and count, and any extras.
    – JGurtz
    Apr 4, 2011 at 19:00
  • cheers for the heads up, time to get the thinking cap on and figure out what to go with. +1
    – noelmcg
    Apr 6, 2011 at 10:27
0

While you say "database" it appears from your description that you're really talking about normal shared files, which is certainly no database.

Given the budget and hardware you have (or don't have) what I suggest you do is to install a proper database server on the existing "server". I recommend you use MySQL, as it can run very happily on low spec hardware and is free. Compared to what you're doing now performance will be excellent.

Accessing the data is a separate issue. Using ODBC most MS Office applications can be used. I prefer to use Access for a front end but you can also use Excel or Word if that's the way you prefer to do things.

Incidentally, I suspect your current performance issues are more the result of anti-virus scanning than anything else.

1
  • it is a split databse with the accdb file on the 'main' host pc. Each work station has its on access front end installed. I also have folders, and in turn word / excel / jpg files, associated with certain records in a shared folder. I have noticed quite a delay when running the macro's in the latter files, even if the laptops have a better spec than the 'main' machine. Will look into the anti-virus issue, im running microsoft security essentials on all the machines. All the shared folders are set as 'trusted locations' for the purpose of running macros.
    – noelmcg
    Mar 31, 2011 at 22:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .