People always told me not to use MS Access for my web sites, however the closest they ever got to actually telling me why not, is something like:
"It doesn't perform well if you have many user!" Well how many users is many I asked? This is where Einstein comes in to modern programming: "Everything is relative!"
When I asked how many is many (user on an Access database) no one ever came up with a number - it was everything from 10 to 10.000.
So Access performance has never been an issue to me, since the solutions where I would use it might have 10 user a day! And even though it might always perform worse
then MS Sql or even MySql - the solutions don't need a lot of performance!
So why not use Access? As a newbie to database programming it has a (in my opinion) a very nice GUI - even better now with MS Office 2007 - and it doesn't require a database server.
Well after getting some experience with database programming, I've come in contact with some points where I prefer MS SQL. It's been a long time since I used MySql, so I'll let others to talk about that.
Before I go on I would like to state that this is in no way a scientific or factual post! It is MY experience and look on things. If You have any comments or corrections please don't hesitate to leave a comment!
First thing - User management: I've yet to figure out how that works in Access, but in MS SQL it is as natural a part of creating a database as to create a table.
Second - Schemas: Can you even create schemas in Access? Since I started using schemas I've grown kind fond of it. Primarily because I get a better overview of my database, and again user management. Schemas let's you do something in this schema, but not in the other.
Third (and final for now) - Stored procedures: You can create queries in Access and access them as stored procedures from your code - but beware(!) or you might end up where I was and what actually let me to write this post!
Parameter passing with Access can be a little tricky. I'm used to naming my parameters and passing them to the stored procedure - regardless of the order. BUT in Access naming is not worth anything. It is the order of your parameters that matters!
I spend a lot of time debugging an update procedure that didn't update my database record, didn't throw an exception - actually it didn't seem to do anything. I was checking through values and rewritting a lot of code, since I was certain that the error had to be somewhere in my code.
Finaly I had almost given up, when a colleague told me that Access look at the order of the parameters and not their names. So in my code I was actually looking for a record with the recordId = true, but it should have looked for recordId = 3 (or something)
So that's my brief view on MS Access vs. MS SQL. I can't say (from this) that one is better than the other. I'm just saying that maybe there's a reason professionals use MS SQL and beginner usualy use Access...