Access + Asp + multiuser + table lock

[Patrick]

Hi all.

In my asp site, i have a "Search and Sort" button that creates a lot of
activity in my access database.

Clciking on the button does that :
1. Begin a transaction
2. Create duplicates of empty model tables : "SELECT * INTO DuplicateTable
FROM ModelTable". Of course DuplicateTable is a unique name.
3. Use lot of "conn.execute sqlstring" instructions to add/delete/update in
the duplicate tables
4. Destroy duplicate tables
5. Close transaction

Plz don't ask why i create duplicate tables as i've tried many different
solutions : this one is the closest to what i want.
(if you want precisions on the other solutions i tried, i wrote a post in
microsoft.public.access.multiuser called Asp Multiuser and table lock)

It works fine until two users click the "Search and Sort" button at the very
same time
In this case, the second user get an error message when creating duplicate
tables (Step 2).
It says something about a lock that i don't know how to translate. Error
number is 80004005.

Why is there a lock here ? I don't open any recordset that involves the
tables concerned by Step 2 !
Any advice welcome

[Jeff Cochran]

On Thu, 15 Apr 2004 21:32:04 +0200, "Patrick" <xxx@xxx.xxx> wrote:

>Hi all.
>
>In my asp site, i have a "Search and Sort" button that creates a lot of
>activity in my access database.
>
>Clciking on the button does that :
>1. Begin a transaction
>2. Create duplicates of empty model tables : "SELECT * INTO DuplicateTable
>FROM ModelTable". Of course DuplicateTable is a unique name.
>3. Use lot of "conn.execute sqlstring" instructions to add/delete/update in
>the duplicate tables
>4. Destroy duplicate tables
>5. Close transaction
>
>Plz don't ask why i create duplicate tables as i've tried many different
>solutions : this one is the closest to what i want.
>(if you want precisions on the other solutions i tried, i wrote a post in
>microsoft.public.access.multiuser called Asp Multiuser and table lock)
>
>It works fine until two users click the "Search and Sort" button at the very
>same time
>In this case, the second user get an error message when creating duplicate
>tables (Step 2).
>It says something about a lock that i don't know how to translate. Error
>number is 80004005.

See:

Why do I get database-related 80004005 errors?
[url]http://www.aspfaq.com/show.asp?id=2009[/url]

>Why is there a lock here ? I don't open any recordset that involves the
>tables concerned by Step 2 !
>Any advice welcome

Most importantly, see:

What are the limitations of Access?
[url]http://www.aspfaq.com/show.asp?id=2195[/url]

Jeff

[Patrick]

Thanks for the links Jeff, but i already spent a lot of time on this and i
can't find a solution or even an explanation to my problem.

"Jeff Cochran" <jcochran.nospam@naplesgov.com> a écrit dans le message de
news:407ef0bf.27856585@msnews.microsoft.com...

> On Thu, 15 Apr 2004 21:32:04 +0200, "Patrick" <xxx@xxx.xxx> wrote:
>

> >Hi all.
> >
> >In my asp site, i have a "Search and Sort" button that creates a lot of
> >activity in my access database.
> >
> >Clciking on the button does that :
> >1. Begin a transaction
> >2. Create duplicates of empty model tables : "SELECT * INTO

DuplicateTable

> >FROM ModelTable". Of course DuplicateTable is a unique name.
> >3. Use lot of "conn.execute sqlstring" instructions to add/delete/update

in

> >the duplicate tables
> >4. Destroy duplicate tables
> >5. Close transaction
> >
> >Plz don't ask why i create duplicate tables as i've tried many different
> >solutions : this one is the closest to what i want.
> >(if you want precisions on the other solutions i tried, i wrote a post in
> >microsoft.public.access.multiuser called Asp Multiuser and table lock)
> >
> >It works fine until two users click the "Search and Sort" button at the

very

> >same time
> >In this case, the second user get an error message when creating

duplicate

