Using Access Database while Internet Users use DB simultaneously

[Marcus S]

I have an msAccess database back-end ( .mdb or .accdb both formats
work ) set up with .asp pages. An msAccess front-end links to the
msAccess back-end. The front-end is what my co-workers will use to
edit the database. With the .asp pages web users are able to access
the back-end at the same time the msAccess front-end is being used,
its great. I am trying to convert to .aspx and its works great on the
web-side as long as the msAccess front-end is not open, otherwise I
get a "file already in use error" from the .aspx page.
The .asp, .aspx, and the ( .mdb or .accdb [format not the issue
here] ) files are located in the same directory with the same
permissions (including IIS_WPG and IUSR) where a simultaneously
accessible .asp / ( .mdb or .accdb ) solution is actively working. How
but once I make it an .aspx / ( .mdb or .accdb ) combo it is only
accessible by .aspx while no msAccess users are using the ( .mdb
or .accdb ). So what sort of record locking (.ldb / .lccdb) enabler
is missing from my .aspx implementation?

 

[Arne Vajhøj]

I have an msAccess database back-end ( .mdb or .accdb both formats
work ) set up with .asp pages. An msAccess front-end links to the
msAccess back-end. The front-end is what my co-workers will use to
edit the database. With the .asp pages web users are able to access
the back-end at the same time the msAccess front-end is being used,
its great. I am trying to convert to .aspx and its works great on the
web-side as long as the msAccess front-end is not open, otherwise I
get a "file already in use error" from the .aspx page.
The .asp, .aspx, and the ( .mdb or .accdb [format not the issue
here] ) files are located in the same directory with the same
permissions (including IIS_WPG and IUSR) where a simultaneously
accessible .asp / ( .mdb or .accdb ) solution is actively working. How
but once I make it an .aspx / ( .mdb or .accdb ) combo it is only
accessible by .aspx while no msAccess users are using the ( .mdb
or .accdb ). So what sort of record locking (.ldb / .lccdb) enabler
is missing from my .aspx implementation?

How does the connection string look in ASP and in ASP.NET?

I believe there are differences between IIS/ASP.NET versions,
but at least in some cases ASP run as IUSR_xxxx while ASP.NET runs
as ASPNET !

Arne

 

[Marcus S]

I have an msAccess database back-end ( .mdb or .accdb both formats
work ) set up with .asp pages.  An msAccess front-end links to the
msAccess back-end.  The front-end is what my co-workers will use to
edit the database.  With the .asp pages web users are able to access
the back-end at the same time the msAccess front-end is being used,
its great.  I am trying to convert to .aspx and its works great on the
web-side as long as the msAccess front-end is not open, otherwise I
get a "file already in use error" from the .aspx page.
The .asp, .aspx, and the ( .mdb or .accdb [format not the issue
here] ) files are located in the same directory with the same
permissions (including IIS_WPG and IUSR) where a simultaneously
accessible .asp / ( .mdb or .accdb ) solution is actively working. How
but once I make it an .aspx /  ( .mdb or .accdb ) combo it is only
accessible by .aspx while no msAccess users are using the ( .mdb
or .accdb ).  So what sort of record locking (.ldb / .lccdb) enabler
is missing from my .aspx implementation?

How does the connection string look in ASP and in ASP.NET?

I believe there are differences between IIS/ASP.NET versions,
but at least in some cases ASP run as IUSR_xxxx while ASP.NET runs
as ASPNET !

Arne

..asp Connection String:
sConnString="PROVIDER=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & Server.MapPath("xxxxxxx.mdb")

..aspx Connection String:
sConnString="PROVIDER=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & Server.MapPath("xxxxxxx.mdb")


So connection strings are identical.

IIS/ASP.NET version is 2.0

Could not find "ASPNET" user on my domain.

 

[Arne Vajhøj]

I have an msAccess database back-end ( .mdb or .accdb both formats
work ) set up with .asp pages. An msAccess front-end links to the
msAccess back-end. The front-end is what my co-workers will use to
edit the database. With the .asp pages web users are able to access
the back-end at the same time the msAccess front-end is being used,
its great. I am trying to convert to .aspx and its works great on the
web-side as long as the msAccess front-end is not open, otherwise I
get a "file already in use error" from the .aspx page.
The .asp, .aspx, and the ( .mdb or .accdb [format not the issue
here] ) files are located in the same directory with the same
permissions (including IIS_WPG and IUSR) where a simultaneously
accessible .asp / ( .mdb or .accdb ) solution is actively working. How
but once I make it an .aspx / ( .mdb or .accdb ) combo it is only
accessible by .aspx while no msAccess users are using the ( .mdb
or .accdb ). So what sort of record locking (.ldb / .lccdb) enabler
is missing from my .aspx implementation?

