At work we have a MS Access database we use solidly for 1 month every year and then forget about it for the other 11 months.

The file lives on a Win2k file server and approx. 8 clients connect across the (currently) 10MB lan. This works well enough so long as the database is compacted every day - it grows to about 3X 'normal' size (20MB) and becomes slow to load otherwise. Also, this 'feels' very corruption prone.

This year the customer data will be at least 5X larger than before and maybe it's about time we used a standalone SQL database engine with Access simply as a 'front end' - I appreciate this may not be the best solution but I need to consider available development time/skills/resources etc. ("My time!").

I know this can be done with MS SQL Server but preferably can we hookup Access to a MySQL database server instead to save money? It would also be helpful to understand whether development time is likely to be a lot higher with MySQL as opposed to MS SQL Server.

Any thoughts/ramblings (please)?

Thanks,
Rue

You should be able to use MyODBC with no problems.

And unless you're doing something extraordinarily complex or very MS-Windows-specific, I can't imagine that development time would be that much different. I mean, it's just a database. As long as it reliably stores the data you need in the way you want and you can access it the way you want, who cares what the backend is?

I've used MyODBC with Access without problems, although not in a production situation, it works very well.

Bear in mind that if data integrity is important and/or lots of people are likely to be updating records constantly, PostgreSQL may be a better option.

Judging by posts so far it looks like MySQL is a 'Go', good.

I don't know whether to host MySQL on a Win2k (company supported OS) or Linux (unsupported) OS. I prefer Win2k here simply because it's recognised at a corporate level and given the (albeit MS OS only) worms last year at least the OS is supported, if I run Linux I'm worried I might get caught out keeping up to date with exploits/patches etc.

Perhaps I've already answered my own question here but are there any compelling reasons NOT to go with a Win2k/MySQL combination as opposed to Linux/MySQL (we have a corp. licence on Win2k svr so no visible costs here)?

I think I would reiterate what David said, if data integrity is an issue, then I would very much advise PostgreSQL over MySQL.
MySQL really is a lightweight compared to Postgres.
The big problem with MySQL and the main reason data integrity is an issue is the complete lack of transaction support. This will be introduced with V4 but that is only available in alpha at the moment.
PostgreSQL on the other hand is a real built from the ground up transactional database server
But if you want the company supported line, (read Microsoft) the the only answer is not cheap, SQL Server.