Multi Users in Access

[primfran]

I developed an Access app that was originally designed for a small number of users.

Since then the popularity has grown and so have the number of users.

Has anyone had any experience with multiple users (>10) simultaneously accessing a table cause performance issues?

The app has a form with combo boxes that pull the items from a table in a backend db. Occasionally these combo boxes fail to retrieve the items.

I am thinking too many people are accessing the back end to support the combos, but not sure.

Please help.

[Samurai Danny]

I have had performance issues with 2 or more people using an access database.
It sounds like it is time to move to real a database server.

[randem]

primfran,

You can have performance issues with Access, but mostly it is ones code that needs to adapt to better techniques. Bad techniques show up really quick while using Access.

Please read Database Problems in my signature for some help on this issue.

[RobDog888]

If users are doing updates to the data while having multiple users connected you are going to run into issues with Access. Access can only support 7-10 concurrent connections when your db is designed for updates. If your only reading or retrieving data then it supports allot more. This is a known issue with all versions of Access. You need to code your app to accomodate for this.

[eranfox]

Hello primfran,
Did you try using this:

DBEngine.Idle dbRefreshCache

Before you execute queries?

this can help you with Multi Users/Tasking.

The Idle Method forces the database engine to write data to disk, releasing locks on memory. (just WRITE idle and press F1 for help)

Best Regards,
ERAN

[dee-u]

Hello primfran,
Did you try using this:

DBEngine.Idle dbRefreshCache

Before you execute queries?

this can help you with Multi Users/Tasking.

The Idle Method forces the database engine to write data to disk, releasing locks on memory. (just WRITE idle and press F1 for help)

Best Regards,
ERAN

That is for DAO right? What if he uses ADO?

[eranfox]

That is for DAO right? What if he uses ADO?

Hello dee-u,
Yes you are right!

DBEngine.Idle dbRefreshCache

Is part of DAO.

For ADO primfran should use Lockings (LockType)
And He can use disconnected recordsets as well.
He must do all calculation outside his queries and close connections when he can.

DAO is the native language of Jet, is faster for Jet than ADO.
ADO is Better than DAO when you use DBMS like SQLServer But when using MS-Access it is not that strong.

Best Regards,
ERAN

[dee-u]

Hello dee-u,
Yes you are right!

DBEngine.Idle dbRefreshCache

Is part of DAO.

For ADO primfran should use Lockings (LockType)
And He can use disconnected recordsets as well.
He must do all calculation outside his queries and close connections when he can.

DAO is the native language of Jet, is faster for Jet than ADO.
ADO is Better than DAO when you use DBMS like SQLServer But when using MS-Access it is not that strong.

Best Regards,
ERAN

I have used such technique before hence it is familiar...

What I wish to clarify now is wherer did you get the knowledge that DAO is faster for Jet? I'm interested to know since I always thought ADO is faster than DAO in all areas...

[eranfox]

Hello dee-u,
I got my knowlege only from my personal experience.
I hate using DAO but for some reason ADO failed for me in some cases.
Any way that was long time ago and since then i moved to MS-SQLServer.

Best Regards,
ERAN

[dee-u]

That's strange since ADO doesn't fail me with Access...

[eranfox]

Hello dee-u,

The DAO object model is designed specifically for the Microsoft Jet database engine. Jet itself incorporates ISAM and ODBC connectivity and makes the back-end providers look as much like the native Jet engine as possible, though this comes at the expense of performance. DAO also has an ODBCDirect mode that allows it to host RDO objects and access ODBC datasources in a very efficient manner.

The ADO object model was designed for OLE DB providers and is a much simpler and more flexible object model than DAO. However, its architectural design poses some problems when using the Microsoft Jet OLE DB provider, and it is more limited than DAO in Jet functionality it supports.

Taken from :
http://support.microsoft.com/kb/q225048/

So, maybe you didnt have problems with ADO and JET but i did and thats history...

Have a NICE day,
ERAN