What's The Best Method For Creating A Multi-User Solution? 2

[BenSC]

I'm looking to create a database that approx 30 people will access simultaneously. I've never created a multi-user solution before so would really appreciate any advice on how to best accomplish this.

Thanks In Advance
BenC512

 

[danvlas]

Welcome to the jungle!!!

First, I think you are at the limit with 30 simultaneous users. I know, Access specification says something about 255, but...don't have to believe everything Microsoft says...

Then, it's such a vast subject and there are so many things to say about it...

Basically, you will have to detect the 'collisions' between users. You need to remember the golden rule: One user at one time can edit one record. All others have to 'take numbers' and wait.

And...if anything wrong can happen, it will happen.

All editable forms: RecordLocks property: Edited record

That's for a start...



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[BenSC]

Thanks Daniel, that's a good start for me. I've been looking at splitting the database into a front and back end or using replications of the database, which are periodically synchronised. Help is fine on telling me how to do it, but it doesn't have anything on when these approaches should be taken. Do you think one of these approaches could be useful?

Thanks
Bc

 

[danvlas]

I think Replication was just an idea of someone at Microsoft who said 'Let's see what happens if'...
I don't think it's a reliable approach and I have never used it. Therefore I may be wrong and if I am, I stand corrected.

But splitting the db is a MUST. This way you have more flexibility in administering, upgrading the program and even backing up the back end from the front end.

Split your db.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Hap007]

Dear Dan,

Several things to consider.
1) If you are going to have 30 users, then you almost must do a frontend/backend, where the frontend is located on each users own PC
2) Thirty users are fine if only several users are updating the database at once. If say 10 of your users are doing data entry while 20 are doing reporting, that should be no problem. But, if you have 30 users doing heavy data entry and or updating all at the same time, 8 hours a day, you may be at the limit of access from the start, so you may want to consider SQL Server for the backend.

I have systems with over 50 users, but only 5 or 10 users are actually updating the database.

Note: Having 30 users update data will not fail in Access, it is just that your performance may not be acceptable.

Hope This Helps,
Hap



Access Developer

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

-
Specializing in Access based Add-on Solutions for the Developer

 

[LittleSmudge]

Daniel,

I have had experience of Replicated databases - so I'll add my comments on the general theme and then chuck in a question .. ..

I needed replication because a database contains some very large tables and a group of users ( 6 ) are on a remote site with a very narrow pipe between them and the main datastore.

So I created a replica and sent it down to them
( Database is split FrontEnd and BackEnd ofcourse )

Although both sites need access to the full data set and both sites are adding data - they very rarely EDIT each other's records - so edit clashes are very rare.
We then synchronise every couple of days and it works fine.

The real pain in replication occures when multiple edits are made to the same record on different replica databases. This then need human intervention to sort out the clashes.


Our next step is porting the back end onto a central MySQL db in a Client/Server operation.
Sending only one record at a time from the backend ( in MySQL ) to the Access front end gets rid of the network capacity problem.

HOWEVER, this will then be expanded to 8 sites. In order to make updates and maintenance of the front ends easier I intend to place the Access FrontEnds on the central Server that is physically at each site. The users can then access this front end as a multi user database just as effectively and using Access FrontEnds that are on their own C:\ drives.

Is the move to FrontEnds on site servers a reasonable approach - or do the front ends really need to be placed on idnividual C:\ drives ?







G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[danvlas]

LS, thanks for the info on replication.

As for the front end location, I think it's a matter of taste...

My absolutely personal preference is to have it on the file server and on a backup CD. I am too lazy to walk through users and see if they actually have the latest version. I know, I know, there will be people here saying it can be done automatically, but it's an extra step that can be taken or not. And I don't like taking chances...


But you have to think about the impact on the server's performance...




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[LittleSmudge]

I don't believe server performance will be an issue in this case. The servers on the remote sites are not exactly being loaded heavily at the busyest times.

I'm inclined to go with FrontEnd on local servers and the BackEnd on MySQL using ODBC to connect the two.
That give a compromise between ease of management of updates to the FrontEnds Vs minimising network traffic - esp along the long narrw pipes.




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[JeremyNYC]

Splitting FE and BE is non-negotiable. It must be done if you're going to have multiple users. Not doing so is asking for a LOT of trouble.

I always have front ends on the user's PC. Much less traffic over the network and, from what I hear, less chance for corruption.

There's no need to walk around making sure everyone has the latest version. Just use a batch file to roll out the new version, if (and only if) the user doesn't already have it.

I've got a couple of pages about how to do it on my website, but here's a simpler example than the one that's up there:
rem make the directories (no harm if they're already there)
c:
cdcd "Program Files"
mkdir "NRP Database"
cd "NRP Database"

rem If the latest version file is on the PC, just start the database
if exist "NRP_FEVersion.1.83.txt" goto Startup

rem copy the images and shortcuts
copy "\\Nrp\C\NRP System\NRP_FE.bmp"
copy "\\Nrp\C\NRP System\ReportFooter.bmp"
copy "\\Nrp\C\NRP System\ReportHeader.bmp"
copy "\\Nrp\C\NRP System\NRP Database.lnk"
copy "NRP Database.lnk" "C:\Documents and Settings\All Users\Start Menu\NRP Database.lnk"
copy "NRP Database.lnk" "C:\Documents and Settings\All Users\Desktop\NRP Database.lnk"

rem copy ABCD Library
copy "\\Nrp\C\NRP System\ABCDLibraryXP.mde"

rem Copy a new front end
copy "\\Nrp\C\NRP System\NRP_FE.mdb"
copy "\\Nrp\C\NRP System\NRP_FEVersion.1.83.txt"

:Startup
rem fire it up
"C:\Program Files\Microsoft Office\Office10\msaccess.exe" "c:\Program Files\NRP Database\NRP_FE.mdb" /wrkgrp "WellThisPartsASecret.mdw"

Any time there's a new version to be rolled out, I change the name of the version text file and change the two references to in in the batch file. All the user gets is a shortcut that points to this batch file.

Jeremy



==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[LittleSmudge]

Nice one Jeremy.

That's gotta be an 'earner'


Your vote for JeremyNYC for TipMaster of the Week has been submitted.

Thank you for letting JeremyNYC know this post was helpful.




Graham

 

[hymn]

Remember that the record locking has to be be set to No locks.

Tools
options
Advanced

And that all machines have to be set the same

Hope this helps
Hymn

 

[danvlas]

Hymn: Definitely NO.

'No locks' will drive users crazy as two or more people can edit the same record at the same time without being aware of the conflict. The first who saves the record thinks he's home free, but each of the others will have the possibility of changing what the first had done (a message that I don't recall very well, asking user what changes should be kept).

'Edited record' is usually the way to go. Sometimes (rarely) 'All records' is necessary, but 'No locks' is a disaster.

This option should only be used in reports.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[hymn]

Danvlas
I stand corrected.
My main concern is that some people like to braid themselves and open the database exclusive.

Hope this helps
Hymn