willis888

I seem to have become the "IT department" of my office by virtue of the fact that I'm the only one here that knows anything about computers - we're a not-for-profit community farm in rural Virginia. We grow heirloom vegetable seeds and sell them through a web page. As a life-long gamer, I've had to learn my way around hardware and software to make games work smoothly. I can add functionality to our pre-existing MS Access database and have set up the network, but proper database and LAN design are beyond the scope of my self-directed 'training'. I'm hoping that somebody with more experience can help me find our bottleneck, and thereby improve the lives of 20 strangers.

Our office uses a MS Access database for order fulfillment, sales projections, inventory management, and several other cool features various people have written into it over the years. Right now it consists of two files, the front end UI forms and queries consisting of a 64mb .mdb file, and the data, a 101mb .mdb file. The front end lives on the server for reliability of updates and backups.

Our office will soon have up to 10 computers that people might want to open the database on.

For now, the database is hosted on an off-the-shelf NAS box in a 2 disk RAID1 array that backs up daily and weekly to a second 2 disk RAID1 in the same box.

The relevant part of our network is the 8 port linksys 10/100 switch in the central office that the NAS and most-used workstations are plugged into. The workstations are a variety of ~3 GHz dual core machines with 2gb DDR(2) of varying speeds, all running WinXP Pro Sp3. I'm not sure if it matters, but there are three wireless routers, one wireless bridge (~300' between buildings with two cantennas), and a firewall+T1 modem also attached to the network, all 10/100 devices.


I'm not sure if the bottleneck is the network, NAS processor, NAS disks, or if it is just the way XP and Access interact over a network.

Some things I've considered:

-Gigabit ethernet switch (+ Router if it will improve performance of our T1 connection?) + NICs

-Using our catalog-maker (see sig build) as a server for 10 months of the year when it isn't making catalogs:
--Load the database to a RAM disk on boot?
--Good backup software to regularly copy the RAM disk to RAID1 NAS?

-Optimizing the database architecture
--Are we not big enough to take advantage of any of the above listed expensive options? Do we really need to give up all the great features that have been added to our UI and start using MySQL?
--Is there an on-line list of rules and appropriate techniques for writing MS Access queries and VB scripts in a way that will minimize network usage or otherwise improve the user's experience to a significant degree?

What is going to get us the biggest performance gain for the least amount of time and money? What about just the least amount of money?

Thank you for your brain-cycles.

-Will