making a database multi user

[arobbo]

This might be very simple, i've read a lot about DDBMS but am unsure whether you can make a database built in access able to have multiusers using it at the same time...

anyone got any advice/ideas

cheers

Andy

 

[jrbarnett]

Theoretically Access can have up to 255 concurrent users, according to MS. However, after about 5 concurrent users, system performance deteriorates quite significantly.

You don't need to do anything special to make an Access database multi user, only to ensure it doesn't open in exclusive mode.

John

 

[arobbo]

excellent news ....

how would make sure that it isn't set to open in exclusive mode,

also any ideas how large an access database can get before it starts to struggle ?

 

[BNPMike]

At this site there are applications over 1Gb and no-one is complaining.

 

[arobbo]

excellent , thanks for your help

any ideas where about you set the access database to not open in exclusive mode ?

 

[BNPMike]

Have a look at this:

http://www.tek-tips.com/viewthread.cfm?qid=1027471&page=1

 

[MoLaker]

arobbo,

Look in Access options under Tools on the top menu. The Advanced tab exposes 2 settings which are important in configuring a database for muli-user mode. The first is Default Open Mode which should be set to Shared. The second is Default Record Locking which usually should be set to Edited Record.

Another consideration is the directory permissions where the database is located. All users should have "Change" permissions for that directory or "Read", "Write" and "Delete" permissions.

 

[easyit]

Hi,

Multiuser means at least two things - split your database in front and backend & enforce security (you dont want 20 administrators using your database). I have several db´s with 10 & 30+ users and I am developing a db for 100 users - of which I am convinced that it will work fine.

Besides the two things mentionend there are several things you can do to improve performance like unbound forms (!!) switch of namecorrection (somewhere in extra->options one of the tabs - sorry I have a localized version dunno the english terms).
There is a book with a section devoted to optimization (access 2002/2000 development by SAMA publishers ISBN 0-672-32120-3 amazon has the quit cheap).

Maarten

 

[Hap007]

Dear Maarten,

I agree, splitting the db to frontend/backend and placing a local Frontend copy on each user PC will greatly improve performance. I have a client, running access97 and performance has been great over the last 5 years. Database has grown to over 1 gig and there are over 50 users updating, searching and reporting on the data.

Note: Regarding unbound forms.
I agree that an unbound form works great, but a simple alternate solution is to have a form's record source set to a 'Null' Query. This returns no records, opens just as fast and is easier for the beginning Access programmer to use.

Query might be:
Select * From tblEmployee Where EmployeeID = 0;

In my case, EmployeeID is a 'Key Field', AutoNumber and must always have an entry 1 or greater, so when the form opens, there are no records returned, yet it makes it easy to do development work.

This works great and then I just change the form's Recordsource to a new query when I want to select a record.

That is the beauty of Access, many ways to solve the same problem.

Enjoy,
Hap...



Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[TaintedFlames]

Hey,
an other thing you can look into is link your tables to a SQL Server. But then you need an SQL server, but you can tie your web page to access quite nicely.


Jay

 

[easyit]

Hi Hap,

what you write is interesting. Have you measured performance differences between unbound forms and nullqueryforms? And if there is no difference, it makes me curious why this is. I would say that a form when bound executes the source query in the same time as in your solution - which actually runs two queries.

I´m going to try it anyway, always nice to learn a new approach!

Maarten

 

[arobbo]

Thanks very much for all your advice , much appreciated.

I think i shall definately split the database into a front and back end, i have seen this done before and will mean the front end that i distribute can be display just the switchboard.

thanks again

Andy

 

[Hap007]

Dear Maarten,

I have checked performance, and found it to be acceptable.

I do have not have exact numbers, but it works in about the same time as an unbound form on first load.

Several tricks here:
1) Makesure that the null query is selecting on a Key field, hopefully the primary key at startup.
2) Make sure any dropdown/lookup boxes also follow similar rules.
3) The big one here, if you have any subforms connected to data, then make sure that the data source or the subform itself is not processing and loading data until needed!

I use this method against customer database with over 55,000 records and then on subforms showing invoice, checks, orders and shipments. these subforms are accessing tables with 400,000 + thousand records each, but using null queries and then queries selecting based on the parent(customer) record, well it works like champ.

Let Me Know how you make out,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121