Multiple Users in Access 2

[pcolvin]

I created a split Access database for use by about 5 people in my office. Everyone is able to open the database when no one else is using it, but it won't allow multiple users. I have set the default open mode to "Shared" and the default record locking mode to "No Locks." I am able to open the database even if there is someone else in the database, but all other users can only open the database when no one else is in it. When the other users click on the icon nothing happens if someone else is already in the database.

We regulalrly update forms, reports, and queries in this Database, so distributing the database to the users is not an option. They must be able to access it from a shared drive.

 

[Remou]

This is not going to happen. You cannot have two people designing the same database at the same time. Perhaps you can give each person a development copy and let them check new forms and queries into a shared repository that others can copy from.

 

[pcolvin]

I dont need multiple people designing. I just need to allow multiple users to enter data. When someone is in the database and someone else tries to open it nothing happens, except for me???

 

[Remou]

In that case you need a split database. The data resides on the server and the front-end containing forms, reports and perhaps some queries, resides on each user's PC, with tables linked from the server back-end.

Here is MS on the subject:

http://msdn.microsoft.com/en-us/library/aa167840.aspx

 

[Countymnca]

Is the front end file on the server or their desktops? Is everyone runing the same version of Access? There will be problems if someone is using 2000 and someone else is using 2003.

 

[Remou]

The front-end is on each person's PC. There should not be problems if one person is running 2000 and another is running 2003, because that is what they are already using to open the database. There would only be problems if the 2000 and 2003 people were both trying to do design work, and even then, there would not be much trouble.

If you do not want people tampering, create an mde using 2000. It will run on the 2002 and 2003 PCs.

 

[pcolvin]

The database is already split, the back end is located on the server. If I place the front end on each persons PC it allows multiple users to work on entering data at the same time, but that is where my problem occurs. I do not want to place the front end on each persons PC, I want the front to also be hosted on the server. As I mentioned, I will be making nurmerous updates and additions to the forms, reports, and queries for this database and it is not practical to ask each person to update the copy on their PC everytime another change or update is made.

I have another database that is used in a similar fashion where both the back end and front end are hosted on the servers and it works perfectly. I do not uderstand why I can't get this database to function the same way?

 

[Remou]

This is not the best practice. You can set up scripts that copy new versions when they are created. However, I guess you can probably get away with it, as you only have 5 users.

Check all users permissions and ensure that the have full permissions on the folders for the back-end and front-end of the database.

 

[boblarson]

You need to have on each users computer but, the good news is that you don't need to touch each computer for them to get the updated frontends. I have a tool which enables auto updating for any frontend. You would have to get it to them the first time and then after that, if you change the version numbers in the master file then it would tell them that their version is out of date, close the file, delete the old frontend file, copy the new one from the server, and automatically reopen for them.

You can find it here:

http://www.btabdevelopment.com/main/MyFreeAccessTools/tabid/78/Default.aspx

Bob Larson
Access MVP
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[Hap007]

Dear pcolvin,

Although you set Access to open the FrontEnd shared on your PC, it sounds like you did not do that on each of your user PCs.

So go to each user PC and make sure that their version of MS Access is also opening the 'shared' Front End as Shared Access.

A Second way to do this would be to create a Shortcut to the FrontEnd that will open the program in shared mode.

1) An Example of a shortcut that opens 'Exclusive' might be:
"C:\Program Files\Microsoft Office XP\OFFICE11\MSACCESS.EXE" /excl /wrkgrp "C:\Program Files\UA\UA65FS.mdw" "C:\Program Files\UA\MenuManager.mdb

2) An Example of a shortcut that opens 'Shared' might be:
"C:\Program Files\Microsoft Office XP\OFFICE11\MSACCESS.EXE" /wrkgrp "C:\Program Files\UA\UA65FS.mdw" "C:\Program Files\UA\MenuManager.mdb

3) An Example of a shortcut that opens 'Shared' no work group might be:
"C:\Program Files\Microsoft Office XP\OFFICE11\MSACCESS.EXE" "C:\Program Files\UA\MenuManager.mdb

The '/excl' switch opens in exclusive and should be used when running frontend on your PC

Omitting the '/excl' switch opens in shared mode and should be used when running a shared frontend or DB on Network.

This Should Solve your problem,
Hap...


Access Developer

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

Access based Accounting Solutions - with free source code
Access Consultants forum

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

 

[KornGeek]

I strongly recommend against doing it that way. You're practically asking for corruption.

I believe that using a script to copy down the new front end when it has been changed to be the best approach, but because you have so few users, there's another idea I have that would be fairly easy.

You can create folders on the server for each user and put a copy of the front-end in each folder and a shortcut on the user's pc that points to their folder. When the front-end gets updated, simply drop it over the top of the front-ends in each of the folders. This would be annoying for 50 users, but trivial for 5.

Still, consider this idea. The user executes a program. This program checks a config file on the desktop and compares it to a config on the server. If the config on the server is newer, it copies the front-end and the config down to the local pc. Then, the program opens Access to run the front-end.

To the user, this is nearly transparent and guarantees them the latest front-end without the hassle and risk of accessing the same file. To the developer, simply modifying a config to signify a release is easy, plus it allows development without having it immediately released. (Anyone here who has ever programmed something with a bug? Quick show of hands. Anyone?)

 

[boblarson]

My suggestion I think is better than KornGeek's (although theirs will work). Once a frontend is auto enabled, all you have to do is change the version numbers and you don't have to drop a copy anywhere. It is all automatic (I have hundreds of users using this tool and have gotten good feedback on it).

Bob Larson
Access MVP
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[PHV]

Bottom line, never, ever, do design changes in a live production database !

 

[Hap007]

FYI,

I agree with all.

I always insist that my applications are copied to a local PC Folder and run in exclusive mode on the local PC.

This makes them run faster, with less network traffic.

I always use the 'FrontEnd' linked to a 'BackEnd' design.

I also deveolp on a seperate copy, never the live program.

Finally, I usually create an Access MDE file that the users run.

That being said,
My previous post was just answering the question (ha ha).

My recomendation, run a local copy of the FrontEnd on the user PC, complied to an Access MDE and opened Exclusively.

Hope This Helps,
Hap...


Access Developer

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

Access based Accounting Solutions - with free source code
Access Consultants forum

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

 

[straybullet]

Hi pcolvin,

One other thing to check (once you've implemented the above) is that all your users have full permissions (including edit) to the server folder where the BE resides.

Let them hate - so long as they fear... Lucius Accius

 

[Remou]

... and we are back to the beginning (20 Oct 08 10:12) :-D

 

[pcolvin]

Thank you all for the great suggestions. I ultimately went with Hap007's suggestion to create a shared shortcut. Although this is not the best method, this data base is only a temporary database interface with 3-5 users (at most 2 at any given time) that was created to operate for about a month. After tha a brand new data base located locally with be added to each users computer.