> >tables (Step 2).
> >It says something about a lock that i don't know how to translate. Error
> >number is 80004005.

>
> See:
>
> Why do I get database-related 80004005 errors?
> [url]http://www.aspfaq.com/show.asp?id=2009[/url]
>

> >Why is there a lock here ? I don't open any recordset that involves the
> >tables concerned by Step 2 !
> >Any advice welcome

>
> Most importantly, see:
>
> What are the limitations of Access?
> [url]http://www.aspfaq.com/show.asp?id=2195[/url]
>
> Jeff

[Jeff Cochran]

On Thu, 15 Apr 2004 23:16:34 +0200, "Patrick" <xxx@xxx.xxx> wrote:

>Thanks for the links Jeff, but i already spent a lot of time on this and i
>can't find a solution or even an explanation to my problem.

It's hard to tell without knowing the exact error, but my first guess
was the recordset is being held open while you perform your
transactions, thus locking the file for the next user or process.
Keeping connections short would help this, but a select into another
table just may keep the file locked for too long. My second thought
is that the file really is locked, and Access is poor for dealing with
multiple users. A switch to MSDE might help that.

Jeff

>
>"Jeff Cochran" <jcochran.nospam@naplesgov.com> a écrit dans le message de
>news:407ef0bf.27856585@msnews.microsoft.com...

>> On Thu, 15 Apr 2004 21:32:04 +0200, "Patrick" <xxx@xxx.xxx> wrote:
>>

>> >Hi all.
>> >
>> >In my asp site, i have a "Search and Sort" button that creates a lot of
>> >activity in my access database.
>> >
>> >Clciking on the button does that :
>> >1. Begin a transaction
>> >2. Create duplicates of empty model tables : "SELECT * INTO

>DuplicateTable

>> >FROM ModelTable". Of course DuplicateTable is a unique name.
>> >3. Use lot of "conn.execute sqlstring" instructions to add/delete/update

>in

>> >the duplicate tables
>> >4. Destroy duplicate tables
>> >5. Close transaction
>> >
>> >Plz don't ask why i create duplicate tables as i've tried many different
>> >solutions : this one is the closest to what i want.
>> >(if you want precisions on the other solutions i tried, i wrote a post in
>> >microsoft.public.access.multiuser called Asp Multiuser and table lock)
>> >
>> >It works fine until two users click the "Search and Sort" button at the

>very

>> >same time
>> >In this case, the second user get an error message when creating

>duplicate

>> >tables (Step 2).
>> >It says something about a lock that i don't know how to translate. Error
>> >number is 80004005.

>>
>> See:
>>
>> Why do I get database-related 80004005 errors?
>> [url]http://www.aspfaq.com/show.asp?id=2009[/url]
>>

>> >Why is there a lock here ? I don't open any recordset that involves the
>> >tables concerned by Step 2 !
>> >Any advice welcome

>>
>> Most importantly, see:
>>
>> What are the limitations of Access?
>> [url]http://www.aspfaq.com/show.asp?id=2195[/url]
>>
>> Jeff

>

[Patrick]

I don't understand anything to these locks.
Why do i get a lock for a table that has no recordsets and no SQL query that
add/delete/update its rows ?

I tried some prvider specific settings, but nothing works :
Jet OLEDB:Database Locking Mode
Jet OLEDB:Lock Delay
Jet OLEDB:Lock Retry
Jet OLEDB:Max Locks Per File
Jet OLEDB:Locking Granularity

I hate not knowing the cause of a problem.
I'll do error handling for this, but error handling in ADO sucks and in my
cases makes both users wait a long time before getting the results on the
screen.

Next time I swear i'll use MSDE :-)

[Larry Linson]

Strictly speaking, you are using a Jet database with your .asp pages, not an
"Access database" although we often use that "shorthand" and so does
Microsoft.

"Multiuser", in Access/Jet parlance, is a rather specific term... implying
an Access-Jet environment with multiple users logging in to the same MDB or
MDB front end with shared MDB back end.

