MS Access - How good is it?

[Desmo]

Here at work we need to set up a mailing list for potential new customers as well as mailing current customers. We currently use our accounts package but it's starting to limit our options on how we split the customer list down.

I was looking at using Access to make a new database which means I can far more selective in business type, area, etc. My initial thoughts are it can do what we want. It will store business details, allow me to select the info I want and then output that to printed labels.

In the future I may well move our entire customer database on to it. I've never used Access before apart from a bit of a play last night, but before I dive in fully, how good is it? How robust? Are there any limitations?

[leowyatt]

Well for what you want it seems perfectly suitable. Unfortunately I don't really have much experience with Access anymore as we use mysql databases at work.

[chumpychops]

It'll do what you want, but if ever you want to expand on it you'll be pretty ****ed.

You should consider MySQL. Its open source, has LOADS of support, and you dont have to pay for it.

[Desmo]

Originally Posted by chumpychops

but if ever you want to expand on it you'll be pretty ****ed.

Expand in what way? The only long term thing I think I can see us using it for is a computerised job docket rather than the paper version we use now.

[phykell]

Originally Posted by chumpychops

It'll do what you want, but if ever you want to expand on it you'll be pretty ****ed.

I can't agree with that. It's a pretty standard job for a developer to port Access applications to SQL Server (for example) - the latter has importing functionality specifically for Access. A simple Access application should present no barrier to porting to SQL Server for future development.

Originally Posted by chumpychops

You should consider MySQL. Its open source, has LOADS of support, and you dont have to pay for it.

Good advice on the MySQL but it's then relatively difficult to find a developer, at a reasonable rate, who would extend or build on it for future development.

[Desmo]

MySQL would be good, if I knew how to use it, program for it and modify it :/

[Daz]

Access is great for up to, say, 3-5 concurrent users - beyond that you get all sorts of fun with locking and what not and you'll be kicking everyone out of it 3/4 times a day to fix it.

It'll handle a reasonable amount of data pretty well ime (up to a couple of GB), but I do agree with chumpy that MySQL will give you more options in future.

In terms of the two though, for a few users Access is like a combined database backend and user frontend tool, whereas MySQL is just your backend - you'd need to put something in front of it, so Access is probably the best place to start. Can always move the data later if needs be.

[MarcLister]

Originally Posted by Daz

Access is great for up to, say, 3-5 concurrent users - beyond that you get all sorts of fun with locking and what not and you'll be kicking everyone out of it 3/4 times a day to fix it.

It'll handle a reasonable amount of data pretty well ime (up to a couple of GB), but I do agree with chumpy that MySQL will give you more options in future.

In terms of the two though, for a few users Access is like a combined database backend and user frontend tool, whereas MySQL is just your backend - you'd need to put something in front of it, so Access is probably the best place to start. Can always move the data later if needs be.

Apologies for bumping and hijacking this thread. Just been emailing an ex-colleague of mine. Is a teacher in a local school and is in charge of IT and network stuff there. He would like a new database done and from his description of it this is going to be quite a large database. It is for teachers/staff to submit repair/work requests to the site team. The site team need to be able to view the requests and prioritise them. So already I know the database must be multi-user. There are 3 site team staff and 70+ teachers/staff.

My Mum works at the same school and she often tells me that she has to use an Access database for something else entirely and how it crashes/freezes regularly. I know the database is used by teachers so I'm almost certain Access just can't cope with a high number of concurrent users so I'm thinking Access can't be what I use (or find someone else to use if I don't feel confident doing this project!). MySQL will probably suit seeing as it is quite cheap.

I did SQL at Uni so it shouldn't be that hard to revise that and get a backend going that can be stored on a network drive. However if I understand the thread so far I would need to create a front end for the staff/teachers/site team staff to use? Would this front end be stored on each PC/laptop locally? Or on each user's network area? How could I put the front end together? Can MySQL help me with this or do I need to program my own front end with Visual Studio or something?

My contact won't be able to discuss the finer details with me for perhaps 2-3 weeks so I have time to download and install MySQL and get to grips with it. I really cannot see how I can use Access for this project. If a current Access database crashes regularly I can't expect it to improve for this new database.

[Desmo]

There's only ever going to be 3 users at most, most of the time just the one so that shouldn't cause us any issues

[Mark]

What Daz said, almost. Modern versions of Access (2000 and up) cope up to just under 2GB data. Keep things simple with a few users and it'll do fine. It's when you start trying to get 'clever' with it (table JOINs and the like are a killer) that it will fall on its arse.

And believe me, it really does fall on it's arse, painfully. Been there, had to rewrite apps to make it work, got the t-shirt.