Creating a Multi User Enviroment

[JaredNJames]

Hi, i have made many access databases which run on standalone machines and perform tasks such as invoicing, stock checking and various other tasks for businesses.

My problem is, I have just been asked to allow multi user access to the system with updates performed almost instantaneously.

Heres the setup:
Computer One - linked to - Computer Two (via a computer to computer network using a simple cat5e crossover cable to allow access to the files on each system).

What I need is to have the database file so that both users can access it at the same time and any updates are virtually instantaneous.

At the moment with only one pc, if someone was to create an invoice the stock levels would all update automatically in the system so if a stock check is performed the correct stock level is always shown as it is only run on the one computer.

What i need is to be able to have the system open on both pcs at the same time and if for example User 1 was to create an invoice, and User 2 was to perform a stock check shortly after, the updated stock levels would be shown.

I trully have no idea as to how to go about this, and would like to keep it as simple as possible. Any help would be much appreciated.

Many Thanks
Jared James

 

[HHUK]

(In Access 2003, might be worded differently in other versions)
I would goto Tools -> Database Utilities -> Database Splitter

This would create a front-end and a back-end... the front-end would be the forms, simply an empty shell. The back-end would be your database tables, queries, etc.

I'll quote what it says:
"This wizard moves tables from your current database to a new back-end database. In multi-user environments, this reduces network traffic, and allows continuous front-end development without affecting data or interupting users."

Just remember to back up first.

The back-end file could be stored on the faster computer or a third computer and all computers who would need access to the database would need the front-end file. I like to make the back end file hidden so the front-ends can find it but the users can't.

The main "con" is that every time you update the front-end you would need to distribute a new version of the front end to your computers.

It should work seamlessly. If ever in doubt, make sure your form has a save button just incase the record is left open (better safe than sorry) and you could put a refresh function in before generating reports.

Hope it helps.

 

[JaredNJames]

Hi, thanks for the reply. I understand what you are saying here, but just require some clarification on a few points.

Firstly, so as long as the network is up and running and the users have the front end file, would it just "connect" to the back end automatically or do i have to specify its location?

Secondly, lets say User 1 creates an invoice and prints it (which saves and prints the data and closes the form), if User 2 now opened the stock check facility, would the data have been updated straight away? I am assuming it would as they are using the same tables, but would just like some clarification before implementation.

As one final point, how would i go about assigning security in the form of three usernames and passwords: Administrator, Staff and Office. I want full permissions for admin and then to restrict access for staff and office. I have tried using the access security setup before and just made a mess of it so any help would also be appreciated.

Many Thanks
Jared James

 

[David Eagar]

Hi, thanks for the reply. I understand what you are saying here, but just require some clarification on a few points.

Firstly, so as long as the network is up and running and the users have the front end file, would it just "connect" to the back end automatically or do i have to specify its location?

Many Thanks
Jared James

When the front end is linked to the back end, it remembers where it found it. It will connect every time, PROVIDED that the back end does not get moved or renamed.

Not expert enough to help you with 2 or 3, but hopefully someone will point you in he right direction

 

[JaredNJames]

Hi, thanks for that,

so if i split the file on Computer 1 and then move a copy of the front end to Computer 2, it will automatically know where it was split and connect to it?

thanks
Jared James

 

[HHUK]

Hey JaredNJames,

It's possible to move the back-end and then re-link them. If you go onto the tables tab in the objects menu. (Not quite sure of the official name, it's the thing that usually opens on startup of access) you'll see all your tables have a little blue arrow next to them, this means they're linked, if the back-end has been moved they will still be there but they might point to nowhere, I usually delete each one as they're dead links. Then right click in the white space then click "Link Tables", find the new location of your back-end file and select all tables, click ok and they'll appear on your table list.

Do this before you distribute the front-end or you'll need to do it for each copy you made which is just abit of hassle.

Your second point in your post is a yes, aslong as the form data has been saved the stock checker will be fully up to date. I suppose there can be slight syncing errors, like if the stock checker is opened just before the invoice is printed and saved... you can get around this by adding a refresh button or a timer that refreshes the stock checker automatically.