There's more than one way to access a Jet database from .ASP, but the most
common one is that all accesses are with the same userid / password. That
can cause some confusion... because all the users are, to the ADO software /
Jet database, just one single user.

Your "duplicate tables" and the reasoning are rather vaguely described, but
likely the design of your database and application are the reason you are
having difficulties.

Larry Linson
Microsoft Access MVP
"Patrick" <xxx@xxx.xxx> wrote in message
news:O0CpNByIEHA.1412@TK2MSFTNGP12.phx.gbl...

> Hi all.
>
> In my asp site, i have a "Search and Sort" button that creates a lot of
> activity in my access database.
>
> Clciking on the button does that :
> 1. Begin a transaction
> 2. Create duplicates of empty model tables : "SELECT * INTO DuplicateTable
> FROM ModelTable". Of course DuplicateTable is a unique name.
> 3. Use lot of "conn.execute sqlstring" instructions to add/delete/update

in

> the duplicate tables
> 4. Destroy duplicate tables
> 5. Close transaction
>
> Plz don't ask why i create duplicate tables as i've tried many different
> solutions : this one is the closest to what i want.
> (if you want precisions on the other solutions i tried, i wrote a post in
> microsoft.public.access.multiuser called Asp Multiuser and table lock)
>
> It works fine until two users click the "Search and Sort" button at the

very

> same time
> In this case, the second user get an error message when creating duplicate
> tables (Step 2).
> It says something about a lock that i don't know how to translate. Error
> number is 80004005.
>
> Why is there a lock here ? I don't open any recordset that involves the
> tables concerned by Step 2 !
> Any advice welcome
>
>

[Patrick]

> "Multiuser", in Access/Jet parlance, is a rather specific term... implying

> an Access-Jet environment with multiple users logging in to the same MDB

or

> MDB front end with shared MDB back end.

Right

> There's more than one way to access a Jet database from .ASP, but the most
> common one is that all accesses are with the same userid / password. That
> can cause some confusion... because all the users are, to the ADO software

/

> Jet database, just one single user.

That is my case : User ID=admin

> Your "duplicate tables" and the reasoning are rather vaguely described,

but

> likely the design of your database and application are the reason you are
> having difficulties.

You may be right as i'm not very experienced. I'd be very pleased to discuss
of my app design in detail if you don't mind spending a lot of time on this.

But i'm still wondering why i get a lock error when trying to execute that
statement : "SELECT * INTO DuplicateTable
FROM ModelTable" knowing that :
- DuplicateTable doesn't exist yet
- ModelTable is used nowhere else in my app (no recordset opened, no SQL
statement executed, NOTHING)

>
> Larry Linson
> Microsoft Access MVP
> "Patrick" <xxx@xxx.xxx> wrote in message
> news:O0CpNByIEHA.1412@TK2MSFTNGP12.phx.gbl...

> > Hi all.
> >
> > In my asp site, i have a "Search and Sort" button that creates a lot of
> > activity in my access database.
> >
> > Clciking on the button does that :
> > 1. Begin a transaction
> > 2. Create duplicates of empty model tables : "SELECT * INTO

DuplicateTable

> > FROM ModelTable". Of course DuplicateTable is a unique name.
> > 3. Use lot of "conn.execute sqlstring" instructions to add/delete/update

> in

> > the duplicate tables
> > 4. Destroy duplicate tables
> > 5. Close transaction
> >
> > Plz don't ask why i create duplicate tables as i've tried many different
> > solutions : this one is the closest to what i want.
> > (if you want precisions on the other solutions i tried, i wrote a post

in

> > microsoft.public.access.multiuser called Asp Multiuser and table lock)
> >
> > It works fine until two users click the "Search and Sort" button at the

> very

> > same time
> > In this case, the second user get an error message when creating

duplicate

