Exclusive Access

[speedman_2001]

Hey guys

Thanks in advance for any help and suggestions.

I have a MS Access DB set up and working fine except that I need to to open exclusively on the PC that opens the file. It is uploaded onto a server where a dozen users will have access, but only one can be in at a time to "approve" payments. Once they get out the next user can get in to pay the approved items.

I have looked at FE/BE setup, but doesn't look like it will help because of the way the lookup/delete macros/queries run. Any help of suggestions would be greatly appreciated.

 

[namliam]

In the menu:
Tools => Options> Advanced tab, choose Exclusive.

 

[speedman_2001]

Mailman

Thanks the for idea. I thought that would only affect the PC I was working on, not all PC's the database was opened on (unless I went to each PC and did this).

After trying that, and a couple other security configurations I gave in and re-wrote the SQL queries inside the DB so that exclusivity shouldn't matter anymore. Thanks for the help.

 

[namliam]

I think... 99% sure that this is something you set per DB, not per PC....

I can always be wrong offcourse

 

[speedman_2001]

After much more tinkering and moving and re-working the structure of the database, my problem has evolved yet again.

The DB is set up to pull a list of purchases that higher ups have to approve. When table A is opened by the president it works fine, but if the president is looking at A and someone else clicks on the run command to look at A, it is set up to clear out A and then repopulate. Doing this causes all of the information to disappear on the president's screen and only re-populate on the new users screen.

I'm looking at 2 options -

Only allow one user into the DB at a time or
Lock the tables anytime they are open (not sure on how to do this one either though I've heard it's possible.)

Exclusive Access would keep this from happening altogether because then only one user can be in the DB at a time, which is not a real big deal. I have also tried searching online on how to set up user-level security and have tried a couple of different routes, all to no prevail. Any suggestions would be greatly appreciated..

Access 2007 converted down to 2002-2003

 

[namliam]

Or have the front end NOT be on the network, presuming you have a front-end back-end setup.

But have the front end sit on the desktop or something...

Also the open exlusively will work offcourse....

Another option is to retrieve the Environ("Username")
This will give you the username logged into your DB. Then add this in the table and retrieve only that data that is intended for them.

 

[speedman_2001]

I have gone to a split setup. The front end is on the server and I have made copies of that down to 2 test desktops. I run the macro to get table A on the first machine and while that is open, I do the same on the second machine. Once the second machine is done and I look back at the first, all I see is #Deleted in each and every field.

The macro to get this table does contain a delete query to basically delete everything already in the table so that it can get fresh data everytime it's run.

 

[namliam]

so? It is fixed now? or?... if not, I dont understand your question?

 

[speedman_2001]

Sorry for the confusion here.

No it is not working correctly at this point.

When two users are in at the same time trying to look at the same table (derived from multiple append & delete queries) they end up deleting each others views. This may just be a problem with how I wrote the macros and the overall workings.

One other article I read suggested creating a table that "logs" user information as soon as someone connects (and clearing out when they exit) to the DB, then writing an open macro to look at that table and if a name exists (someone is already in the db) then display a message and close the app, though I'm not sure how to go about doing this as I'm not that advanced in Access.

The theory of the above sounds fairly good and would in the end accomplish something I could use but I'm not sure how to write the code to run when this db is opened.

 

[namliam]

You have tried the Environ option I mentioned??

You have to use it in 2 places, 1 in building the temp table, 2 in deleting again from the temp table.

While actually on the subject, why have a temp table at all? You are running a query to append to the temp table, so cannot you run your form(s) on the query? Skipping the need for the temp table all together.

 

[speedman_2001]

I have not tried the Environ option. I see where you are going with it, but the access does not need to be restricted to a table by user (all users have access to all tables) but I think it may be along the lines of something that may work.

Does the environ always exist or just when someone is in the database? If it's only when someone is in the database is it possible to insert vb code to run when the switchboard opens to look for that environ, and if it exists to close out the app? I'm totally in uncharted territory here. I have a very basic vb knowledge, just need to get pointed in the right direction on looking up the environ and such.

Thanks for the help mailman... much appreciated.

 

[namliam]

Environ("username") is the (windows) user CURRENTLY using the DB.
If you open the DB it returns your user name, if your boss opens it... his username.
If you are both in the DB or not that doesnt matter. For you it will always and only return your username, for your boss only and always his username.

You cannot use it to check for other users in the db.

 

[speedman_2001]

Is it possible that as soon as the form opens, it inserts the current username into a table I have created and then is cleared out when it closes?

What I'm thinking is this:

User A logs in and name is inserted into CurrentUsers table.
User B logs in and name is inserted as well but another script runs to see if any names are in the CurrentUsers table besides B, and if so, close out the application with a message that says something like "User A is currently logged in, please try again later."

I think what I'm going after here is possible, though I'm not sure on how to insert the code in the open/close modules of the form.

 

[apr pillai]

Open Exclusive

Try This:

Tools -- > Security - -> User and Group Permissions

Select Database in the Object Type Control

Select each User Name in the User/Group Name List

Put check marks in Open Run & Open Exclusive Check Boxes.

Visit the following Link for any other issues you may have:

http://www.msaccesstips.com/2006/11/microsoft-access-security.shtml

 

[speedman_2001]

apr

I looked into this and have tried creating a new workgroup. The only problem I saw with this is that the user's MS Access will always look at this workgroup, when I only want/need it for this database. Is it possible to only use this workgroup when opening this database or does it have to apply to everytime MS Access is opened?

 

[speedman_2001]

Thanks for all of the help and suggestions.

Ended up creating a shortcut with a /excl switch, which now warns when the file is in use.

Thanks again.