Multiple Users Hitting An Access Database

[Courtney Wright]

I have a VB6.0 sp4 program that accesses data from an Access 97 database via
DAO 3.5. That database that my program access, TemporaryDB.mdb, are just a
group of queries that link to tables in 3 other *.mdbs. My program runs the
queries in the TemporaryDB.mdb and loads the info into a recordset within
VB. To process all of the queries I have, it takes about 15 seconds. This
is acceptable. But as soon as I open up multiple instants of my program,
the data return time doubles for all instants open.

1 instants: 15 secs
2 instants: 30secs
4 instants: 60secs

This is not acceptable. Is there anything I can do to maintain the 15 sec
data return time for all open instants? This is urgent, thanks...

Courtney

[Brian Griffin]

A big part of the problem with Access as a back end occurs when you design
your system so that clients connect to the database then stay connected. In
a multi-user environment, this can push Access to it's limits rather
quickly.

I have written systems using Access as the backend where I use VB to create
an ActiveX exe that has all of the data access code, I call this exe the
data engine. The data engine is deployed on a network server along with the
Access mdb file. DCOM is used by the clients to hit the ActiveX exe.

When a client needs data, they create an instance of the worker object in
the data engine and request the data. The data engine does the work then
returns the data. When the client is done with the call, the data engine's
worker object is released. NOTE: the client should not "hold on" to a data
engine object, as this nullifies the benefits of this architecture. When
the object goes out of scope the connection the object holds is released.

I use ADO to hit the Access database, and configure ADO to use session
pooling, so that released connection handles are not released.

Designing an Access system for use on a network in the same manner you
design a standalone access system doesn't work. Using the basis of the
architecture that I outlined above, I have personally created systems that
use Access as their data storage mechanism that can serve several hundred
users.

"Courtney Wright" <cwr...@tdc-group.com> wrote in message
news:O5AHxizFAHA.65@cppssbbsa04...

[Courtney Wright]

Thank you for the detailed response!. I'm not familiar with making ActiveX
exes or the processed you used to create your multi-user program. Since you
have set this up personally, I doubt there are code examples of this right?
Are there any basic steps you could provide me as to how to go about
building a simple application that uses this architecture?

Your help is greatly appreciated!

Thanks,
Courtney

"Brian Griffin" <no.spam.br...@cscg.org> wrote in message
news:EA7t5.17078$58.22...@typhoon.tampabay.rr.com...

[Prodip Saha]

You may not need to use a server to manipulate the trasactions. You can
simply have a module or class module within your project to host some
functions which will return the DISCONNECTED recordsets. You can have your
function even on your client form.

Module1:
Public Function GetRecordSet(strSQL as string) as ADODB.Recordset
Dim objCn as ADODB.Connection
Dim objRs as ADODB.Recordset

Set objCn=CreateObject("ADODB.Connection")
Set objRs=Createobject("ADODB.Recordset")

With objCn
.ConnectionString = strCn 'Use your connection string here
.ConnectionTimeout = 60
.Properties("User ID").Value = strUID 'You may not need for
Access Database
.Properties("Password").Value = strPWD ''You may not need for
Access Database
.CursorLocation = adUseClient
.Open
End With

With objRs
.CursorLocation = adUseClient
.ActiveConnection = objCn
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Source = strSQL

End With

objRs.Open

Set GetRecordSet=objRs 'Here you get what you need

' Release all your resources
Set objRs.ActiveConnection=Nothing
If objRs.State=adStateOpen then
objRs.Close
End If
If objCn.State=adStateOpen then
objCn.Close
End If

Set objRs=Nothing
Set objCn=Nothing

End Function

Good luck.

Prodip Saha
Programmer Analyst
EMC Mortgage Corp.

Courtney Wright wrote in message ...

[Brian Griffin]

Transactions are not the issue here. The issue is that an Access database
can only handle a certain number of concurrent users, and the poster is
feeling the pinch of how the JET engine handles multiple users on an Access
mdb file. Using the server allows you to create a stateless component that
enforces an architecture that is designed to mitigate the multi-user issues
common to Access.

Your approach involves the client establishing connections from the client
over the network. Furthermore, your approach implies a one to one
relationship of users to database connections. This is what you want to
AVOID in a multiuser Access application. Using OLE-DB session pooling, it
would be MUCH more efficient to server 20 clients off of 7 cached connection
handles that are being managed by the session pool and whose connection
allocation is local to the machine that the mdb file is located on!

Your proposed solution would also be considerably more network intensive.
Given the time lags described by the poster, I suspect that the network is
part of the delay. The last thing you would want to do is to INCREASE
network traffic, as would be required by releasing and acquiring a client
side database connection as needed the marshalling a recordset object to the
client.

Your proposed design is one of the design approaches that I have found
(Through experience) to cause problematic VB->Access implementations. I
have designed and implemented 7 systems using Access and the architechture
that I described. They all run beautifully. They don't experience the
database corruptions and unusual behaviors that many Access developers deal
with.

