ASP, Access, Permissions, and an Exicting Sudden Error 1

[Genimuse]

I've got an intranet running for a client, simple enough little thing running on Win2k Server and MS Access 2000.

Database access is light, and has worked like a champ for many months.

Suddenly I have a problem: After a small number of writes to the database -- it can read forever, after 1 or 2 inserts, updates, or deletes -- I receive a good ole 80004005 error, specifically:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file 'MyPathAndFileNameIsHere.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

Searches here and on Google indicate that it's a permissions problem, but (a) the permissions hadn't changed, and (b) I gave Everyone and IUSR and IWAM total control over the entire drive (and yes, made sure that included the directory the db is in, the db, and temp folders), but the problem remains.

The database isn't open any other way (like in InterDev or in Access or anything).

My connection is directly to the file, it doesn't use a DSN (as you can probably tell from the error).

I updated to Jet 4.0. I updated to MDAC 2.7. I've tweaked every little thing I can possibly think of. Help! The only way to recover is to restart the server. Which, unsurprisingly, isn't a very satisfactory solution.

Any ideas on what I could be missing, what I might not have thought to look for?

Oh, also, I haven't had any Windows Updates run in 2 weeks, and the problem is only a few days old, so no changes there, either.

Thanks in advance.

 

[DotNetGnat]

how about doing a compant and repair on the access database and restart the machine and see if that helps...

-DNG

 

[DotNetGnat]

also check the permissions for system.mdw file...

-DNG

 

[Sheco]

This is a longshot but is there an ADO object (recordset, connection, command, etc?) stored in an application or session variable?

Do recordsets and connections get closed and set to nothing when you're done with them?

 

[DotNetGnat]

Good One there Sheco. Definitely unclosed recordset and connection objects are suspicious items in this situation...

-DNG

 

[Genimuse]

Repaired & Compacted with no help.

I did check for closes and dumping the objects everywhere. I'll check again, however, as there's quite a lot of code.

How this suddenly evolved with no code or system changes, though... grr.

Thanks, I'll take a look at the code again, and at the system.mdw file.

 

[Sheco]

When it used to run like a champ was it read only then?

Is it only recently that the ability to insert or update records was added and the problem is new since those changes?

 

[Genimuse]

Nope. Had the very same inserts, updates, and deletes. In fact, nothing about the app has changed... :-(

I don't see any unclosed recordsets or connections anywhere.

 

[DotNetGnat]

by the error you posted...i dont think of any other reasons...here is what i gleaned online...make sure you have taken care of each step

There are several causes for this error message: 

1.The account that Microsoft Internet Information Server (IIS) is using (which is usually IUSR) does not have the correct Windows NT permissions for a file-based database or for the folder that contains the file. 

2.The file and the data source name are marked as Exclusive. 

3.Another process or user has the Access database open. 

4.The error may be caused by a delegation issue. Check the authentication method (Basic versus NTLM), if any. If the connection string uses the Universal Naming Convention (UNC), try to use Basic authentication or an absolute path such as C:\Mydata\Data.mdb. This problem can occur even if the UNC points to a resource that is local to the IIS computer. 

5.This error may also occur when you access a local Microsoft Access database that is linked to a table where the table is in an Access database on a network server.

-DNG

 

[Genimuse]

1.The account that Microsoft Internet Information Server (IIS) is using (which is usually IUSR) does not have the correct Windows NT permissions for a file-based database or for the folder that contains the file.

Check.

2.The file and the data source name are marked as Exclusive.

How is such a thing marked as Exclusive? I have no DSN involved, so it's not that... is there some way that files are marked Exclusive?

3.Another process or user has the Access database open.

Check.

4.The error may be caused by a delegation issue. Check the authentication method (Basic versus NTLM), if any. If the connection string uses the Universal Naming Convention (UNC), try to use Basic authentication or an absolute path such as C:\Mydata\Data.mdb. This problem can occur even if the UNC points to a resource that is local to the IIS computer.

I don't quite understand this (not an OS pro). It uses Basic authentication, and the database path is absolute. The database is local to the IIS server.... What's a delegation issue?

5.This error may also occur when you access a local Microsoft Access database that is linked to a table where the table is in an Access database on a network server.

There are indeed linked tables involved... the database is split into databasename.mdb and databasename_be.mdb, which are on the same server (in the same directory). Updating links in Access has no effect on the error. This is a network server. Is this saying that I can't have linked tables, even if they're in the same directory on the same drive? They've been split for months without a problem, so I'm thinking this isn't it, but I'm uncertain of what this point actually means. Any thoughts?

 

[oharab]

I would try restarting IIS. This will free up any locked connections.
I would then look at writing a log every time the db is connected to or disconnected from. That way you can see if there are any connections left hanging.
In my ASP pages I have an include at the top of the page that opens a connection, and a second include that closes it. That way it's always closed (assuming there's not an error mid page!)

