MS Access Vs. SQL Server... Internet Appropriateness

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

HHWd - 31 Mar 2009 13:37 GMT

I have a client who uses and uploads an Access database to the internet
that we are using Coldfusion to access.

The issue is, that when someone is "viewing" the Coldfusion page, then
the database gets a lock, as Access believes (correctly) that the
database is open. Often times the upload is attempted at the same time
the lock is in place, and then both the access .mdb file and the .ldb
files become so corrupted, that they cannot be deleted, removed, or
otherwise corrected through an FTP access. I have to get tech support
at the hosting office to manually remove the locks and deleting both
files.

The upload is an export of a proprietary third party vendor office
system. I have made recommendations to have the database uploaded in an
Internet appropriate database, like SQL server or MySQL. The vendor
maintains that "ACCESS is a SQL compliant relation database". I content
that is not designed for multiuser Internet database use.

Can someone provide some links, insight, or other direction that either
validate my position or validate the vendor?

Note... this file used to be an Excel spreadsheet export that we were
able to upload and create Coldfusion pages, and the lock corruption
issue was worse then... Now is happens about 2-3 times per year
as opposed to 5-6... STill the issue I believe is Microsoft related.

Tami

Reply to this Message

Azadi - 31 Mar 2009 13:54 GMT

using 'ms access' and 'server-side db' in the same sentence is a plain
oxymoron.
ms access is a DESKTOP database application.
any mission-critical website should NEVER even CONSIDER using ms access...

but the locks you are talking about... they can appear at times when
there happens a disruption in network traffic during an insert/update...
they can easily be programmatically 'lifted' by running a query that
tries to select from a non-existent table in the access db...

the problem is - you never really know until it is too late that the db
has locked up... short of running a bogus table select query before any
other query in your code, you only find out about a lock when you start
getting error alerts in your mail...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Reply to this Message

Adam Cameron - 31 Mar 2009 21:12 GMT

> using 'ms access' and 'server-side db' in the same sentence is a plain
> oxymoron.
> ms access is a DESKTOP database application.
> any mission-critical website should NEVER even CONSIDER using ms access...

Seconded. Access is designed for "My CD Collection", not "My Production
Website".

As you - the OP - have already discovered - it's a single-user system *by
design*: it's not meant for more than one person to use at once. This
immediately discounts it as an option for a production system. Before
anyone else starts, *yes*, it *can* be used for more than "My CD
Collection", but it's not intended for anything more than that. It's very
good as far as desktop applications go, but it's a desktop application.

>> The vendor maintains that "ACCESS is a SQL compliant relation database". I content
>> that is not designed for multiuser Internet database use.

What does "SQL compliant" mean? Do they mean it conforms to one of the
ANSI (or is it ISO now?) SQL standards? I doubt that very much.

Anyway, the compliance of the query language has nothing - NOTHING - to do
with what the underlying DB engine was designed for. And it was designed
for single-user "My CD Collection" type databases. That your "vendor"
doesn't understand that means that doing research or finding references to
point them at is a fool's errand, because they're already out of their
depth.

Signature

Adam

Reply to this Message

JR "Bob" Dobbs - 03 Apr 2009 21:37 GMT

Here are some articles from Microsoft that deal with scenarios where using
Access vs SQL Server.

"...Microsoft ODBC Driver for Access and Microsoft OLE DB Provider for Jet are
not intended for use with high-stress, high-concurrency, full-time server
applications (such as Web applications, commerce applications, transactional
applications, messaging servers, and so on)."
http://support.microsoft.com/kb/316675

"Microsoft Access is a database intended for small projects with a few users.
When a database grows large, and more users need to work with it, you are ready
to move up to the more robust and secure database solution provided by
Microsoft SQL Server."
http://www.microsoft.com/sqlserver/2005/en/us/migration-access.aspx

Article: "When to Migrate from Microsoft Access to Microsoft SQL Server"

http://download.microsoft.com/download/5/d/0/5d026b60-e4be-42fc-a250-2d75c49172b
c/when_to_Migrate_from_Access.doc

Note that Microsoft encourages migration from Access to SQL Server, however
you could migrate to another database server such as Oracle, DB2, etc.

Reply to this Message

Webmaster Forum / ColdFusion / Database Access / April 2009

MS Access Vs. SQL Server... Internet Appropriateness