By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,829 Members | 1,828 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,829 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
DFS
"Tim Dol" <ti*****@tiscali.nl> wrote in message
news:40**********************@dreader2.news.tiscal i.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.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

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.