MS Access Vs. SQL Server... Internet Appropriateness

Report · Mar 31, 2009

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

Report · Mar 31, 2009

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/

Report · Mar 31, 2009

The vendor's statement is correct. MS Access is an sql compliant database. It's also irrelevent.

I'm guessing your cold fusion pages have select queries only. You could always write a db on other RDBMS with the same schema as the access one. Then, when you receive a new mdb file, go through it and update your real db as required.

Report · Mar 31, 2009

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

--
Adam

Report · Apr 03, 2009

Here are some articles from Microsoft that deal with scenarios on 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-2d75c49172bc/when_to_Migrate_fr...

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

Report · Apr 10, 2009

There used to be a chunk of code in the code exchange called "breaklock" that would sever the database connection to Access and make the file avaialble again. Was a bit twitchy, but usually worked.

Here is the code from it, use at your own risk!

<CFSET CFUSION_DBCONNECTIONS_FLUSH()>

or

Report · Apr 23, 2009

Tami,

Refer to http://kb.adobe.com/selfservice/viewContent.do?externalId=tn_17034&sliceId=2 and also google for any other related pieces.

The vendor's right. Access is a SQL-compliant database. That statement however has absolutely nothing to do with why desktop databases (like Access) are not supposed to be used in multi-user environments.