File Sharing

[Vandelay]

If want to create a database with 4 or 5 work stations for inputting data, can I file share through windows or do I have to make 5 different copies and connected through relationships?

Which is the easiest?

 

[BNPMike]

Access, using the default Jet database, is multi-user out of the box. All you need is to point people to the database .mdb which typically will be on a file server.

 

[awhitsel]

Your best bet is to split the database in half, place the half with the data at a central location, and place the half containing the forms that users will be working off of on each user machine, and link the tables on the user's machine to the tables at the central location.

The maximum amount of users you can do this for is 5. Any higher than that will require you to use a SQL Server database for your data. The reason for this is that if you put more than 5 users on your application, it will dramataically slow down your system.

 

[BNPMike]

I think Jet databases can support more than 5 users. It rather depends on the level of activity. Also whether you need to split the data or not will depend on your network and fileserver. As these get noticeably faster every year I'm not sure old rules of thumb can be used. I'd just try a plain shared mdb and then stress test it to see if you need to do anything more fancy.

 

[taylorantone]

I'm a relative n00b myself, but wouldn't setting up the database on a shared location with workgroup permissions be easiest? I've got our inventory database out on the file server set up like this, and 5 of us currently use it.

 

[psemianonymous]

The max of 5 (concurrent) users is an MSDE limitation, not a JET limitation.

JET databases can handle well over five people, usually. I haven't seen anyone on this board complain about multiuser performance with any userbase that small. I wrote a FAQ about this, which basically says the above:

How many (max) users can Access support? faq181-4462

It also says: you'll know when you're straining the multiuser capabilities of Access when one or both of the following occur:
-massive, noticeable performance loss
-recurring corruption (can be caused by other factors)

 

[Hap007]

Splitting the application into a frontend (Programs, code, queries) and the backend (Data Tables) is always a good programming concept. This is almost always true (particularily if the backend is updated by user(s). The only type of exception might be an Access application that only does reporting or saves no data.

Here are 3 good reasons to split:
1) If your data and forms are all stored in one file, then maintenance of forms and code becomes a nightmare. Yopu should never modify forms that users are currently running. And if you make a copy to develop and test, then the live data is not the same as the test data. So when your new version is complete, you need to import the live data. Much better if you just link to your data, test or live.
2) The form and code part of an Access mdb is much more likely to become corrupt, where the data is less prone to that. So, if split, then you would always have a good copy of the Frontend and the data backend will run more efficiently.
3) When Split, each user can run the Frontend on their local PC. This is more efficient and depending on how you have coded your program, the only way to insure data integrity.

Regarding running on local PC, each user running their own copy. Well, if you use global variables, or temporary work tables, then if user 1 builds a temp table to produce(say a report), well if user 2 runs the same report as user 1 with different criteria, well results could be unpredicable. This is just an example.

Regarding users, I have had good luck with up to 50 users all accessing an Access backend, provided that not all users are updating the same tables all at once. Just depends on your user needs and how you program the Frontend. In theory, access can have 255 users open the same mdb file at once, but don't try it. I have had one company where 10 users were updating the backend, but close to 100 users were connected and reporting.

Hope this Helps,
Hap



Access Developer

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

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/groups/accessDevelopers

 

[BNPMike]

MSDE doesn't have a limit of 5 users. It has a limit of 5 threads. As far as I am aware you can support more than 5 users - they will just slow down whenever they have to queue for a thread.

 

[Hap007]

I think the MSDE limit is 5, and you can some how upgrade to 10. Microsoft does not want too many users, since it is just a SQL lite engine. Instead, they want you to buy big MS SQL if you have 10 or more users.

That is why Access and Jet was such a good deal. If you programmed it correctly, you could get 50 users, all running good and for free.

Soooooo..., Microsoft saw all the money going down the drain, and are trying to plug the leak...

They did the same thing with Terminal Server 2000 compared to Teminal server 2003

Hap...



Access Developer

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

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/groups/accessDevelopers

 

[BNPMike]

You can actually have over 32,000 connections (see the Microsoft documentation below). The governor however starts to delay things when 8 threads are being processed. So you may well be able to support 50 people given that they are not ever going to be pressing the button simultaneously.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_0ciq.asp

 

[Vandelay]

So basically I need only one (1) front end and one (1) back end. From there I just place the front end on the server with short cuts on each individual computer terminal right? Because right now I actually placed a complete front end copy on each machine, but what your telling me is that I only need one to file share....

Thanks.
Vandy

 

[Hap007]

Dear Vandy,

Someone in this thread may have suggested placing the FrontEnd on the server. That would not be me. Everyone has there own opinion and personal good or bad experiences.

My experience has shown that placing the backend on the server and then placing a copy of the FrontEnd on each user's local hard drive is the best.

Perfomance is better, less chance of corruption, when one user changes a report printer setting, it will not affect other users. (Just some of the reasons)

Note, you can store the Frontend on the server to make it easier to copy to each user, but I suggest never running the 'Server' copy.

I know that some will disagree, but I found, when supporting 100 users, that this worked the best.

Regardles of where you run access Frontend, each user will still need a copy of MS Access, so this is not an issue.

Hap

Access Developer

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

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/groups/accessDevelopers