How does the connection string look in ASP and in ASP.NET?

I believe there are differences between IIS/ASP.NET versions,
but at least in some cases ASP run as IUSR_xxxx while ASP.NET runs
as ASPNET !

.asp Connection String:
sConnString="PROVIDER=Microsoft.ACE.OLEDB.12.0;"& _
"Data Source="& Server.MapPath("xxxxxxx.mdb")

.aspx Connection String:
sConnString="PROVIDER=Microsoft.ACE.OLEDB.12.0;"& _
"Data Source="& Server.MapPath("xxxxxxx.mdb")

So connection strings are identical.

IIS/ASP.NET version is 2.0

Could not find "ASPNET" user on my domain.

Those connection strings are literally identical.

ASPNET is a local account.

I checked the docs - it should only be there for
Win 2000 & XP IIS 5.x - newer versions should
use the network service account.

But I don't know if that is relevant for your problem.

Arne

 

[Marcus S]

On 21-01-2011 19:23, Marcus S wrote:
I have an msAccess database back-end ( .mdb or .accdb both formats
work ) set up with .asp pages.  An msAccess front-end links to the
msAccess back-end.  The front-end is what my co-workers will use to
edit the database.  With the .asp pages web users are able to access
the back-end at the same time the msAccess front-end is being used,
its great.  I am trying to convert to .aspx and its works great on the
web-side as long as the msAccess front-end is not open, otherwise I
get a "file already in use error" from the .aspx page.
The .asp, .aspx, and the ( .mdb or .accdb [format not the issue
here] ) files are located in the same directory with the same
permissions (including IIS_WPG and IUSR) where a simultaneously
accessible .asp / ( .mdb or .accdb ) solution is actively working. How
but once I make it an .aspx /  ( .mdb or .accdb ) combo it is only
accessible by .aspx while no msAccess users are using the ( .mdb
or .accdb ).  So what sort of record locking (.ldb / .lccdb) enabler
is missing from my .aspx implementation?
How does the connection string look in ASP and in ASP.NET?
I believe there are differences between IIS/ASP.NET versions,
but at least in some cases ASP run as IUSR_xxxx while ASP.NET runs
as ASPNET !

.asp Connection String:
sConnString="PROVIDER=Microsoft.ACE.OLEDB.12.0;"&  _
"Data Source="&  Server.MapPath("xxxxxxx.mdb")

.aspx Connection String:
sConnString="PROVIDER=Microsoft.ACE.OLEDB.12.0;"&  _
"Data Source="&  Server.MapPath("xxxxxxx.mdb")

So connection strings are identical.

IIS/ASP.NET version is 2.0

Could not find "ASPNET" user on my domain.

Those connection strings are literally identical.

ASPNET is a local account.

I checked the docs - it should only be there for
Win 2000 & XP IIS 5.x - newer versions should
use the network service account.

But I don't know if that is relevant for your problem.

Arne

I'm running server 2003 64-bit with IIS 6.0 so it was the network
service account.

Thank you, I have solved this problem with your help.

http://msdn.microsoft.com/en-us/library/ff647402.aspx

FYI: Posting the above link for others in the future. It got my aspx
executing while msAccess was also open. thanks again, Arne.

 

[Brian Cryer]

I have an msAccess database back-end ( .mdb or .accdb both formats
work ) set up with .asp pages. An msAccess front-end links to the
msAccess back-end. The front-end is what my co-workers will use to
edit the database. With the .asp pages web users are able to access
the back-end at the same time the msAccess front-end is being used,
its great. I am trying to convert to .aspx and its works great on the
web-side as long as the msAccess front-end is not open, otherwise I
get a "file already in use error" from the .aspx page.
The .asp, .aspx, and the ( .mdb or .accdb [format not the issue
here] ) files are located in the same directory with the same
permissions (including IIS_WPG and IUSR) where a simultaneously
accessible .asp / ( .mdb or .accdb ) solution is actively working. How
but once I make it an .aspx / ( .mdb or .accdb ) combo it is only
accessible by .aspx while no msAccess users are using the ( .mdb
or .accdb ). So what sort of record locking (.ldb / .lccdb) enabler
is missing from my .aspx implementation?

Sorry, I can't answer your specific question

