Could not use '(unknown)'; file already in use

Question

We have the problem, which I think should be easily resolvable, but just not sure how. We have the Windows 2003 Server with the Classic ASP application on it. The MSAccess database is used for data storage.

The application is designed in the way that it has a lot of includes.

The .asp page may open the DB connection and close it in the end of the page. At the same time the include that is included on the page may have the same database open as well.

The problem is that on the second open we're getting an error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] Could not use '(unknown)'; file already in use

This is on our local configuration only - we don't own that project, but are helping customer to fix some items. Everything works just perfect on Customer side. When we set the read-only access to the database this error is not shown. So, it seems that the ADODB.Connection is opening the database in exclusive mode.

Anyone has any ideas how to fix that?

Thanks

Solution 1

Ok. The issue was resolved. As I was saying in the question - the resolution was VERY SIMPLE. Instead of setting the IUSR_* Write permission to MDB file only, it was necessary to set the Write permission to the whole folder where the MDB file is stored. Strange, because I don't see any additional files created (meaning, like .ldb file, which is created during an exclusive DB open). But, it works now!

Solution 2

As far as I remember from using Access in my first classic ASP days you can't have more than one connection opened in the same time pointing to the same physical .mdb file because once it's opened, it's locked. That's what I learned the hard way.

The solution for me was using one single Connection. Having very small website to manage, I just used Application level connection object, created and opened once in global.asa then used in all pages.
However this is not good for ordinary websites so you can write file called "database.asp" in there create and open the connection then include that file and use the connection object in your code, not forgetting to close it in the end of every page.

Hope this makes sense, I'll clarify if needed.

Comments

[Denis Mazourick]

as I noted above, it works in production well. Also, I remember other project we've developed around 9 years ago in Classic ASP and it worked there also. So it should not be the case. Also, opening the "Application level" connection is not a good practice.

[Shadow The Kid Wizard]

I know it's not good practice, but using only one connection is.

[Denis Mazourick]

I resolved that - it was necessary to put the Write permission on the WHOLE folder where the MDB file is located, not only to the MDB file.

[David-W-Fenton]

With a file-based database engine like Access/Jet/ACE, using a single connection makes a great deal of sense. It's the default way of doing things. Opening and closing connections repeatedly is a practice you'd use with a server database engine, but is actually a huge performance drain with Jet/ACE because of the overhead of creating the record locking file.

[Denis Mazourick]

@David-W-Fenton - the single connection can be used ONLY in the case of a single process working with the DB. The ODBC/OLEDB may have an internal state for the connection and when you will use the same connection from multiple threads you may be in a big trouble. Moreover, the lock file is not create in the case of non-exclusive access. I'm not sure why we needed the full write permission for the folder, but no additional files were created there.

[David-W-Fenton]

I recommend against using a Jet/ACE data store behind a web server. MS recommends against it and it wasn't designed for that environment. As to full write permission, the locking file gets created with any non-exclusive access, so I'm not certain what you mean. I would get away from using Jet/ACE behind a web server and use a database engine designed for use in that environment instead.