I'm having a confusing problem with an ASP getting to an Access database.  

Testing under Personal Web Server yields NO errors, but when I move it to 

IIS I get a "file is already in use" error message.



For our purposes, my page opens two different tables in one database. I 

have two objects, LHDB (Connection object) and LHQryRes (Recordset object). 

My code is very straightforward, and conceptually does this:



connectstring = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=report.mdb"

set LHDB = Server.CreateObject("ADODB.Connection")

LHDB.open connectstring



set LHQryRes = Server.CreateObject("ADODB.Recordset")

sqlstring = "select * from table1 where DeptID = '001'"

LHQryRes.open sqlstring, LHDB, adOpenDynamic, adLockOptimistic , adCmdText



(display an HTML form with info from DB - this part works fine)



LHQryRes.close

LHDB.close



connectstring = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=report.mdb"

set LHDB = Server.CreateObject("ADODB.Connection")

LHDB.open connectstring [[ ERROR HAPPENS HERE!]]

- - - - - - - - - -

Microsoft OLE DB Provider for ODBC Drivers error '80004005' 

[Microsoft][ODBC Microsoft Access Driver] Could not use '(unknown)'; file 

already in use. 

/dph/local_health/DOHSurvey2001BoardOfHealth.asp, line 295

- - - - - - - - - -



(these lines would be next, but don't execute)

set LHQryRes = Server.CreateObject("ADODB.Recordset")

sqlstring = "select * from table2 where DeptID = '001'"

LHQryRes.open sqlstring, LHDB, adOpenDynamic, adLockOptimistic , adCmdText



Line 295 is the above noted LHDB.open statement.  Ignoring whether or not I 

really should close the database in between queries, WHY am I getting the 

database sharing error?  If the web server admin grants read/write 

priviledges on the DIRECTORY the database is in, the error goes away, but 

as soon as she removes that, so that read/write priviledges exist only for 

the .MDB file, the error comes back.



Yes, I am NOT updating the database during this call so I don't need to use 

adOpenDynamic or adLockOptimistic for this call.  HOWEVER, other users of 

this page who have clicked the SUBMIT button will trigger a database 

update.



What I'm wondering is, if I'm doing a query and a db CLOSE, then another 

OPEN a few lines later, why does it fail?



Thanks,

Ken

You need to give the IUSR_xxx account create/delete privs on  the 

directory where the .MDB file resides so it can create/delete the .LDB 

(lock) file.



-- Tomm



> -----Original Message-----

> From: Ken Mosher [mailto:vorlon_ken@h...]

> Sent: Wednesday, July 18, 2001 6:41 PM

> To: ASP Databases

> Subject: [asp_databases] MS Access "file already in use" error

>

>

> I'm having a confusing problem with an ASP getting to an Access

> database. 

> Testing under Personal Web Server yields NO errors, but when I move it 

to

> IIS I get a "file is already in use" error message.

>

> For our purposes, my page opens two different tables in one database. 

I

> have two objects, LHDB (Connection object) and LHQryRes

> (Recordset object).

> My code is very straightforward, and conceptually does this:

>

> connectstring =3D "Driver=3D{Microsoft Access Driver (*.mdb)}; 

DBQ=3Dreport.mdb"

> set LHDB =3D Server.CreateObject("ADODB.Connection")

> LHDB.open connectstring

>

> set LHQryRes =3D Server.CreateObject("ADODB.Recordset")

> sqlstring =3D "select * from table1 where DeptID =3D '001'"

> LHQryRes.open sqlstring, LHDB, adOpenDynamic, adLockOptimistic , 

adCmdText

>

> (display an HTML form with info from DB - this part works fine)

>

> LHQryRes.close

> LHDB.close

>

> connectstring =3D "Driver=3D{Microsoft Access Driver (*.mdb)}; 

DBQ=3Dreport.mdb"

> set LHDB =3D Server.CreateObject("ADODB.Connection")

> LHDB.open connectstring [[ ERROR HAPPENS HERE!]]

> - - - - - - - - - -

> Microsoft OLE DB Provider for ODBC Drivers error '80004005'

> [Microsoft][ODBC Microsoft Access Driver] Could not use '(unknown)'; 

file

> already in use.

> /dph/local_health/DOHSurvey2001BoardOfHealth.asp, line 295

> - - - - - - - - - -

>

> (these lines would be next, but don't execute)

> set LHQryRes =3D Server.CreateObject("ADODB.Recordset")

> sqlstring =3D "select * from table2 where DeptID =3D '001'"

> LHQryRes.open sqlstring, LHDB, adOpenDynamic, adLockOptimistic , 

adCmdText

>

> Line 295 is the above noted LHDB.open statement.  Ignoring

> whether or not I

> really should close the database in between queries, WHY am I getting 

the

> database sharing error?  If the web server admin grants read/write

> priviledges on the DIRECTORY the database is in, the error goes away, 

but

> as soon as she removes that, so that read/write priviledges exist

> only for

> the .MDB file, the error comes back.

>

> Yes, I am NOT updating the database during this call so I don't

> need to use

> adOpenDynamic or adLockOptimistic for this call.  HOWEVER, other users 

of

> this page who have clicked the SUBMIT button will trigger a database

> update.

>

> What I'm wondering is, if I'm doing a query and a db CLOSE, then 

another

> OPEN a few lines later, why does it fail?

>

> Thanks,

> Ken

Opps... also just saw something else:



> > LHQryRes.close

> > LHDB.close

> >

> > connectstring =3D "Driver=3D{Microsoft Access Driver (*.mdb)};

> DBQ=3Dreport.mdb"

> > set LHDB =3D Server.CreateObject("ADODB.Connection")

> > LHDB.open connectstring [[ ERROR HAPPENS HERE!]]



If you want to reuse the connection object then you don't need to re-SET 

the object.

If you want to use the SET statement again, then you need to SET LHDB 

=3D NOTHING on the line before it.



-- Tomm