473,322 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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
1 1792
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Frnak McKenney | last post by:
Back when computer dinosaurs roamed the earth and the precursors to today's Internet were tiny flocks of TDMs living symbiotically with the silicon giants, tracking access to data processing...
10
by: John Phelan | last post by:
I read an article by, by Mike Groh, in Access-VB-SQL Advisor Magazine, Week 37 that concerns me quite a bit on distributing Access Applications commercially. First I need to describe a "commercial...
20
by: John | last post by:
Hi, I've recently upgraded from Access 97 to Access 2002 & the performance basically stinks. I have tried the following items listed below but it has only had a minor impact: 1) Upgraded Jet...
8
by: Thats Me | last post by:
Background: Access 2000 running on Windows 2000, Did not design inherited (three previous database maintainers in last 18 months), Non-existent comments for existing code modules and objects, six...
6
by: Terry Bell | last post by:
We've had a very large A97 app running fine for the last seven years. I've just converted to SQL Server backend, which is being tested, but meanwhile the JET based version, running under terminal...
33
by: DFS | last post by:
An application I wrote has been deployed on Citrix, and the Citrix admin tells me all users run the same .mde file. There aren't a lot of concurrent users, but even 2 could be cause for concern. ...
4
by: James | last post by:
I have a VB windows forms application that accesses a Microsoft Access database that has been secured using user-level security. The application is being deployed using No-Touch deployment. The...
34
by: Mathieu Trentesaux | last post by:
Hello I downloaded Office 2007 for this reason : It seems, once again, that it is impossible to save any modification done in a VBA library, from the main project in Access. The save button...
11
by: HC | last post by:
I posted this in one of the VB forums but I'm starting to think it might be more appropriate to have it here, since it really seems to be a SQL server (MSDE/Express 2005) problem: Hey, all, I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.