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]

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

[Desmo]

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.

[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.

[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.

[Admiral Huddy]

The problem with Access is that it's not to long before you start to realise it's limitations and you need to code VBA behind the events.. Things like validation and database minipulation. SQL is essential too I think.

[MarcLister]

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. :D

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.

[Davey_Pitch]

Marc, if you wanted to use Access you still could, as you wouldn't necessarily need to give the teachers access to the database. You could recommend a system where the teachers/staff email their problems to a generic helpdesk email account, and the 3 technicians can then input the data into the database, which can in turn automatically send out an email to the member of staff with a job number. I think MS actually have a template for this on their website. I'll try and dig it out for you on Monday if you want :)

[Daz]

Building a web based front end to a MySQL DB is a common solution, but that would be reinventing the wheel somewhat for a help desk - look here (http://www.opensourcehelpdesklist.com). Some highlights:

OTRS (http://www.otrs.com/en/tools/faq/) comes up a lot, as it's ITIL compliant and very customisable. Bit complicated though and pretty ugly.

I know a few guys who quite like OSTicket (http://www.osticket.com/index.php) - not very feature rich but simplicity fits the bill sometimes.

RT (http://blog.bestpractical.com/2008/07/today-were-rele.html#screenshots) is somewhere in the middle, sporting a vastly improved new look.

[MarcLister]

Thanks but perhaps I wasn't clear. :D

This isn't for the ICT technicians but the site team (Caretakers). The ICT technicians already have a web-based system that staff can use to report ICT problems. The site team need a database so that staff can report broken windows etc. :)

I'm thinking I might try and get a copy of the ICT technicians database so I can modify it as it has a web-based interface.

[Mark]

I was about to suggest HTML front/MySQL back, but Daz got their first, and as he correctly says, doing it yourself is reinventing the wheel.

Less hassle all round than the alternatives. You don't have to go around installing masses of software for a start, and even the software for the server can be obtained as a package deal if you're lazy. :)

[MarcLister]

Mark are you suggesting using one of the progs Daz suggested? Would I be able to modify them to suit the caretaking team? It isn't an ICT system I need but one allowing staff to report broken windows and so on, for caretaker staff to view, prioritise and cost jobs. Then staff need to be able to see progress on the jobs they've reported.

Quite similar to a helpdesk system but with some fundamental differences. :)

[Mark]

Still a helpdesk system. You're just helping a different department. Don't think you'll have a problem with that. Any system that can't be customised probably isn't worth using for ICT either.

[AboveTheSalt]

I would echo what most people have said. Access will work but I would still advise MySQL. There has to be a reason why Microsoft have SQL-Server rather than Access-Server for serious users.

[MarcLister]

Still a helpdesk system. You're just helping a different department. Don't think you'll have a problem with that. Any system that can't be customised probably isn't worth using for ICT either.Just to be sure, are you suggesting I take the ICT database and customise that OR get a new database system, MySQL say, and use that?

I would echo what most people have said. Access will work but I would still advise MySQL. There has to be a reason why Microsoft have SQL-Server rather than Access-Server for serious users.I'd quite like to use MySQL. I know that Access crashes/freezes with multiple users so I can't really use that.

Edit: Just checked http://www.opensourcehelpdesklist.com/ and it has some good stuff on there. :D

[phykell]

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.

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.

[phykell]

I would echo what most people have said. Access will work but I would still advise MySQL. There has to be a reason why Microsoft have SQL-Server rather than Access-Server for serious users.
There are loads of reasons certainly, but there's a compromise - it's called SQL Server 2005 Express and is the successor to MSDE. Express is the free, redistributable version of SQL Server and is ideal for web applications, etc.

[Mark]

Yup. I'm running SQL Express on my HTPC (backend for Mediaportal). I could just as well have used MySQL, but as it's Windows...

[MarcLister]

Hmm might need to look at SQL Express then. The database will run on a Windows server.

[phykell]

You can download it here:

http://www.microsoft.com/Sqlserver/2005/en/us/express.aspx

[MarcLister]

Cheers phykell. I'll download it and play with it. :)