Linked Tables and Permissions

[Genimuse]

I have an ASP application with an Access backend. I've hashed through this problem over in the ASP forum, and have concluded that the issue most likely lies with Access.

Access 2000
IIS 5.0

Here's the situation: I have a small intranet with ASP in the front and Access in the back. It's low-traffic, with the vast majority of the Access use being either simple SELECTs or simple parameterized SELECT queries. This part works beautifully.

However, when performing actions that modify the database -- INSERTs, UPDATEs, or DELETEs -- a problem occurs. After one or two, or even sometimes three of these actions in a row, I receive an ASP error telling me that the database is elsewise opened for exclusive access. Now I know I'd get that error if I opened the database in Access while someone was trying to access it via ASP, but in this case the database is definitely not open.

Within ASP connections are made to the database, but they are always closed, every time, so I don't believe anything's being left open there.

The database was, for many months, just one file, but a month or so ago I split it so that I could work on front-end stuff (forms and such) while others accessed the data. This seemed to work fine for a couple of weeks, but for the last couple of weeks this error has been cropping up.

The first thing to blame, from an ASP perspective (besides not closing connections) is a permissions problem. In order to ensure that this isn't the case, both the front end (e.g. database.mdb) and the back end (e.g. database_be.mdb) are (a) in the same directory, on the same drive, and (b) allow full access to Administrators, Everyone, and IUSR and IWAM (the web server).

However, it's true that about two weeks ago I took the database to my home office over the weekend and returned it to the client's place, at which point I had to go in and relink the tables. This seemed seamless and easy enough, and doesn't seem like a source of the problem, but I mention it just in case.

Here's one more clue: the problem fixes itself after a certain amount of time, something like 5 to 10 minutes. Whatever is holding the connection open or throwing the permissions error (ASP doesn't know which is the problem) eventually times out, it seems.

And one last clue: I cannot reproduce this error by using the intranet from the server it's on while logged in as the administrator -- I can insert, update, and delete to my heart's content, never throwing the error. But the minute I try it from any other computer on the network, POOF, after 1 to 3 modifications the error is thrown.

I'm posting this here because I don't know much about Access's permissions, especially when it comes to linked tables. Is there something I should know, or something that comes to mind?

 

[smedvid]

Just couple of thoughts off top of my head... hth

1. U set the Timeout your ADO Connections?
DBConn.ConnectionTimeout = 0 'for Safety
DBConn.CommandTimeout = 0 'Safety

2. I've had cases where I need to add a Delay of a second or so to allow the database to catch up and commit my action queries. I created a little Delay function to that end. It solved unexplainable issues in the past. Even a DelayTime 1 helped in some cases.

Public Function DelayTime(PauseTime As Double)
'***************************
'* Function will delay processing by seconds passed in.
'* This is required for some MS Access/SQL Server Processing
'* and screen updating/refreshing.
'***************************
Dim start
start = Timer ' Set start time.
Do While Timer < start + PauseTime
DoEvents ' Yield to other processes.
Loop
End Function




Steve Medvid
&quot;IT Consultant & Web Master&quot;

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[Genimuse]

Thank you for the suggestions.

The answer -- unfortunately it should have been pretty obvious to me -- was to point ASP to the back-end database instead of the front-end, importing over the queries I use. It appears that the Microsoft Access Driver in ADODB is more than happy to talk to a front-end database, passing all of those table queries through the link, but only so often, with a lengthy timeout.

This should have been obvious, I suppose. The thing that I DID in fact do shortly before the problem occurred, but that I'd forgotten about, was split the database.

So the lesson: don't point ADODB connections to a front end database. Duh.