> > tables (Step 2).
> > It says something about a lock that i don't know how to translate. Error
> > number is 80004005.
> >
> > Why is there a lock here ? I don't open any recordset that involves the
> > tables concerned by Step 2 !
> > Any advice welcome
> >
> >

>
>

[Jeff Cochran]

On Fri, 16 Apr 2004 03:47:53 +0200, "Patrick" <xxx@xxx.xxx> wrote:

>> "Multiuser", in Access/Jet parlance, is a rather specific term... implying
>> an Access-Jet environment with multiple users logging in to the same MDB

>or

>> MDB front end with shared MDB back end.

>
>Right
>

>> There's more than one way to access a Jet database from .ASP, but the most
>> common one is that all accesses are with the same userid / password. That
>> can cause some confusion... because all the users are, to the ADO software

>/

>> Jet database, just one single user.

>
>That is my case : User ID=admin
>

>> Your "duplicate tables" and the reasoning are rather vaguely described,

>but

>> likely the design of your database and application are the reason you are
>> having difficulties.

>
>You may be right as i'm not very experienced. I'd be very pleased to discuss
>of my app design in detail if you don't mind spending a lot of time on this.
>
>But i'm still wondering why i get a lock error when trying to execute that
>statement : "SELECT * INTO DuplicateTable
>FROM ModelTable" knowing that :
>- DuplicateTable doesn't exist yet
>- ModelTable is used nowhere else in my app (no recordset opened, no SQL
>statement executed, NOTHING)

Except on an Access, err, Jet, database, that can lock the MDB file
itself. Part of your problem may very well be the single user locking
of the database as Larry suggested. On the other hand, even with
multiple users Access, um, I mean Jet, databases have some locking
issues. You may see it in an ADO environment where you may not see it
elsewhere too.

Larry can probably go into better detail with the actual locking
levels, but you may need to redesign your database or move to a MSDE
or SQL Server database to keep from having these locking issues.

Jeff

>> Larry Linson
>> Microsoft Access MVP
>> "Patrick" <xxx@xxx.xxx> wrote in message
>> news:O0CpNByIEHA.1412@TK2MSFTNGP12.phx.gbl...

>> > Hi all.
>> >
>> > In my asp site, i have a "Search and Sort" button that creates a lot of
>> > activity in my access database.
>> >
>> > Clciking on the button does that :
>> > 1. Begin a transaction
>> > 2. Create duplicates of empty model tables : "SELECT * INTO

>DuplicateTable

>> > FROM ModelTable". Of course DuplicateTable is a unique name.
>> > 3. Use lot of "conn.execute sqlstring" instructions to add/delete/update

>> in

>> > the duplicate tables
>> > 4. Destroy duplicate tables
>> > 5. Close transaction
>> >
>> > Plz don't ask why i create duplicate tables as i've tried many different
>> > solutions : this one is the closest to what i want.
>> > (if you want precisions on the other solutions i tried, i wrote a post

>in

>> > microsoft.public.access.multiuser called Asp Multiuser and table lock)
>> >
>> > It works fine until two users click the "Search and Sort" button at the

>> very

>> > same time
>> > In this case, the second user get an error message when creating

>duplicate

>> > tables (Step 2).
>> > It says something about a lock that i don't know how to translate. Error
>> > number is 80004005.
>> >
>> > Why is there a lock here ? I don't open any recordset that involves the
>> > tables concerned by Step 2 !
>> > Any advice welcome
>> >
>> >

>>
>>

>

[Patrick]

> Except on an Access, err, Jet, database, that can lock the MDB file

> itself. Part of your problem may very well be the single user locking
> of the database as Larry suggested. On the other hand, even with
> multiple users Access, um, I mean Jet, databases have some locking
> issues. You may see it in an ADO environment where you may not see it
> elsewhere too.
>
> Larry can probably go into better detail with the actual locking
> levels, but you may need to redesign your database or move to a MSDE
> or SQL Server database to keep from having these locking issues.

