Tomm,

Thanks for your feedback.  I read the knowledgebase article on Microsoft's site regarding my 

error, so your advice was not unanticipated. However, the ASP I illustrated does more than my 

code shows - even though the DB functions it performs are VERY simple.



There are actually two databases involved, opened and queried separately.  The queries for 

this page all do something similar - they fetch a single record from some table based on a 

department number.  I'll use fake names to keep things simple:

1) fetch one record from DB1, UserTable (gets department description)

2) fetch one record from DB2, (some ReportTable).  Each section of the report (which they're 

updating) is in a table, they update the HTML form and then submit the updates.

3) fetch one record from DB2, ControlTable (gets info about which report tables have been 

edited and when) and display as a "Menu" of available pages to be worked on.



All the ADO constants are the same for ALL the queries. All the object names are re-used, I 

just change the connect string and the sqlstring.  Each step is self-contained, so I open DB, 

query table, close query, close DB before moving on to the next step.  (Let's ignore for now 

whether or not that's efficient and focus on functionality.)



Soooo, why do steps 1 and 2 work perfectly and then step 3 fails?  The code is identical, and 

in fact is only a few lines apart!  If step 3 fails because of missing directory permissions, then 

steps 1 and 2 should have failed for exactly the same reason!



Just as an experiment, I modified 1 and 2, inserting a redundant query close, db close, db 

open, query open in the sequence for both of them. I wanted to see if the DB wasn't closing 

fast enough. Steps 1 and 2 still worked perfectly (then 3 crashed, as usual).  I'm really trying 

to understand what's going on here!



Any and all info is appreciated.  The web administrator (correctly) prefers not to grant 

directory-level permissions, although has agreed to if I require it. I just don't want to ask for 

something I don't need.



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

Ken - 



OK here's a shot in the dusk if you will.  Since you are using DB2 for two 

things, try keeping DB2 open for both step 2 and 3 instead of closing DB2 

in step 2 and then reopening it in step 3.



Hope this helps!



Grant

Hi



Firstly, just use the one connection for the 2nd database. Jet connections

are not pooled:

http://support.microsoft.com/support/kb/articles/Q191/5/72.ASP



Secondly, you say you administrator doesn't want to grant directory level

permissions. Unfortunately there are going to have to be some! Access needs

to be able to create and destory the .ldb lockfile that is uses to keep

track of who is using what parts of the database. My guess is that your

first bit of code is generating the lockfile. You then close the connection,

but either the lockfile can't be deleted, or it isn't being deleted quickly

enough.



Your alternatives are to:

a) make sure you are not using exclusive locks - stick to adLockReadOnly

(see www.adopenstatic.com/faq/jetcursors.asp to see what cursors will give

you that lock type) and

b) make sure you only use 1 connection per page, per database



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: "Ken Mosher" <vorlon_ken@h...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, July 18, 2001 8:47 PM

Subject: [asp_databases] RE: MS Access





: Tomm,

: Thanks for your feedback.  I read the knowledgebase article on Microsoft's

site regarding my

: error, so your advice was not unanticipated. However, the ASP I

illustrated does more than my

: code shows - even though the DB functions it performs are VERY simple.

:

: There are actually two databases involved, opened and queried separately.

The queries for

: this page all do something similar - they fetch a single record from some

table based on a

: department number.  I'll use fake names to keep things simple:

: 1) fetch one record from DB1, UserTable (gets department description)

: 2) fetch one record from DB2, (some ReportTable).  Each section of the

report (which they're

: updating) is in a table, they update the HTML form and then submit the

updates.

: 3) fetch one record from DB2, ControlTable (gets info about which report

tables have been

: edited and when) and display as a "Menu" of available pages to be worked

on.

:

: All the ADO constants are the same for ALL the queries. All the object

names are re-used, I

: just change the connect string and the sqlstring.  Each step is

self-contained, so I open DB,

: query table, close query, close DB before moving on to the next step.

(Let's ignore for now

: whether or not that's efficient and focus on functionality.)

:

: Soooo, why do steps 1 and 2 work perfectly and then step 3 fails?  The

code is identical, and

: in fact is only a few lines apart!  If step 3 fails because of missing

directory permissions, then

: steps 1 and 2 should have failed for exactly the same reason!

:

: Just as an experiment, I modified 1 and 2, inserting a redundant query

close, db close, db

: open, query open in the sequence for both of them. I wanted to see if the

DB wasn't closing

: fast enough. Steps 1 and 2 still worked perfectly (then 3 crashed, as

usual).  I'm really trying

: to understand what's going on here!

:

: Any and all info is appreciated.  The web administrator (correctly)

prefers not to grant

: directory-level permissions, although has agreed to if I require it. I

just don't want to ask for

: something I don't need.

:

: 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

: ---

: * Fast, Full-Featured Microsoft® Excel Web Reports & Charts!

: A breakthrough in high performance Web application development,

SoftArtisans

: ExcelWriter 1.1 supports native Excel charting, image insertion, and

: advanced functions & formatting. One click generates presentation-quality

: Excel spreadsheets-and ExcelWriter performs over 100 times faster than the

: Excel Object. Several editions, including ExcelWriterFREE, are available.

: http://www.softartisans.com/softartisans/excelwriter.html>




$subst('Email.Unsub')

Grant and Ken,



Thanks for the help.  Grant, I was going to try that but hadn't gotten around to it.  Even though I know 

efficiency-wise it seems silly to open/close then open/close the same database, the anal side of me liked 

the symmetry of it.  Plus, I prefer to view the situation as though each table is in a separate DB. That 

way, if I ever do move tables my code won't break (I'll may have to change some connect strings, I 

guess, but I could avoid that too if I wanted to).  Plus, as far as efficiency goes, the app is pretty 

