How do I allow multiple users access to my db at the same time? 3

[EarlJohnson]

I've develped an Access 2000 database that needs to be shared with other users in my area. There may be times when all three of us will need access to it at the same time to add and/or edit records. It is in a shared folder on a network drive.

I've done this before but without usng VB, but unfortunately, I cannot locate my notes for it. Can anyone help?

Thanks in advance.

Earl Johnson, Jr.

 

[LittleSmudge]

It is acually so easy that your problem is that you are thinking about it too much.

Each person runs the Access application on their machine and then opens the same central file.mdb - as long as they open it shared ( Not Exclusive ) then all will be well.

You can read, write, modify, and delete data at the same time as long as you are not working on the same 2kByte memory pages. If you do you'll get lockout problems - but on a large database with plenty of data it becomes rare.

Access 2000 has also fixed the old bug that prevented two separate users adding new records to a table at the same time within the same memory page.


'ope-that-'elps.

G LS

 

[evalesthy]

Go to Tools --> Database Utilities --> Database Splitter and follow steps for splitting database. Front end will contain queries, forms, macros, modules, reports, while the back end will contain your tables. Put the back end on the server and a copy of the front end on each desktop. If you need to relink the front end to the backend use the Linked Table Manager which is also on the Tools menu. This will get you started.

 

[seb2]

Hey Evalesthy,

How do you do it in '97?

Tools --> Database Utilities --> (Database Splitter- not an option) I've got 'compact', 'repair' & 'mde' as options...

 

[LittleSmudge]

In Access 97 the first thing you have to do is BUY Access 2000 upgrade.

The cheaper alternative is :-
Compact and backup the database
Make a copy of the Database.mdb and call it DataBaseFront.mdb
Make a copy of the Database.mdb and call it DatabaseBack.mdb

Open DatabaseFront.mdb and delete All of the non system tables and recompact

Open DatabaseBack.mdb and delete all of the Queries, Forms, Reports, Modules and the recompact

Open DatabaseFront.mdb and go to File menu, Get External Data, Link Tables ..
Find the DatabaseBack.mndb in the dialogue box and then Select All option to get all of the tables.

DatabaseFront.mdb will now function like the original yet the data is stored in a different database to the Forms et al.


QED

G LS

 

[seb2]

Thanks LittleSmudge!

I was afraid that was the answer. Was feeling momentarily optimistic when I read about the 'splitter'.

I've got 2000 at home, but of course this is a work project and they don't wanta upgrade. :-(

Thanks for your prompt response!

 

[EarlJohnson]

Thanks to all for responding. As soon as I can get IT to install the Database Spl;itter and Link Tables features, I can get this resolved.

Thanks again.

 

[LittleSmudge]

NO Earl, Thats my point.. ..

For what YOU want to do it is NOT NECESSARY to split the thing.

For what you are wanting to do splitting the database buys you "two tenths of nothing at all".

Each person runs the Access application on their machine and then opens the same central file.mdb - as long as they open it shared ( Not Exclusive ) then all will be well.


G LS

 

[starsky51]

Access 97 does have a Database Splitter as an Add-in.. Go to Tools->Add-ins->Database Splitter
If it isn't there use Tools-Add-ins->Addin Manager and select the Database Splitter from the list.

starsky51

 

[seb2]

Hey thanks!



I knew I had seen it somewhere, but thought I was getting '97 confused with 2000.

 

[JohnBOA]

Thank you Little Smudge!! With SHARED selected this work great.

JJO

 

[rtalipski]

Earl,
A few words of caution... Access was not built to be a multi-user database on it's own. So the larger your database gets and the more people that logon the slower you database and network will get. The reason being is that access has no "trafficing" capablities. When a user pulls up a record they don't just pull up that record they pull the entire table across the network. Now take into account a couple different users pulling table(s) of records back and forth across your network (when the record is written back to the database again the whole table travels across the network). After a while it could bring your database and network to a snail crawl. I know this from personal experience where a vendor came in trying to sell a package designed to be a standalone and said it could be networked like you want to. I advised management not to but against my advice they forged ahead and of course it got to the point of users waiting sometimes 5-10 minutes to change one record (maybe just a few characters) and dragging the network down. Now if your talking a couple hundred records great but we had a couple thousand and about two users on at the same time and the above would occur. Sorry about the lengthy warning. Now another bit of advice if your still awake that is... Make SURE that you have RECORD LOCKING in place. By default there is none, and it will cause your users a world of grief if they happen to be editing the same record. Again sorry for the lengthy advice.

Ray T

 

[ashishh]

Thanks LittleSmudge,

Your answer helped my situation. I have just a basic Access DB which needs to be simultaneously by 3 users (add/modify/delete). It worked perfectly.

Thanks,
Ashish