Connecting to an Access database

In a KB article (http://support.microsoft.com/kb/299974/), it is
stated:
"When you run Microsoft Jet in an IIS environment, it is recommended
that you use the native Jet OLE DB Provider in place of the Microsoft
Access ODBC driver. The Microsoft Access ODBC driver (Jet ODBC driver)
can have stability issues due to the version of Visual Basic for
Applications that is invoked because the version is not thread safe. As
a result, when multiple concurrent users make requests of a Microsoft
Access database, unpredictable results may occur....While Microsoft Jet
is consciously (and continually) updated with many quality, functional,
and performance improvements, it was not intended (or architected) for
the high-stress performance required by 24x7 scenarios, ACID
transactions, or unlimited users, that is, scenarios where there has to
be absolute data integrity or very high concurrency."

In basic usage (just simple retrieving of rows or tables, and executing
INSERT or UPDATE statements), what "unpredictable results" are possible
that could affect data integrity?

Re: Connecting to an Access database

brian.lukoff@gmail.com wrote:
> In a KB article (http://support.microsoft.com/kb/299974/), it is
> stated:
> "When you run Microsoft Jet in an IIS environment, it is recommended
> that you use the native Jet OLE DB Provider in place of the Microsoft
> Access ODBC driver. The Microsoft Access ODBC driver (Jet ODBC driver)
> can have stability issues due to the version of Visual Basic for
> Applications that is invoked because the version is not thread safe. As
> a result, when multiple concurrent users make requests of a Microsoft
> Access database, unpredictable results may occur....While Microsoft Jet
> is consciously (and continually) updated with many quality, functional,
> and performance improvements, it was not intended (or architected) for
> the high-stress performance required by 24x7 scenarios, ACID
> transactions, or unlimited users, that is, scenarios where there has to
> be absolute data integrity or very high concurrency."
>
> In basic usage (just simple retrieving of rows or tables, and executing
> INSERT or UPDATE statements), what "unpredictable results" are possible
> that could affect data integrity?


You won't get too many problems with SELECT statements, apart from Jet
eventually running out of resources in a high-user environment. Having
said that, I have an Access 2000 db humming along quite nicely serving
up over 1/2 a million recordsets a month, and has done so for a few
years with no problems.

INSERTS and UPDATES are the potential killer for Jet. Incomplete
operations could leave you with a corrupted mdb file.

More info:

http://databases.aspfaq.com/database/what-are-the-limitation s-of-ms-access.html


--
Mike Brind

Re: Connecting to an Access database

This may be a silly question, but does a corrupted MDB file just mean
that I won't be able to open the file (or will receive errors when I
do), or could I be left with a database that can be read without errors
but that contains incorrect values? (If so, would the user that caused
such a problem have received an error from their web browser when it
occurred?)

Mike Brind wrote:
> brian.lukoff@gmail.com wrote:
> > In a KB article (http://support.microsoft.com/kb/299974/), it is
> > stated:
> > "When you run Microsoft Jet in an IIS environment, it is recommended
> > that you use the native Jet OLE DB Provider in place of the Microsoft
> > Access ODBC driver. The Microsoft Access ODBC driver (Jet ODBC driver)
> > can have stability issues due to the version of Visual Basic for
> > Applications that is invoked because the version is not thread safe. As
> > a result, when multiple concurrent users make requests of a Microsoft
> > Access database, unpredictable results may occur....While Microsoft Jet
> > is consciously (and continually) updated with many quality, functional,
> > and performance improvements, it was not intended (or architected) for
> > the high-stress performance required by 24x7 scenarios, ACID
> > transactions, or unlimited users, that is, scenarios where there has to
> > be absolute data integrity or very high concurrency."
> >
> > In basic usage (just simple retrieving of rows or tables, and executing
> > INSERT or UPDATE statements), what "unpredictable results" are possible
> > that could affect data integrity?
>
>
> You won't get too many problems with SELECT statements, apart from Jet
> eventually running out of resources in a high-user environment. Having
> said that, I have an Access 2000 db humming along quite nicely serving
> up over 1/2 a million recordsets a month, and has done so for a few
> years with no problems.
>
> INSERTS and UPDATES are the potential killer for Jet. Incomplete
> operations could leave you with a corrupted mdb file.
>
> More info:
>
> http://databases.aspfaq.com/database/what-are-the-limitation s-of-ms-access.html
>
>
> --
> Mike Brind

Re: Connecting to an Access database

Depending on the level of corruption you could get any of the
following:

Microsoft JET Database Engine error '80004005'
Unrecognized database format '.mdb'

or

Microsoft JET Database Engine error '80004005'
Cannot Open Database. It may not be a database that your application
recognizes, or the file may be corrupt.[1]

These may be reparable by running JRO commands to compact and repair
the database remotely or downloading the file, opening it and running
compact and repair, then upload it again.

Or you may get the very much more serious:

Catastrophic Failure[2]

on every page that accesses the db. If you are lucky, you may be able
to download the file, open it and export all the objects and data to a
new file, and upload that. If you are not, you will have to rely on
the last back up. If you have no backup, you may be able to get a
specialist to recover the file, then again you might not.

Bear in mind that backups of mdb files are normally done as a scheduled
task to copy the file over at a predetermined time. If the file is
locked (open) at the time the task runs, you maight not get a copy
done, so your actual backup might be a day or more out of date.

[1] I used to get this regularly on Monday mornings with an Access 97
database and a DSN connection. The database was primarily read-only,
and moving to OLEDB and Access 2000 solved that problem.

[2] This is the one that got me migrating to SQL Server.

--
Mike Brind

brian.lukoff@gmail.com wrote:
> This may be a silly question, but does a corrupted MDB file just mean
> that I won't be able to open the file (or will receive errors when I
> do), or could I be left with a database that can be read without errors
> but that contains incorrect values? (If so, would the user that caused
> such a problem have received an error from their web browser when it
> occurred?)
>
> Mike Brind wrote:
> > brian.lukoff@gmail.com wrote:
> > > In a KB article (http://support.microsoft.com/kb/299974/), it is
> > > stated:
> > > "When you run Microsoft Jet in an IIS environment, it is recommended
> > > that you use the native Jet OLE DB Provider in place of the Microsoft
> > > Access ODBC driver. The Microsoft Access ODBC driver (Jet ODBC driver)
> > > can have stability issues due to the version of Visual Basic for
> > > Applications that is invoked because the version is not thread safe. As
> > > a result, when multiple concurrent users make requests of a Microsoft
> > > Access database, unpredictable results may occur....While Microsoft Jet
> > > is consciously (and continually) updated with many quality, functional,
> > > and performance improvements, it was not intended (or architected) for
> > > the high-stress performance required by 24x7 scenarios, ACID
> > > transactions, or unlimited users, that is, scenarios where there has to
> > > be absolute data integrity or very high concurrency."
> > >
> > > In basic usage (just simple retrieving of rows or tables, and executing
> > > INSERT or UPDATE statements), what "unpredictable results" are possible
> > > that could affect data integrity?
> >
> >
> > You won't get too many problems with SELECT statements, apart from Jet
> > eventually running out of resources in a high-user environment. Having
> > said that, I have an Access 2000 db humming along quite nicely serving
> > up over 1/2 a million recordsets a month, and has done so for a few
> > years with no problems.
> >
> > INSERTS and UPDATES are the potential killer for Jet. Incomplete
> > operations could leave you with a corrupted mdb file.
> >
> > More info:
> >
> > http://databases.aspfaq.com/database/what-are-the-limitation s-of-ms-access.html
> >
> >
> > --
> > Mike Brind

Re: Connecting to an Access database

OK. Am I correct in saying that if the database is corrupt I would
know because users would be getting errors trying to access the
database from ASP pages? (I'm just trying to make sure that there
couldn't be a situation where the ASP pages accessing/writing to the
database appear to be working fine, but the database contains incorrect
values because of some sort of corruption.)

Mike Brind wrote:
> Depending on the level of corruption you could get any of the
> following:
>
> Microsoft JET Database Engine error '80004005'
> Unrecognized database format '.mdb'
>
> or
>
> Microsoft JET Database Engine error '80004005'
> Cannot Open Database. It may not be a database that your application
> recognizes, or the file may be corrupt.[1]
>
> These may be reparable by running JRO commands to compact and repair
> the database remotely or downloading the file, opening it and running
> compact and repair, then upload it again.
>
> Or you may get the very much more serious:
>
> Catastrophic Failure[2]
>
> on every page that accesses the db. If you are lucky, you may be able
> to download the file, open it and export all the objects and data to a
> new file, and upload that. If you are not, you will have to rely on
> the last back up. If you have no backup, you may be able to get a
> specialist to recover the file, then again you might not.
>
> Bear in mind that backups of mdb files are normally done as a scheduled
> task to copy the file over at a predetermined time. If the file is
> locked (open) at the time the task runs, you maight not get a copy
> done, so your actual backup might be a day or more out of date.
>
> [1] I used to get this regularly on Monday mornings with an Access 97
> database and a DSN connection. The database was primarily read-only,
> and moving to OLEDB and Access 2000 solved that problem.
>
> [2] This is the one that got me migrating to SQL Server.
>
> --
> Mike Brind
>
> brian.lukoff@gmail.com wrote:
> > This may be a silly question, but does a corrupted MDB file just mean
> > that I won't be able to open the file (or will receive errors when I
> > do), or could I be left with a database that can be read without errors
> > but that contains incorrect values? (If so, would the user that caused
> > such a problem have received an error from their web browser when it
> > occurred?)
> >
> > Mike Brind wrote:
> > > brian.lukoff@gmail.com wrote:
> > > > In a KB article (http://support.microsoft.com/kb/299974/), it is
> > > > stated:
> > > > "When you run Microsoft Jet in an IIS environment, it is recommended
> > > > that you use the native Jet OLE DB Provider in place of the Microsoft
> > > > Access ODBC driver. The Microsoft Access ODBC driver (Jet ODBC driver)
> > > > can have stability issues due to the version of Visual Basic for
> > > > Applications that is invoked because the version is not thread safe. As
> > > > a result, when multiple concurrent users make requests of a Microsoft
> > > > Access database, unpredictable results may occur....While Microsoft Jet
> > > > is consciously (and continually) updated with many quality, functional,
> > > > and performance improvements, it was not intended (or architected) for
> > > > the high-stress performance required by 24x7 scenarios, ACID
> > > > transactions, or unlimited users, that is, scenarios where there has to
> > > > be absolute data integrity or very high concurrency."
> > > >
> > > > In basic usage (just simple retrieving of rows or tables, and executing
> > > > INSERT or UPDATE statements), what "unpredictable results" are possible
> > > > that could affect data integrity?
> > >
> > >
> > > You won't get too many problems with SELECT statements, apart from Jet
> > > eventually running out of resources in a high-user environment. Having
> > > said that, I have an Access 2000 db humming along quite nicely serving
> > > up over 1/2 a million recordsets a month, and has done so for a few
> > > years with no problems.
> > >
> > > INSERTS and UPDATES are the potential killer for Jet. Incomplete
> > > operations could leave you with a corrupted mdb file.
> > >
> > > More info:
> > >
> > > http://databases.aspfaq.com/database/what-are-the-limitation s-of-ms-access.html
> > >
> > >
> > > --
> > > Mike Brind

Re: Connecting to an Access database

A corrupted mdb file will prevent anyone writing to it, and would
generate error messages (such as I outlined earlier) as a result of any
attempt in code to open a connection to the database.

--
Mike Brind

brian.lukoff@gmail.com wrote:
> OK. Am I correct in saying that if the database is corrupt I would
> know because users would be getting errors trying to access the
> database from ASP pages? (I'm just trying to make sure that there
> couldn't be a situation where the ASP pages accessing/writing to the
> database appear to be working fine, but the database contains incorrect
> values because of some sort of corruption.)
>
> Mike Brind wrote:
> > Depending on the level of corruption you could get any of the
> > following:
> >
> > Microsoft JET Database Engine error '80004005'
> > Unrecognized database format '.mdb'
> >
> > or
> >
> > Microsoft JET Database Engine error '80004005'
> > Cannot Open Database. It may not be a database that your application
> > recognizes, or the file may be corrupt.[1]
> >
> > These may be reparable by running JRO commands to compact and repair
> > the database remotely or downloading the file, opening it and running
> > compact and repair, then upload it again.
> >
> > Or you may get the very much more serious:
> >
> > Catastrophic Failure[2]
> >
> > on every page that accesses the db. If you are lucky, you may be able
> > to download the file, open it and export all the objects and data to a
> > new file, and upload that. If you are not, you will have to rely on
> > the last back up. If you have no backup, you may be able to get a
> > specialist to recover the file, then again you might not.
> >
> > Bear in mind that backups of mdb files are normally done as a scheduled
> > task to copy the file over at a predetermined time. If the file is
> > locked (open) at the time the task runs, you maight not get a copy
> > done, so your actual backup might be a day or more out of date.
> >
> > [1] I used to get this regularly on Monday mornings with an Access 97
> > database and a DSN connection. The database was primarily read-only,
> > and moving to OLEDB and Access 2000 solved that problem.
> >
> > [2] This is the one that got me migrating to SQL Server.
> >
> > --
> > Mike Brind
> >
> > brian.lukoff@gmail.com wrote:
> > > This may be a silly question, but does a corrupted MDB file just mean
> > > that I won't be able to open the file (or will receive errors when I
> > > do), or could I be left with a database that can be read without errors
> > > but that contains incorrect values? (If so, would the user that caused
> > > such a problem have received an error from their web browser when it
> > > occurred?)
> > >
> > > Mike Brind wrote:
> > > > brian.lukoff@gmail.com wrote:
> > > > > In a KB article (http://support.microsoft.com/kb/299974/), it is
> > > > > stated:
> > > > > "When you run Microsoft Jet in an IIS environment, it is recommended
> > > > > that you use the native Jet OLE DB Provider in place of the Microsoft
> > > > > Access ODBC driver. The Microsoft Access ODBC driver (Jet ODBC driver)
> > > > > can have stability issues due to the version of Visual Basic for
> > > > > Applications that is invoked because the version is not thread safe. As
> > > > > a result, when multiple concurrent users make requests of a Microsoft
> > > > > Access database, unpredictable results may occur....While Microsoft Jet
> > > > > is consciously (and continually) updated with many quality, functional,
> > > > > and performance improvements, it was not intended (or architected) for
> > > > > the high-stress performance required by 24x7 scenarios, ACID
> > > > > transactions, or unlimited users, that is, scenarios where there has to
> > > > > be absolute data integrity or very high concurrency."
> > > > >
> > > > > In basic usage (just simple retrieving of rows or tables, and executing
> > > > > INSERT or UPDATE statements), what "unpredictable results" are possible
> > > > > that could affect data integrity?
> > > >
> > > >
> > > > You won't get too many problems with SELECT statements, apart from Jet
> > > > eventually running out of resources in a high-user environment. Having
> > > > said that, I have an Access 2000 db humming along quite nicely serving
> > > > up over 1/2 a million recordsets a month, and has done so for a few
> > > > years with no problems.
> > > >
> > > > INSERTS and UPDATES are the potential killer for Jet. Incomplete
> > > > operations could leave you with a corrupted mdb file.
> > > >
> > > > More info:
> > > >
> > > > http://databases.aspfaq.com/database/what-are-the-limitation s-of-ms-access.html
> > > >
> > > >
> > > > --
> > > > Mike Brind

Re: Connecting to an Access database

A bit more about corrupted mdb files:

http://support.microsoft.com/kb/q209137/

--
Mike Brind

Mike Brind wrote:
> A corrupted mdb file will prevent anyone writing to it, and would
> generate error messages (such as I outlined earlier) as a result of any
> attempt in code to open a connection to the database.
>
> --
> Mike Brind
>
> brian.lukoff@gmail.com wrote:
> > OK. Am I correct in saying that if the database is corrupt I would
> > know because users would be getting errors trying to access the
> > database from ASP pages? (I'm just trying to make sure that there
> > couldn't be a situation where the ASP pages accessing/writing to the
> > database appear to be working fine, but the database contains incorrect
> > values because of some sort of corruption.)
> >
> > Mike Brind wrote:
> > > Depending on the level of corruption you could get any of the
> > > following:
> > >
> > > Microsoft JET Database Engine error '80004005'
> > > Unrecognized database format '.mdb'
> > >
> > > or
> > >
> > > Microsoft JET Database Engine error '80004005'
> > > Cannot Open Database. It may not be a database that your application
> > > recognizes, or the file may be corrupt.[1]
> > >
> > > These may be reparable by running JRO commands to compact and repair
> > > the database remotely or downloading the file, opening it and running
> > > compact and repair, then upload it again.
> > >
> > > Or you may get the very much more serious:
> > >
> > > Catastrophic Failure[2]
> > >
> > > on every page that accesses the db. If you are lucky, you may be able
> > > to download the file, open it and export all the objects and data to a
> > > new file, and upload that. If you are not, you will have to rely on
> > > the last back up. If you have no backup, you may be able to get a
> > > specialist to recover the file, then again you might not.
> > >
> > > Bear in mind that backups of mdb files are normally done as a scheduled
> > > task to copy the file over at a predetermined time. If the file is
> > > locked (open) at the time the task runs, you maight not get a copy
> > > done, so your actual backup might be a day or more out of date.
> > >
> > > [1] I used to get this regularly on Monday mornings with an Access 97
> > > database and a DSN connection. The database was primarily read-only,
> > > and moving to OLEDB and Access 2000 solved that problem.
> > >
> > > [2] This is the one that got me migrating to SQL Server.
> > >
> > > --
> > > Mike Brind
> > >
> > > brian.lukoff@gmail.com wrote:
> > > > This may be a silly question, but does a corrupted MDB file just mean
> > > > that I won't be able to open the file (or will receive errors when I
> > > > do), or could I be left with a database that can be read without errors
> > > > but that contains incorrect values? (If so, would the user that caused
> > > > such a problem have received an error from their web browser when it
> > > > occurred?)
> > > >
> > > > Mike Brind wrote:
> > > > > brian.lukoff@gmail.com wrote:
> > > > > > In a KB article (http://support.microsoft.com/kb/299974/), it is
> > > > > > stated:
> > > > > > "When you run Microsoft Jet in an IIS environment, it is recommended
> > > > > > that you use the native Jet OLE DB Provider in place of the Microsoft
> > > > > > Access ODBC driver. The Microsoft Access ODBC driver (Jet ODBC driver)
> > > > > > can have stability issues due to the version of Visual Basic for
> > > > > > Applications that is invoked because the version is not thread safe. As
> > > > > > a result, when multiple concurrent users make requests of a Microsoft
> > > > > > Access database, unpredictable results may occur....While Microsoft Jet
> > > > > > is consciously (and continually) updated with many quality, functional,
> > > > > > and performance improvements, it was not intended (or architected) for
> > > > > > the high-stress performance required by 24x7 scenarios, ACID
> > > > > > transactions, or unlimited users, that is, scenarios where there has to
> > > > > > be absolute data integrity or very high concurrency."
> > > > > >
> > > > > > In basic usage (just simple retrieving of rows or tables, and executing
> > > > > > INSERT or UPDATE statements), what "unpredictable results" are possible
> > > > > > that could affect data integrity?
> > > > >
> > > > >
> > > > > You won't get too many problems with SELECT statements, apart from Jet
> > > > > eventually running out of resources in a high-user environment. Having
> > > > > said that, I have an Access 2000 db humming along quite nicely serving
> > > > > up over 1/2 a million recordsets a month, and has done so for a few
> > > > > years with no problems.
> > > > >
> > > > > INSERTS and UPDATES are the potential killer for Jet. Incomplete
> > > > > operations could leave you with a corrupted mdb file.
> > > > >
> > > > > More info:
> > > > >
> > > > > http://databases.aspfaq.com/database/what-are-the-limitation s-of-ms-access.html
> > > > >
> > > > >
> > > > > --
> > > > > Mike Brind

Re: Connecting to an Access database

> You won't get too many problems with SELECT statements, apart from Jet
> eventually running out of resources in a high-user environment. Having
> said that, I have an Access 2000 db humming along quite nicely serving
> up over 1/2 a million recordsets a month, and has done so for a few
> years with no problems.

If there is a problem with a SELECT statement, would it necessarily
manifest itself as users receiving errors when they try to access
pages, or could users get no errors but just incorrect/incomplete data
from a query?

I have a number of web apps that are running on Access that seem to
have been running OK, and I'm wondering if I can assume that all is
well if users aren't receiving any errors.

Brian

Re: Connecting to an Access database

brian.lukoff@gmail.com wrote:
> > You won't get too many problems with SELECT statements, apart from Jet
> > eventually running out of resources in a high-user environment. Having
> > said that, I have an Access 2000 db humming along quite nicely serving
> > up over 1/2 a million recordsets a month, and has done so for a few
> > years with no problems.
>
> If there is a problem with a SELECT statement, would it necessarily
> manifest itself as users receiving errors when they try to access
> pages, or could users get no errors but just incorrect/incomplete data
> from a query?

Depends on what you mean by problem. If you mean as a result of
corruption, the file cannot be opened. If the SELECT statement works,
then the suspect/corruption flag hasn't been set. Some people think of
Access as like the miner's canary. In a desktop/LAN environment, it is
often the first thing that indicates there are network problems due to
its sensitivity in setting the suspect/corruption flag.

>
> I have a number of web apps that are running on Access that seem to
> have been running OK, and I'm wondering if I can assume that all is
> well if users aren't receiving any errors.
>

I would.

--
Mike Brind

Re: Connecting to an Access database

> > If there is a problem with a SELECT statement, would it necessarily
> > manifest itself as users receiving errors when they try to access
> > pages, or could users get no errors but just incorrect/incomplete data
> > from a query?
>
> Depends on what you mean by problem.

My concern is with "silent" problems (in general, not just with SELECT
statements) where users aren't experiencing any errors but the web page
is returning incorrect or incomplete data (or the database file
contains incorrect data as the result of commands sent from an ASP
script). I guess my question is this: are there any problems like this
that could occur because of connecting to an Access database instead of
a SQL Server database in ASP? (Or do all problems that come from using
Access instead of SQL Server manifest themselves in the form of errors
that would be sent to the user?)