VB app connecting to Access 2000 db runs slower for each additional user

[Tim Dol]

I have inherited a VB6-SP5 program connecting to an Access 2000 database.
I'm relatively new to VB/Access programming, so I may overlook something
obvious. I'll try to explain the problem I can't solve.

My VB executable and Access database are both stored in a shared folder on a
Novell volume.

The first user has a good performance, but the second user has to wait 9
seconds to open the first form.
The third user has to wait 18 seconds, the fourth, 28 seconds, etc.
Does anyone recognize this behaviour, and if so: what might be the problem?
First, I suspected a locking problem, but I don't understand why the delay
increases with around 9 seconds for each consecutive user. I found an
article on 'ldb locking which a persistent connection fixes' and 'sub
datasheet Name property set to [Auto]'. I already tried these suggestions,
but they didn''t solve this particular problem. Below are details on the
program and database:

In sub Main() the program connects to the database:

Set cn = New ADODB.Connectio n
cn.ConnectionSt ring = strConnectionSt ring
cn.Open

where cn is declared as 'Public cn As ADODB.Connectio n' and
strConnectionSt ring is 'Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=<path>\k iezers_be.mdb;P ersist Security Info=False'.

The form has an Adodc control for each of the 6 tables, that the forms uses.
Only 1 table contains the data that are being edited on the form, the other
tables are used to populate DataCombo controls.

(Relevant???) settings for Adodc1, the one with the data being edited:
..CacheSize=50
..CommandTimeou t=30
..CommandType=a dCmdUnknown
..ConnectionStr ing=<set from code to facilitate flexible change of the
location of the db>
..ConnectionTim eout=15
..CursorLocatio n=adUseClient
..CursorType=ad OpenDynamic
..LockType=adLo ckPessimistic
..Mode=adModeUn known

Settings for the other Adodc controls (Adocdc2 to -6), used to populate the
DataCombos:
same as Adodc1, except .Locktype=adLoc kReadOnly

The DataCombos have their .RowSource and .ListField set at design time.
..DataSource and .DataField are empty, because the destination depends on a
user-selectable option. The corresponding value in Adodc1 is set in the
_Change event of the DataCombo.

In Sub Form_Load of the form, the listboxes are populated with the following
code (with a different table name and adodc object for the different
combos):

strSql = "SELECT * FROM landentabel"
Adodc2.Connecti onString = strConnectionSt ring
Adodc2.CommandT ype = adCmdText
Adodc2.RecordSo urce = strSql
Adodc2.Refresh

The delay users are experiencing is distributed evenly over the 6 tables.

I hope this enough information and thanks in advance for your help!
tim

[DFS]

"Tim Dol" <ti*****@tiscal i.nl> wrote in message
news:40******** **************@ dreader2.news.t iscali.nl...

I have inherited a VB6-SP5 program connecting to an Access 2000 database.
I'm relatively new to VB/Access programming, so I may overlook something
obvious. I'll try to explain the problem I can't solve.

My VB executable and Access database are both stored in a shared folder on a Novell volume.
Tim,

You definitely want to give each user a copy of the VB executable to run on
their own machines, and remove it from the share. Leave the database where
it is.

The first user has a good performance, but the second user has to wait 9
seconds to open the first form.
The third user has to wait 18 seconds, the fourth, 28 seconds, etc.
Does anyone recognize this behaviour, and if so: what might be the problem? First, I suspected a locking problem, but I don't understand why the delay
increases with around 9 seconds for each consecutive user. I found an
article on 'ldb locking which a persistent connection fixes' and 'sub
datasheet Name property set to [Auto]'. I already tried these suggestions,
but they didn''t solve this particular problem. Below are details on the
program and database:

In sub Main() the program connects to the database:

Set cn = New ADODB.Connectio n
cn.ConnectionSt ring = strConnectionSt ring
cn.Open

where cn is declared as 'Public cn As ADODB.Connectio n' and
strConnectionSt ring is 'Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=<path>\k iezers_be.mdb;P ersist Security Info=False'.

The form has an Adodc control for each of the 6 tables, that the forms uses. Only 1 table contains the data that are being edited on the form, the other tables are used to populate DataCombo controls.

(Relevant???) settings for Adodc1, the one with the data being edited:
.CacheSize=50
.CommandTimeout =30
.CommandType=ad CmdUnknown
.ConnectionStri ng=<set from code to facilitate flexible change of the
location of the db>
.ConnectionTime out=15
.CursorLocation =adUseClient
.CursorType=adO penDynamic
.LockType=adLoc kPessimistic
.Mode=adModeUnk nown

Settings for the other Adodc controls (Adocdc2 to -6), used to populate the DataCombos:
same as Adodc1, except .Locktype=adLoc kReadOnly

The DataCombos have their .RowSource and .ListField set at design time.
.DataSource and .DataField are empty, because the destination depends on a
user-selectable option. The corresponding value in Adodc1 is set in the
_Change event of the DataCombo.

In Sub Form_Load of the form, the listboxes are populated with the following code (with a different table name and adodc object for the different
combos):

strSql = "SELECT * FROM landentabel"
Adodc2.Connecti onString = strConnectionSt ring
Adodc2.CommandT ype = adCmdText
Adodc2.RecordSo urce = strSql
Adodc2.Refresh

The delay users are experiencing is distributed evenly over the 6 tables.

I hope this enough information and thanks in advance for your help!
tim