Thanks to take the time to answer me Jeff !
I hope i won't have to redesign my app or change my database as i'm nearly
finished with it.
As I'm running out of time, i prefer put a friendly error message asking the
user to wait rather than redesigning the entire app !

But i don't like not knowing why things do not work !

[Larry Linson]

Patrick, unfortunately, I don't do .asp, and do little ADO, so I am not a
particularly good source of details. In my experience, however, it is rarely
necessary to create temporary tables, and it causes enough people enough
trouble that it's a good idea to avoid it if there is any other way. If you
do so in Access itself, a well-regarded approach is to create a temporary
database, and then create the temporary table in the temporary database.
When it is no longer needed, you use the KILL statement to delete the whole
temporary database. But, I am not at all sure what the ADO / ADOX code would
be to accomplish this -- it's easy using DAO. You'd still need to make sure
that multiple users aren't trying to create/use the same temp table.

I don't know that you'll have to redesign the whole app, but I don't know
the equivalent of "just a squirt of oil right here and she'll run fine" for
this.

Larry Linson
Microsoft Access MVP


"Patrick" <xxx@xxx.xxx> wrote in message
news:%23Cr5Fs1IEHA.3512@TK2MSFTNGP10.phx.gbl...

> > Except on an Access, err, Jet, database, that can lock the MDB file
> > itself. Part of your problem may very well be the single user locking
> > of the database as Larry suggested. On the other hand, even with
> > multiple users Access, um, I mean Jet, databases have some locking
> > issues. You may see it in an ADO environment where you may not see it
> > elsewhere too.
> >
> > Larry can probably go into better detail with the actual locking
> > levels, but you may need to redesign your database or move to a MSDE
> > or SQL Server database to keep from having these locking issues.

>
> Thanks to take the time to answer me Jeff !
> I hope i won't have to redesign my app or change my database as i'm nearly
> finished with it.
> As I'm running out of time, i prefer put a friendly error message asking

the

> user to wait rather than redesigning the entire app !
>
> But i don't like not knowing why things do not work !
>
>

[roger]

"Patrick" wrote

> 1. Begin a transaction
> 2. Create duplicates of empty model tables : "SELECT * INTO DuplicateTable
> FROM ModelTable". Of course DuplicateTable is a unique name.
> 3. Use lot of "conn.execute sqlstring" instructions to add/delete/update

in

> the duplicate tables
> 4. Destroy duplicate tables
> 5. Close transaction

Does 2, 1, 3, 5, 4 work?

Since DuplicateTable is unique, do you need the transaction?


--
roger

[Patrick]

> If you

> do so in Access itself, a well-regarded approach is to create a temporary

> database, and then create the temporary table in the temporary database.

> When it is no longer needed, you use the KILL statement to delete the

whole

> temporary database. You'd still need to make sure

> that multiple users aren't trying to create/use the same temp table.

OK. Thanks for the advice

> I don't know that you'll have to redesign the whole app, but I don't know

> the equivalent of "just a squirt of oil right here and she'll run fine"

for

> this.

I could try...

Thanks for all Larry

[Patrick]

> Does 2, 1, 3, 5, 4 work?

I'm not sure to understand what you mean.
Do you think that using transactions can cause the error ?

> Since DuplicateTable is unique, do you need the transaction?

In case of trouble (server hangs), transaction will not commit and my
database will not be polluted by temporary tables that should have been
destroyed.

[Patrick]

> > 1. Begin a transaction

> > 2. Create duplicates of empty model tables : "SELECT * INTO

DuplicateTable

> > FROM ModelTable". Of course DuplicateTable is a unique name.
> > 3. Use lot of "conn.execute sqlstring" instructions to add/delete/update

> in

> > the duplicate tables
> > 4. Destroy duplicate tables
> > 5. Close transaction

>
> Does 2, 1, 3, 5, 4 work?

It works ! Why ?

[roger]

"Patrick" wrote