Even if things do clash, you could add a timestamp to your stock check results and your invoices which could help clear up any later misunderstandings.

The last point about security, I was stuck on this lately but this forum quickly cleared it up for me. I suggest the user-level security wizard. Make sure you do this last though as it is not fully necessary until the whole, new system goes live and you only really want one problem at a time.

Tools -> Security -> User-Level Security Wizard. Follow it through, make a few usernames. Hey presto, you can then go into the User and Group accounts menu afterwards to edit any permissions that you didn't set up while securing the database. I personally just use the User-Level Security at a minimum, then hardcode bits of it, e.g. If CurrentUser = HHUK Then
DoCmd open form etc etc
Else
msgbox ("lol")

 

[JaredNJames]

Thank you very much, i now understand this as well as i feel i need to.

Will re-post if necessary for help.

Many Thanks
Jared James

 

[JaredNJames]

Hi, Just one more thing, if User 1 was to access the invoice form and start creating an invoice, what would happen if User 2 attempted to access the invoice form and create an invoice?
Also, if User 1 is checking the stock on Computer 1 and User 2 then attempts to perform a stock check on Computer 2, would they both be able to operate the form?

Jared James

 

[JaredNJames]

Hi, just one more thing:

if User 1 is creating an invoice and User 2 attempts to access the form and create one as well, what would happen?

also

if User 1 is performing a stock check and User 2 attempts to perform a stock check at the same time, would this be possible?

Thanks
Jared James

 

[HHUK]

The first one:
The jet engine which powers Access should handle that sort of situation, I've tried breaking my own database by opening it from different PC's and trying to input data at the same time but it seems to handle it well. I believe it only starts to screw up if you have quite a large number of people trying to work on it. I've read Access is designed for less than 25 users.

The second one:
The stock checker is more of a reader so there wouldn't be any clashes there.

The thing to do is to give it a shot and then try break the program yourself once it's split.

 

[JaredNJames]

Many Thanks, will be in touch if i hit any snags.

you are right, i also read about the 25 user thing.

mine is only for 2 max so shouldnt hit problems there.

Many Thanks
Jared James

 

[Rabbie]

(In Access 2003, might be worded differently in other versions)
I would goto Tools -> Database Utilities -> Database Splitter

This would create a front-end and a back-end... the front-end would be the forms, simply an empty shell. The back-end would be your database tables, queries, etc.

Just remember to back up first.

The back-end file could be stored on the faster computer or a third computer and all computers who would need access to the database would need the front-end file. I like to make the back end file hidden so the front-ends can find it but the users can't.

The main "con" is that every time you update the front-end you would need to distribute a new version of the front end to your computers.

It should work seamlessly. If ever in doubt, make sure your form has a save button just incase the record is left open (better safe than sorry) and you could put a refresh function in before generating reports.

Hope it helps.

Not quite correct. The Backend should just contain the tables. everything else should be in the FE.

 

[HHUK]

Sorry, I realised that mistake after I had posted it.

 

[gemma-the-husky]

jared

generally there is no problem with more than 1 user accessing a dbs at the same time. access actually has a number of record locking strategies, the default being "no locks" and this is generally OK.

there is generally no problem with several users creating invoices at the same time- the crucial issue is more likely to be "how do you allocate invoice numbers". what happens to the number sequence if one user cancels his invoice

 

[JaredNJames]

Hi, invoice numbers are incremental autonumbers, so if one record is deleted the number simply continues as it would do if the record exists.
so if the records are:
1
2
3
4
and 4 is deleted it would now continue like this:
1
2
3
5
6
etc...

All i was wondering was if User 1 was creating an invoice (ID 123 for example), does the record show in the table immediately so that if User 2 was to start creating one whilst User 1 still has the form open, it would see that ID 123 has already been used and simply go to ID 124 and allow another invoice to be created.

Thats all im concerned about. Can both Users 1 and 2 create invoices simultaneously without any problems?

Thanks
Jared James

 

[gemma-the-husky]

i'm sure it wont be a problem - if it is, access won't let you do it - thats generally what happens

 

[DJNickWatt]

In a word, yes. Don't worry about details like that. The database engine handles at that for you.