But, when I'm recruiting and interviewing software engineers its one of
my standard questions to ask why is MS-Access not suitable as a database
for a website. The bottom line is that MS-Access (which is great for
what it is) isn't suitable for a website becasue its single threaded,
meaning that if one process is doing a query or an insert then it blocks
everything else. So its fine for developing a simple site with (not that
I would) and I suppose for very low traffic sites, but not it you have
more than a small handful of concurrent visitors. So in the mid to long
term you would experience much better performance by switching to
Sql-Server or MySQL.

With your problem in hand, what is the error you are getting or is it
just locking up? If its locking then its almost certainly down to the
single threaded nature of MS-Access - so check to ensure that all your
connections are closed and you haven't left any queries open. If you get
an error then what it is?

Hope this helps.

 

[Jason Keats]

Sorry, I can't answer your specific question

But, when I'm recruiting and interviewing software engineers its one of
my standard questions to ask why is MS-Access not suitable as a database
for a website. The bottom line is that MS-Access (which is great for
what it is) isn't suitable for a website becasue its single threaded,
meaning that if one process is doing a query or an insert then it blocks
everything else. So its fine for developing a simple site with (not that
I would) and I suppose for very low traffic sites, but not it you have
more than a small handful of concurrent visitors. So in the mid to long
term you would experience much better performance by switching to
Sql-Server or MySQL.

Brian, a Jet database can have up to 255 concurrent users. If you don't
maintain a database connection for each user, then it is possible to
support many more concurrent users.

The judicious use of disconnected ADO recordsets, to help minimise
connection time, along with ASP.NET's ability to queue requests make it
quite possible to support several hundred concurrent users - which is as
many as most ASP.NET sites can support anyway.

However, it is true that MSSQL (or any another true database server) is
likely to perform better and to be more reliable - due to it's log
files, etc.

 

[Brian Cryer]

Brian, a Jet database can have up to 255 concurrent users. If you don't
maintain a database connection for each user, then it is possible to
support many more concurrent users.

True. Although I still think that Access is single threaded - but I
acknowledge that I confused the boundaries between Jet (driver) and
Access (application).

The judicious use of disconnected ADO recordsets, to help minimise
connection time, along with ASP.NET's ability to queue requests make it
quite possible to support several hundred concurrent users - which is as
many as most ASP.NET sites can support anyway.

However, it is true that MSSQL (or any another true database server) is
likely to perform better and to be more reliable - due to it's log
files, etc.

Agreed.

I don't suppose Microsoft have made any progress on allowing space to be
reclaimed, to avoid the need for a compact operation?

 

[Arne Vajhøj]

But, when I'm recruiting and interviewing software engineers its one of
my standard questions to ask why is MS-Access not suitable as a database
for a website. The bottom line is that MS-Access (which is great for
what it is) isn't suitable for a website becasue its single threaded,
meaning that if one process is doing a query or an insert then it blocks
everything else.

I can not but wonder about what you hire.

The above is not correct.

The LDB file is specifically there to handle that problem.

So its fine for developing a simple site with (not that
I would) and I suppose for very low traffic sites, but not it you have
more than a small handful of concurrent visitors. So in the mid to long
term you would experience much better performance by switching to
Sql-Server or MySQL.

SQLServer and MySQL can handle a lot more concurrent users. LDB file
works but is not as efficient as the in memory locks in a database
server.

Arne

 

[Arne Vajhøj]

True. Although I still think that Access is single threaded

It is not. If that was the case then the LDB file would
not be needed.

Arne

 

[Brian Cryer]

It is not. If that was the case then the LDB file would
not be needed.

Arne

When you run a query in Access you can't then run a second query until
the results of the first have come back. That's because its single threaded.

This is a separate issue from whether you can open two separate
instances - as Jason has already covered.

 

[Brian Cryer]

I can not but wonder about what you hire.

The above is not correct.

Yes, I've been corrected on that by Jason.

What I actually ask is why Access isn't suitable as a back end database
for a website. I was wrong on the locking issue but this is only part of
the answer.

The LDB file is specifically there to handle that problem.


SQLServer and MySQL can handle a lot more concurrent users. LDB file
works but is not as efficient as the in memory locks in a database
server.

Its not just locks which makes Access a poor choice. Its less resilient,
doesn't scale well and needs to be compacted (i.e. downtime), and I'm
sure if you think about it you should be able to add to that list.

Access is a good choice in some scenarios (it even has some advantages
over SQL-Server and MySQL), but I wouldn't advocate its use for a website.