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 1 1792
"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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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.
...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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
|
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...
| |