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.Connection
cn.ConnectionString = strConnectionString
cn.Open
where cn is declared as 'Public cn As ADODB.Connection' and
strConnectionString is 'Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=<path>\kiezers_be.mdb;Persist 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=adCmdUnknown
..ConnectionString=<set from code to facilitate flexible change of the
location of the db>
..ConnectionTimeout=15
..CursorLocation=adUseClient
..CursorType=adOpenDynamic
..LockType=adLockPessimistic
..Mode=adModeUnknown
Settings for the other Adodc controls (Adocdc2 to -6), used to populate the
DataCombos:
same as Adodc1, except .Locktype=adLockReadOnly
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.ConnectionString = strConnectionString
Adodc2.CommandType = adCmdText
Adodc2.RecordSource = 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
