Exclusive access to Access???

[kuipers78]

Hello,

I've created my own login procedure for an Access application (Access 2003) without using a .mdw file. Users can fill in their username and password and the program will check in a table if the user exists. If so, dependent on the assigned usergroup (also stored in a table) the user will see a full or stripped menu and forms/tables/queries will be shown or hidden. This whole procedure is being settled by VBA. I store the user information temporary in a table and it can be obtained by the code all over the program.

This works fine, but sometimes I need to change for example a form design or VBA-code. There are always users in the program and that automatically means Access doesn't allow me to make any changes. For that, I need to have exclusive access to the program...

My question: can I enter the program exclusively while there are users inside, by throwing them out of the program? How?
If not, is it possible for me to throw users out of the program while I am inside? How?
If not, are there any other ways to solve this problem? (without using a .mdw file) For intelligibility: it isn't a problem if all other users are being kicked out of the program instantly.

Suggestions are very welcome! Thanx!

 

[nikkypickles]

I too would like to know if there's a solution to this.

At the moment, the only way I can get exclusive access to Access is by sending a network message out requesting everyone exits and then watching the server to see the .ldb file disappear.

 

[ghudson]

You can not exclusively open a db that is already opened by one or more users. You would have to kick them out. There are code samples posted somewhere in this forum on how to kick a user out of a shared database. Is your database split? Each user should have a copy of the front end installed on their PC and each front end must be linked to the one back end.

I use a custom shortcut to open my db's with the exclusive command line switch. That prevents anybody else from opening the db while I have it opened exclusively.

 

[kuipers78]

You are right, I should have read the forum more carefully. I managed to alter the VBA-code so that I can block users from entering the database and kick them out (even with an announcement screen). This can be done by simply altering one field value in the Backend.

Sometimes solutions are less complicated than expected. Cool!

 

[gemma-the-husky]

more importantly if you have a multi user dbs, you should split the database into a front end and back end, so each user has their own copy of the programmes, and share the common data files. that way, you can improve your database without problem, and issue it to your users when you have made your improvements. You will be able to maintain tables in the data back end that are not currently in use (eg add fields. modify keys etc.)

look on this site for reasons why users should not share the frontend (or the whole database)

Sorry, reading your thread again, you obviously have split the tables, but it sounds like you are all sharing the front end, which is not ideal.

 

[kuipers78]

Well, indeed my application uses one front end which is being shared by all users. I (almost) never change anything in the back end, because it only contains 'hard' data stored in tables. So, when I have to make a change I now open (exclusively) the front end and alter it. That's it, done.

Should all users have their own copy of the front end I should have to change them all. I could also make one new front end available for all users, hoping that they will copy it and start to use it. To make sure they will, I would have to check it. Quite an unattractive idea with a lot of users...

My opinion: the benefits of seperated front ends have to be really noteworthy to beat these arguments.

more importantly if you have a multi user dbs, you should split the database into a front end and back end, so each user has their own copy of the programmes, and share the common data files. that way, you can improve your database without problem, and issue it to your users when you have made your improvements. You will be able to maintain tables in the data back end that are not currently in use (eg add fields. modify keys etc.)

look on this site for reasons why users should not share the frontend (or the whole database)

Sorry, reading your thread again, you obviously have split the tables, but it sounds like you are all sharing the front end, which is not ideal.

 

[gemma-the-husky]

You ought to check this forums for discussions on why a shared front end is not a good idea.

 

[KernelK]

Should all users have their own copy of the front end I should have to change them all. I could also make one new front end available for all users, hoping that they will copy it and start to use it. To make sure they will, I would have to check it. Quite an unattractive idea with a lot of users...

Not neccessarily the case. Simply put a table with version numbers in both the front end and the back end. Have loading VBA code in the front end check the version number against the backend and lock them out until they copy the new version over. This completely relieves you of the duty of notifying user's of the change, and hoping that they use the new version. I usually also put code to check the path of the front end to make sure that no users are simply running the front end from the network share.
There are far more benefits to having a distributed front end than drawbacks, as most drawbacks can be addressed easily, while the benefits can never be achieved by simply sharing the front end.

 

[jhomeyer]

May I have a copy

You are doing exactly what I want to be able to do. I have a table with userids and their type of access to the database (administrator, date entry, reports only). When they first logon they will be presented with a logon form that must be filled out correctly or they will be exited from the application. The only thing on the form is a text box for the userid and the continue button where the VB code is. What I can't figure out is how to read just the one record from the table and then store the results into public variable that I can examine throughout the rest of the database. Can you share your code with me, please.