I have one particular system using this architecture that serves 160 users.
It is quick, does not corrupt, and my client is very pleased with the
implementation. I re-designed their system after they had spent 18 months
trying to get an architecture IDENTICAL to the one that you propose to work
(They never got it to work reliably or consistently). It took me 5 months
to re-design and re-implement the system from their existing code line into
the architecture that I have outlined. The system has run in production now
for 14 months with no problems or down time.

Using a server component also allows for a scalable system architecture.
Single tiered systems such a you are proposing do NOT scale. They reach
their limits and must be re-implemented to increase the maximum user load.
Companies DO NOT like that!

"Prodip Saha" <ps...@bear.com> wrote in message
news:IFat5.32$KE1.44834738@IConNet...

[Robert Paquette]

Brian,

How can you be reached? I tried your email, but it won't deliver. Please
email me.

Thanks,

Robert

Brian Griffin <no.spam.br...@cscg.org> wrote in message
news:EA7t5.17078$58.22...@typhoon.tampabay.rr.com...

[Tim Snyder]

Brian, your response gives solid testimony to the efficacy of your approach.
How about giving us some clues as to where to find examples of how to do
this ourselves so we can provide our clients with the same solid design?

Thanks,
Tim

[Brian Griffin]

In dealing with Access, I don't know that there are any good samples or
books. The things that I was laying out are based on my own experience and
was learned largely through trial and error. I learned this by implementing
7 different multi-user systems in VB with Access as the database with each
subsequent implementation getting better than the last. I've always been
strong in object oriented and tiered developement having spent alot of time
developing in C++ and VC++ on client server systems.

One book that does an *adequate* job of explaining n-tiered COM
architectures such as I have described is "Professional Business Objects"
published by WROX press and written by Rockford Lothka.

Anything that discusses tiered COM architectures would be applicable.

Brian Griffin

"Tim Snyder" <tsn...@compusolutions.com> wrote in message
news:Jqbu5.349022$t91.4...@news4.giganews.com...

[Andrew Grillage]

Brian,

Your design is certainly the ideal but there is one problem I can envisage
in certain situations which I'd like to know how you deal with. If you are
building for a single client then fine. You can build your ActiveX exe,
deploy it and set up the clients. What if you are creating a product to sell
multiple times and you need a setup program which a regular user can deal
with. Deploying this kind of architecture is a nightmare unless you set it
up yourself or have someone on site that really knows what to do. And if we
talk about workgroup products that might have 10-15 users then it is not
unreasonable to expect that the customer will not always want to pay for
on-site install and support.

Any ideas on deploying this stuff or do you always do custom, one-site jobs?

--

Andrew Grillage
http://www.concresco.com

Brian Griffin <no.spam.br...@cscg.org> wrote in message

news:YSbu5.12795$98.12...@typhoon.tampabay.rr.com...

[Brian Griffin]

I can see why you would think this, and it is a very good point. I can
appear daunting.

Once you get in and understand what you have to do to setup a DCOM client
(It's really just 2 calls to CliReg32.exe) you realize that all you need is
a server name. The DCOM client can be setup programmatically provided you
have the server IP address when the client install script is run. I
typically set up the install to read from an ini file that my client can
place their server IP address (My clients almost invariably move the install
images off of CD and onto a network drive for mass to their user base).

Some of the "shrink wrap" products that I provide will also prompt for the
IP Address in the absence of a value in the ini file. So here you probably
say "Well, the client has to be savy enough to know their IP address? My
client's can't turn their computer on without help!!" If you are deploying
a tiered architecture to different machines, they have to install the server
and the client separately. Have your documentation indicate that they
should install the server first. When the install scripts are run on the
server, read the IP address for the server machine and prominently display
it to the end user informing them to write down the address because they
will be asked for this value when installing clients. You should call the
IP address the same thing in both installs so that you don't confuse the
user.

Another option that I have used is that the client install image is copied
onto the server machine by the server install scripts (ala MS Visual
SourceSafe), as part of the server installation, the aforementioned ini file
is populated with the IP address of the server. The client install scripts
are run from their location on the server, where they can refer back to the
ini file for the IP address they need to configure the DCOM client. Viola -
No user knowledge of the IP address required.

Another factor is that most times, the consumers of applications that
require high user loads and scalability (Which are the factors that would
drive you to this type of architecture) are larger companies. They have IT
staff that don't find this stuff daunting, and often times routine.

Like most things, the more familiar you become with DCOM as well as
developing and deploying DCOM apps; the less daunting and complex it
becomes.

Thanks,
Brian

"Andrew Grillage" <and...@concresco.com.nospam> wrote in message
news:utaB2aEHAHA.242@cppssbbsa04...

[Brian Griffin]

The first line of my previous post *should* have read "It can appear
daunting" not "I can appear daunting".

"Brian Griffin" <no.spam.br...@cscg.org> wrote in message

news:Xzev5.41301$58.54...@typhoon.tampabay.rr.com...