lightweight and there were no noticeable delays. There are a max of 103 users, many of whom will be 

dial-up, and they'll never notice.



Ken, testing along your lines had occupied my time. I was hypothesizing that using adOpenStatic and 

adLockReadOnly might solve my problem.  They did not.  I was hoping to avoid creating the .ldb 

altogether.  I will move my databases to a new subdirectory and have the permissions assigned there. 

Lucky for me all the DB names that I use in my DSN-less connect strings are defined in a single include 

- easy to change.



I'll say, though, that I'm still might confused that any of my code works at all if the permissions at the 

directory level are required for MS Access databases to work correctly.  I have three other .MDB files, 

two of which are written to based on form submissions and all of which are queried in exactly the same 

way that's causing this error - and all my other code works!  Ken may have hit it with the timing 

hypothesis.  I'll read the links you provided in the morning. Right now it's kind of late on the east coast 

of the US.



Thanks again,

Ken

He *will* have to grant create/delete permissions for the directory... 

typically, people place the .MDB file in a seperate directory below or 

outside the web folders for increased security.



Hope that helps.



-- Tomm



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

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

> Sent: Wednesday, July 18, 2001 8:47 PM

> To: ASP Databases

> Subject: [asp_databases] RE: MS Access

>

>

> Tomm,

> Thanks for your feedback.  I read the knowledgebase article on

> Microsoft's site regarding my

> error, so your advice was not unanticipated. However, the ASP I

> illustrated does more than my

> code shows - even though the DB functions it performs are VERY simple.

>

> There are actually two databases involved, opened and queried

> separately.  The queries for

> this page all do something similar - they fetch a single record

> from some table based on a

> department number.  I'll use fake names to keep things simple:

> 1) fetch one record from DB1, UserTable (gets department description)

> 2) fetch one record from DB2, (some ReportTable).  Each section

> of the report (which they're

> updating) is in a table, they update the HTML form and then

> submit the updates.

> 3) fetch one record from DB2, ControlTable (gets info about which

> report tables have been

> edited and when) and display as a "Menu" of available pages to be

> worked on.

>

> All the ADO constants are the same for ALL the queries. All the

> object names are re-used, I

> just change the connect string and the sqlstring.  Each step is

> self-contained, so I open DB,

> query table, close query, close DB before moving on to the next

> step.  (Let's ignore for now

> whether or not that's efficient and focus on functionality.)

>

> Soooo, why do steps 1 and 2 work perfectly and then step 3 fails?

>  The code is identical, and

> in fact is only a few lines apart!  If step 3 fails because of

> missing directory permissions, then

> steps 1 and 2 should have failed for exactly the same reason!

>

> Just as an experiment, I modified 1 and 2, inserting a redundant

> query close, db close, db

> open, query open in the sequence for both of them. I wanted to

> see if the DB wasn't closing

> fast enough. Steps 1 and 2 still worked perfectly (then 3

> crashed, as usual).  I'm really trying

> to understand what's going on here!

>

> Any and all info is appreciated.  The web administrator

> (correctly) prefers not to grant

> directory-level permissions, although has agreed to if I require

> it. I just don't want to ask for

> something I don't need.

>

> Thanks,

> Ken

>

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

=3D

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

.LDB =3D

> > (lock) file.

> >

> > -- Tomm

Tomm, Grant, Ken,



Well, at first it seemed like Grant's suggestion had fixed the problem.  I took out the 

intervening (and unnecessary) DB close and the scripts now work - mostly.



The pages work when all I'm doing is displaying data, but when the pages are called again via 

the SUBMIT button (I use the same ASP to save and then redisplay the data) it fails when 

trying to update a second table.  Upon SUBMIT:

1) select * from ReportTableX where depID = '001'

2) set fields to new values

3) table.update

4) table.close (but leave DB connection OPEN)



5) run sql statement via a Command object:

update table1 set LastUpdated = current date where DeptID = '001'  [[this fails with a similar 

error to the original error]]



I've set the Command object's ActiveConnection to the open Connection object's 

ConnectionString, thus trying to replicate the conditions that allowed my read from two tables. 

No go!  So, it looks like Tomm is triumphant.  I will follow his original advice and make the 

request of my server admin.



I *STILL* really, really wonder why, if my permissions are wrong, that I'm able to do any of 

what I'm doing at all.  If using MS Access files and the .LDB files that go along with that require 

permissions at the directory level, then why oh why do my scripts work at all?



It's so unsatisfying to solve a problem without really understanding the solution.  It means I 

haven't learned anything, when you come right down to it.  Grrr.



Thanks again,

Ken