> > > 1. Begin a transaction
> > > 2. Create duplicates of empty model tables : "SELECT * INTO

> DuplicateTable

> > > FROM ModelTable". Of course DuplicateTable is a unique name.
> > > 3. Use lot of "conn.execute sqlstring" instructions to

add/delete/update

> > in

> > > the duplicate tables
> > > 4. Destroy duplicate tables
> > > 5. Close transaction

> >
> > Does 2, 1, 3, 5, 4 work?

>
> It works ! Why ?

I don't know, sorry.

But since "Select into" caused the problem,
moving it out of the transaction seemed sensible.

Does the error message give you any clue about
which table is locked - DuplicateTable or ModelTable?

--
roger

[Patrick]

Damned !
This caused me so much trouble and the solution was so easy !

I'm not forced to use duplicates of empty tables anymore because it seems
that the problem didn't come from queries or recordsets but from the fact of
using a transaction.
Thanks for the advice roger as it helped me a lot.

But if someone has an idea why transactions causes table locks, don't
hesitate to explain !


"roger" <mothland@btopenworld.com> a écrit dans le message de
news:c5r9if$4kf$1@sparta.btinternet.com...

> "Patrick" wrote
>

> > > > 1. Begin a transaction
> > > > 2. Create duplicates of empty model tables : "SELECT * INTO

> > DuplicateTable

> > > > FROM ModelTable". Of course DuplicateTable is a unique name.
> > > > 3. Use lot of "conn.execute sqlstring" instructions to

> add/delete/update

> > > in
> > > > the duplicate tables
> > > > 4. Destroy duplicate tables
> > > > 5. Close transaction
> > >
> > > Does 2, 1, 3, 5, 4 work?

> >
> > It works ! Why ?

>
> I don't know, sorry.
>
> But since "Select into" caused the problem,
> moving it out of the transaction seemed sensible.
>
> Does the error message give you any clue about
> which table is locked - DuplicateTable or ModelTable?
>
> --
> roger
>
>

[Bob Barrows]

Larry Linson wrote:

> Patrick, unfortunately, I don't do .asp, and do little ADO, so I am
> not a particularly good source of details. In my experience, however,
> it is rarely necessary to create temporary tables, and it causes
> enough people enough trouble that it's a good idea to avoid it if
> there is any other way. If you do so in Access itself, a
> well-regarded approach is to create a temporary database, and then
> create the temporary table in the temporary database. When it is no
> longer needed, you use the KILL statement to delete the whole
> temporary database. But, I am not at all sure what the ADO / ADOX
> code would be to accomplish this -- it's easy using DAO. You'd still
> need to make sure that multiple users aren't trying to create/use the
> same temp table.
>

Patrick, if you don't have Visual Studio, the ADOX online help can be found
here:
[url]http://msdn.microsoft.com/library/en-us/ado270/htm/admscadoapireference.asp[/url]

Another article that I have found useful is:
[url]http://msdn.microsoft.com/library/en-us/dndao/html/daotoadoupdate.asp[/url]

You will find the procedure to create a database in there, although you will
have to rewrite the code examples which were written for VB to vbscript
(mostly a matter of getting rid of the type declarations from the dim
statements and replacing Set ... = New ... with Server.CreateObject)

You can use ADOX to create a database. I think you will need to use the
FileSystemObject to delete the database file once you are done with it. You
can find help with FSO at [url]www.aspfaq.com[/url].

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Patrick]

thanks Bob.

It looks that my problem comes from the use of transactions.
Check the same topic in "
microsoft.public.inetserver.asp.db" if you're interested

[roger]

"Patrick" wrote

> But if someone has an idea why transactions causes table locks, don't
> hesitate to explain !

Well that's their purpose when you are changing data,
to stop other users seeing or changing data that may
no longer be current, or even exist.

It's not so obvious when you are reading data
e.g. from ModelTable.

But if that's locked too, I'm not really *that* surprised


--
roger