Multiple Connections to an Access.mdb

[Terra]

Hi there,

I have a web app that connects to a small access
database. Many users may connect to this database at the
same time. Is there anything that I should be aware of, or
can do from preventing access to it. I was told that I may
have problems with multiple users connecting to it, and
I'm wondering if I could do something to prevent this.
There are queries stored within the database used as
command objects, and many dynamic select statements
throughout the pages. Eventually, I want to update the
database through the web pages...

Any thoughts on this would be appreciated.

Thanks,
Terra

[Bob Barrows]

All users of the database must have change permissions on the folder
containing the Access database. This allows the ldb file to be created and
deleted as necessary
There are two scenarios:
In an intranet application which uses NT Challenge and Response security,
all users must have change permissions

In apps using Anonymous/ Basic authentication, the IUSR account must have
change permissions - do not count on the IUSR account being part of the
Everyone group

As long as permissions are set, there is nothing extra you have to do.
However, Jet does limit the number of connections at one time (I think 10 is
the number) so you have to design your application accordingly:

1. Use recordsets for reading and displaying records only - no recordset
updates. Stay connected to the database for as short a time as possible.
This means using GetRows and GetString as much as possible - stay away from
slow recordset loops: stuff the data into an array with GetRows, or a string
with GetString, and close that recordset and connection. This has two
advantages: a. connection time is minimized b. looping through an array is
much faster than looping through a recordset. And if you use GetString, no
looping is needed!

2. Use SQL Insert, Update and Delete statements for all data modification.
Again, stay connected for a short a time as possible

3. Avoid dynamic SQL - use saved queries (learn how to use parameterized
saved queries) as much as you can. This will also help you minimize
connection time

4. Make the proper sacrifices: you must keep the gods appeased

5. Keep pressuring the people in charge to purchase a SQL Server so you can
get away from using a desktop database in a client-server application.

OK, 4 and 5 (especially 4) were jokes .. I once gave this advice to a Dutch
or German poster and he sent me an email asking me to clarify whether he
should use a male or female goat for the sacrifice. I think he was serious.
<g> I replied that a male goat would be appropriate :-) ... no, I told him
it was a joke!

For more information, see www.aspfaq.com.

Bob Barrows

[Bullschmidt]

Terra,

Turn buffering on so that only one user at a time accesses the database.

Buffer That Output by Charles Carroll
http://www.learnasp.com/advice/whybuffer.asp

Best regards,
J. Paul Schmidt
http://www.Bullschmidt.com - Freelance ASP Web Developer
http://www.Bullschmidt.com/DevTip.asp - ASP Developer Tips

"Terra" <terra...@nv.sympatico.ca> wrote in message news:<058701c3283d$bdb90040$a501...@phx.gbl>...

[Bob Lehmann]

I don't think bufferring has anything to do with accessing the db. I
certainly can't find anything in the article that makes that claim.

Bob Lehmann

"Bullschmidt" <pa...@bullschmidt.com> wrote in message
news:3b1adcec.03060...@posting.google.com...

[Terra-Dawn Nymann]

I would like to have multiple users be able to access the database at
one time. For this reason, if buffering did result in only one user
being able to access the DB, I wouldn't find it useful. In any case,
thanks for the advice and the response, I always like to learn something
new...

Thanks again!
Terra

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[A Ratcliffe]

Just a thought, but surely this is going against the grain of Late Open
Early Close. ASP pages are ultimately disconnected, since once the page has
been parsed and sent to the client, there is no further communication until
a postback. With well written SELECTs, INSERTs and UPDATEs you should be
able to have far more than 10 people 'apparently' all working concurrently,
even though they are actually connecting, processing, and immediately
disconnecting from the database.

Yours,

A Ratcliffe

"Terra-Dawn Nymann" <terra...@nv.sympatico.ca> wrote in message
news:%23wmhP7X...@TK2MSFTNGP10.phx.gbl...

[Bob Barrows]

Buffering has no effect on the number of users able to use a database
concurrently. Bullschmidt must have been thinking of something else.

Bob Barrows