Help with multiuser database

[PaulSpell]

I have a multiuser database installed on our network with about 30-40 users. At present it is a single shared database (i.e. not split). This is basically a management information system so no data is being updated by the users.

I have a couple of questions though:

1. Is it likely that the performance will be enhanced by splitting the database and installing the front end locally on users workstations?

2. What is the limit for multiple users with Access? Does this limit change depending on which of the above methods are used?

3. Finally, yesterday two users reported problems accessing the database. They got an error message saying "file is already in use". The database is setup as shared, so what might cause this? Could it be a network issue?

Any help would be greatly appreciated.

 

[Autoeng]

1. If there is no data entry going on there is likely to be no performance gain by splitting the db. If however, you are doind a lot of queries you might see a slight gain by splitting the db.

2. Microsoft says that Access can support 255 users but I have found that in a heavy traffic environ I start having problems after 10 concurrent users. I have had as many as 50 non-concurrent users and had no problems.

3. It sounds as though someone is opening the db "Exclusively". You can change this under Tools-Options-Advanced.

Autoeng

 

[PaulSpell]

Thanks for the reply, you've pretty much confirmed what I had thought.

The database is set to open as shared already, but can this be overridden by users (if their default is to open as exclusive)?

 

[Autoeng]

Read Access Help on "exclusive" and it explains it.

Autoeng

 

[PaulSpell]

Ok, I think that explains it. But how do I set to "shared" programatically (help isn't very good at this bit)?

I have tried using an autoexec macro but can't get it to work. Can you offer any help or do you have a sample piece of code that I could use?

 

[Autoeng]

Thinking more about your problem I wonder....

Do you have Access user level security implemented?

Do the users have to go through a shortcut to open the db?

Are some of the users finding a way not to use the shortcut to open the db?

I don't think that there is a way to programmatically set it to shared.

Autoeng

 

[PaulSpell]

Do you have Access user level security implemented? No

Do the users have to go through a shortcut to open the db? Yes

Are some of the users finding a way not to use the shortcut to open the db? More than likely.

Got any ideas?

 

[Autoeng]

What does your shortcut do?

Autoeng

 

[PaulSpell]

Simply points to the .mdb on the network drive.

 

[Autoeng]

Paul:

I'm at a loss for what could have caused #3 and have yet to find a way to code against exclusive opening of a db.

Sorry,

Autoeng

 

[PaulSpell]

Thanks for your help anyway.

Looks like I'll end up having to set up Access security control to solve the problem!

 

[Autoeng]

Read the Access security Faq on Microsoft's Knowledge Base website before you try. Security is very complex but the Faq is very good. I have posted links to the article in other posts of mine so search for user Autoeng

Autoeng