Just my thoughts.

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------------

 

[Genimuse]

Stopping and starting IIS doesn't bring it back... which logically indicates, then, that it's the database that's having trouble, yes?

Here's a new wrinkle: The system does recover eventually, I've learned. After about 5 minutes or so, it's back in business again, so something's timing out. I'd be led to believe that it IIS if stopping and restarting it would fix the problem.

 

[Genimuse]

Well, I continue to seek any ideas.

I added logging to every single open, close, and execute. It's not leaving anything open, it's not closing anything in mixed order (like Open A, Open B, Close A, Close B), and nothing is open when the error is thrown.

I've now verified that if the Administrator of the server runs the application, it will never, ever cause the problem. If anyone else runs it, it will always cause the problem after a few inserts, updates, or deletes.

To try a permissions thing, I gave the whole website folder specific full control to a given machine and login (just in case). It still has the problem.

Any ideas as to why the Administrator can't make the problem occur?

And thanks very much for all of the suggestions so far.

 

[Genimuse]

(To clarify, I wrote "nothing is open when the error is thrown," but that's not accurate. It opens a standard connection but fails on executing the insert/update/delete.)

 

[Sheco]

Maybe it is a row locking issue? Are you using pessimistic?

 

[SatishPutcha]

Did you give the required permissions to "Everyone"?

Satish

 

[Genimuse]

All optimistic locking. "Everyone" has Full Control (which I normally wouldn't want, but have set it this way to try to see if it fixes the problem).

Here's a theory I came up with: Since the database is split, when I access the database by its front-end name (database.mdb) I'm piping all my requests through that front-end to back-end connection. If I instead access the back-end file (database_be.mdb), there won't be a connection limit imposed by that link. Duh.

So... since I'm using several queries and parameterized queries, can I move those to the back end? Guess I should ask that in the Access forum, but in case anyone knows....

Thanks for the great thoughts!

 

[Genimuse]

Ok, so here's the lesson for everyone:

If you split a Microsoft Access database into a front-end and a back-end, be sure to point your ASP application to the BACK-END. If you have queries in the front-end that you need to use, import them into the back-end.

Effectively, everything was going through one little front-end to back-end link connection, which unsurprisingly, is easy to jam up.

D'oh!

Thanks again for everyone's suggestions.

 

[Sheco]

Does the ODBC driver know what to do when you've got linked databases like that or does the access run in the background?

 

[Genimuse]

When you split an Access database, it basically turns it into two files. By default the front-end file is called YourDatabase.mdb and the back-end files is called YourDatabase_be.mdb.

My connection string, prior to the split, correctly pointed to YourDatabase.mdb. After the split nothing changed in the connection string, of course (nor would it auto-change in a DSN), so now ASP was accessing the database through the front-end file. Which, really, just points to the back-end file whenever a table is accessed.

That linked table connection appears to have some severe access limits, and after some number of "live" link requests between the two, it just poops out and the old links have to time out.

I'm using the Microsoft Access Driver in my connection string, mind you, and not ODBC. It's possible that splitting the database would just break ODBC, or that it would do the same thing as the Microsoft Access Driver does. I'm not certain. Access, however, does not run in the background, that I'm certain of (based on checking the running processes many times while trying to debug this ).