Access Reliability in Web Application

[TJ_onTheWeb]

:confused;
Does anyone have recommendation on using Access database in Web application.
I have always used Access, but recently heard that it can cause great problems
if more than one user is accessing information at the same time.
Is there anyway around this or do I need to switch to MySql?
The application is written entirely within Coldfusion, using a DSN for the
Access DB.
This is a small application and it would be rare that more than one user would
be into the same table at one time but it could happen, as there is a login and
the users table would be accessed each time someone logs in.
Any insight would be helpful. I really don't want to have to redo the database.
so far there have been no problems but I would be in a fix if the system
crashed.
Thanks.

[paross1]

Access can be OK for prototypes or small apps, but it can be a liability for
any production system. It is too prone to corruption, doesn't network well, has
serious performance issues, and SQL syntax "quirks" can be a pain, to name a
few. MySQL, SQL Server and Oracle are all much better alternatives to this
file-based database.

Phil

[Dan Bracuk]

Actually, as long as nobody access your db outside of your cf pages, the db shouldn't have multiple user issues. Rapid succession does not equal simultaneous.

[MikerRoo]

<blockquote>quote:<br><hr><i>Originally posted by: <b><b>TJ_onTheWeb</b></b></i>
... I would be in a fix if the system crashed.<hr></blockquote>

There's your answer.

Access DB's need to be "repaired" every now and then and it requires a little
effort to do so.
It is also takes a little finagling to automatically and regularly back up an
Access database.

From your statement above, you should be backing up at least once per day.

I recommend you switch to postGreSQL, MySQL, MS SQL or Oracle (Listed best
first).

[TJ_onTheWeb]

Thanks for the feedback.
It sounds like I need to work on migrating the data into MySQL.
I haven't really used MySQL before but have downloaded it onto my testing
server.
My next client application will be using MySQL on a Unix Web server so I am
setting it up for development on the local server.
Regarding the Coldfusion Application which is currently running on the web,
using an Access database, how disruptive do you think it would be to migrate to
MySQL from Access?
I've downloaded the migration tool from MySQL.com, but have no clue where to
go from there.
Anyone have experience with this?
Thanks!

[MikerRoo]

I've migrated Access to everything BUT MySQL -- but I've heard it's fairly easy
and MySQL even understands much of Access' non-standard syntax.

For migration issues, try the MySQL Access forum at: <a target=_blank
class=ftalternatingbarlinklarge
href="http://forums.mysql.com/list.php?65">http://forums.mysql.com/list.php?65</
a>

[Dan Bracuk]

<blockquote>quote:<br><hr><i>Originally posted by: <b><b>TJ_onTheWeb</b></b></i>
Thanks for the feedback.
My next client application will be using MySQL on a Unix Web server so I am
setting it up for development on the local server.
Regarding the Coldfusion Application which is currently running on the web,
using an Access database, how disruptive do you think it would be to migrate to
MySQL from Access?
Anyone have experience with this?
Thanks!<hr></blockquote>
The work might not be hard, but there is an awful lot of it. I changed one
from Access to Oracle once. What I did was:
1. Create the Oracle tables and sequences.
2. Transfer the existing data to the new db.
3. Test every single query and rewrite the ones that needed it.

So far there is no disruption to your production app, until now.

4. Take the production app offline to freeze the data.
5. Bring the new db up to date.
6. Put your new app into production.

[elDonrico]

depending on the database you are bringing it onto, it might be easier to do a
rewrite, at least it would give you a chance to fix any problems you have been
wanting to fix... i once did an access to informix.... it was not worth it.
every join sql needed fixing.

[TJ_onTheWeb]

Thanks for all the input.
It looks like I've got my work cut out for me.
But migrating to MYSQL sounds like the wise choice.