Excel VBA - ActiveX Data Objects (ado) Performance

[adam99]

Hi All,

I am working on an Excel Application which is a Fronted to a Microsof
Access Database. Initially the con-current users was going to be aroun
10, this has now blown out to 50 users.

Is there a figure that any one would recomend as the maximum number o
users?

When I run a query(based on table 'A') which is stored in the Acces
database are all the records in table A passed back to Excel via AD
then filtered via the query? If so this might explain my performanc
issues.

If anyone has any information on this or general performance with AD
it would be appreciated.

Thanks,
Adam. :

 

[Charles Williams]

With 50 users I would switch the backend to MSDE rather than Jet : MSDE will
perform significantly better (it is a proper client/server database and its
more robust).

The maximum number of users with Access/jet is mainly defined by performance
rather than number of users, but 50 concurrent would probably be considered
high by most people.

regards
Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

 

[Tom Ogilvy]

Charles Williams made this suggestion in the newsgroups - unfortunatel
the forum method for bringing the answers back seems to be havin
trouble.

With 50 users I would switch the backend to MSDE rather than Jet : MSD
will
perform significantly better (it is a proper client/server database an
its
more robust).

The maximum number of users with Access/jet is mainly defined b
performance
rather than number of users, but 50 concurrent would probably b
considered
high by most people.

regards
Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

 

[adam99]

Thanks Tom for replying,

There will have to be some serious testing as the number of users wil
be quite high. I think I will take your advice & ivestigate using MSD
rather than Jet.

Thanks,
Adam

 

[Jamie Collins]

I think accepted wisdom states the safe limit to the number of users
*connected* to a Jet database at any one time is approximately eight
users i.e. much lower than advertized. You can support more
*concurrent* users by implementing a 'disconnected' model e.g.
disconnected recordsets with client side cursors and either
batch-optimistic locking or updating via direct execution of SQL in a
transaction etc. In other words, users may have local copies of the
data, possibly with locks on that data to prevent other users from
changing it, but only actually connect to the database briefly to
fetch and update data. Whether this connection strategy is an
acceptable risk is discussed in this thread:

http://groups.google.com/[email protected]

I too recommend the MSDE route.

Jamie.

--

 

[Tom Ogilvy]

Just for information,
Adam99 can't see your answer at present. Even when Excelforum fixes their
feed, I don't know if these missed postings will be propagated. Good
information none-the-less.

 

[adam99]

Thanks everyone for responding, I agree with the article posted abou
using Access. I think we will have to re-think the package & number o
users.

Cheers,
Adam