Multiuser environment ... poor performance and locks?

[juvi]

Hello,

I have got some troubles with my access database in respect to performance
and locking (concurent editing):

I have splitted my database into a frontend and to a backend (backend is on
a server) ... the database performs a little bit slow but there are max 10
users on the database...and I make my INSERTs and UPDATEs against linked
tables from the backend...through DoCmd.RunSQL "..."

Today was the first time that the backend database was locked and nobody was
able to make INSERTs/UPDATEs only SELECTs...in the path where the backend
database is stored there was no *.ldb created but something locked it....what
could be the reason for that? Hope that somebody can help me...

thx
juvi

 

[Rick Brandt]

Hello,

I have got some troubles with my access database in respect to
performance and locking (concurent editing):

I have splitted my database into a frontend and to a backend (backend is
on a server) ... the database performs a little bit slow but there are
max 10 users on the database...and I make my INSERTs and UPDATEs against
linked tables from the backend...through DoCmd.RunSQL "..."

Today was the first time that the backend database was locked and nobody
was able to make INSERTs/UPDATEs only SELECTs...in the path where the
backend database is stored there was no *.ldb created but something
locked it....what could be the reason for that? Hope that somebody can
help me...

If someone opens the file while nobody else is using it and that person
does not have the permissions required to create new files in that folder
then they will open the file as read-only and no LDB file is created.

This also prevents subsequent users from creating an LDB file so everyone
that follows that person into the file will also get read-only access.

 

[Klatuu]

In addition to Rick's comments, I would make a suggestion that will improve
you performance.

Instead of using the DoCmd.RunSQL, use the Excute method of the CurrentDb
object. It is much faster because it does not go through the Access UI, it
goes directly to Jet/Ace. It also does not trigger warning messages so you
don't have to worry about setting them for Action queries. But, it also
does not report errors, so you do have to use the dbFailOnError option. It
would be like this:

Currentdb.Execute "MyQueryName", dbFailOnError

or
Currentdb.Execute"DELETE FROM SomeTable WHERE [SomeField] =
"""Foobar"";", dbFailOnError

 

[Jack Leach]

And should you decide to use the more efficient CurrentDb.Execute method of
running queries, note that the Expression Service is not available this way
(because it doesn't work with the access interface).

So a statement such as below that you can run with DoCmd.RunSQL:

"DELETE * FROM table WHERE ID = [Forms]![frmName].[IDField]"

would need to be changed to:

"DELETE * FROM table WHERE ID = " & Forms!frmName.IDField

that is... you need to "manually enter" the data into the SQL string as the
db.Execute will not do it for you.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)