Restrict FE application to one user at a time

[teddysnips]

One of my clients has asked me to make a change to one of their Access
applications.

The application is a Front End/Back End standard app. I didn't
develop it, but looking at it tells me that it was done entirely using
the Wizards. There is no log-in procedure.

They want to amend it so that only one person can be logged in at any
one time. So, if Joe Bloggs tries to open the application, and Fred
Jones has already connected, Joe Bloggs will see a message saying
"System exlusively opened by Fred Jones. Please try later" or
something like that, and Access would then close.

My first thought is as follows.

Create a Users table matching users to their network name.

Create a LoggedIn table to store details of the logged-in user. This
table would only ever have one row. Once the user had finished their
work and closed the app., the row would be deleted.

If a user tried to connect, the application would simply check for the
existence of a row in this LoggedIn table.

My one concern is to ensure that the row is ALWAYS deleted however the
user exits the application - has anyone had any experience with this
and any thoughts about possible pitfalls? There's only enough money
for one day's work, so I'm not interested in gold-plating.

TIA

Edward

[Keith Wilby]

<te********@hotmail.comwrote in message
news:11*********************@h3g2000cwc.googlegrou ps.com...

>
They want to amend it so that only one person can be logged in at any
one time. So, if Joe Bloggs tries to open the application, and Fred
Jones has already connected, Joe Bloggs will see a message saying
"System exlusively opened by Fred Jones. Please try later" or
something like that, and Access would then close.

I think this is achievable by checking the default open mode to "Exclusive"
under Tools, Options, Advanced.

Keith.
www.keithwilby.com

[teddysnips]

On 12 Mar, 15:43, "Keith Wilby" <[e-mail]:

<[e-mail] in message

news:11*********************@h3g2000cwc.googlegrou ps.com...

They want to amend it so that only one person can be logged in at any
one time. So, if Joe Bloggs tries to open the application, and Fred
Jones has already connected, Joe Bloggs will see a message saying
"System exlusively opened by Fred Jones. Please try later" or
something like that, and Access would then close.

I think this is achievable by checking the default open mode to "Exclusive"
under Tools, Options, Advanced.

Hi Keith

I've finally got around to testing this.

I created a dummy back-end with a single table and, as you suggested,
set the default open mode to "Exclusive". I moved this database to a
location on our network.

I also created a dummy front-end linked to the dummy back-end, also
setting the default open mode to "Exclusive". I sent a copy to a
colleague and we were both able to open the front-ends
simulataneously, editing the data ad lib.

Any thoughts?

Edward

[Rick Brandt]

<te********@hotmail.comwrote in message
news:11**********************@c35g2000hsg.googlegr oups.com...

I've finally got around to testing this.

I created a dummy back-end with a single table and, as you suggested,
set the default open mode to "Exclusive". I moved this database to a
location on our network.

I also created a dummy front-end linked to the dummy back-end, also
setting the default open mode to "Exclusive". I sent a copy to a
colleague and we were both able to open the front-ends
simulataneously, editing the data ad lib.

Any thoughts?

That setting is for your installation of Access, not for the individual file you
currently are opening. I'm also pretty sure that it only affects front ends,
not back ends.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[paii, Ron]

<te********@hotmail.comwrote in message
news:11**********************@c35g2000hsg.googlegr oups.com...

On 12 Mar, 15:43, "Keith Wilby" <[e-mail]:

<[e-mail] in message

news:11*********************@h3g2000cwc.googlegrou ps.com...

They want to amend it so that only one person can be logged in at any
one time. So, if Joe Bloggs tries to open the application, and Fred
Jones has already connected, Joe Bloggs will see a message saying
"System exlusively opened by Fred Jones. Please try later" or
something like that, and Access would then close.

I think this is achievable by checking the default open mode to

"Exclusive"

under Tools, Options, Advanced.

Hi Keith

I've finally got around to testing this.

I created a dummy back-end with a single table and, as you suggested,
set the default open mode to "Exclusive". I moved this database to a
location on our network.

I also created a dummy front-end linked to the dummy back-end, also
setting the default open mode to "Exclusive". I sent a copy to a
colleague and we were both able to open the front-ends
simulataneously, editing the data ad lib.

Any thoughts?

Edward

The "Exclusive" setting is for the front end. You could try opening the back
end tables and place a lock on the tables using VBA code in the front end.

[Bri]

te********@hotmail.com wrote:

Hi Keith

I've finally got around to testing this.

I created a dummy back-end with a single table and, as you suggested,
set the default open mode to "Exclusive". I moved this database to a
location on our network.

I also created a dummy front-end linked to the dummy back-end, also
setting the default open mode to "Exclusive". I sent a copy to a
colleague and we were both able to open the front-ends
simulataneously, editing the data ad lib.

Any thoughts?

Edward

Your colleague is opening a copy not the same frontend that you have
open. The Exclusive pertains only to the opening of a specific MDB. Also
as mentioned, linking to a backend does not 'open' the backend in the
same sense that opening it directly. In other words, the Exclusive does
not get triggered by the link.

To do what you want, you need to have only one frontend that all users
open and have that set to Exclusive. Or use code to keep track of when
someone opens the FE and not allow anyone else in (dangerous because
what happens if the frontend is terminated abnormally before it can
'logout'?)

A better question would be 'Why can only one person be in the app at one
time?" I can't see why this would be wanted or necessary.

--
Bri

[teddysnips]

On 30 Apr, 20:09, Bri <[e-mail]:
[...]

To do what you want, you need to have only one frontend that all users
open and have that set to Exclusive. Or use code to keep track of when
someone opens the FE and not allow anyone else in (dangerous because
what happens if the frontend is terminated abnormally before it can
'logout'?)

I'm taking a different approach - I'm searching for the existence of
the locking file for the backend - if it exists, then the application
informs the user that there's another person attached to the database
and exits. Now I have the problem of how to do this, but I've got a
cunning plan....

A better question would be 'Why can only one person be in the app at one
time?" I can't see why this would be wanted or necessary.

Neither can I, but I'm not the client ;¬}

[Bri]

te********@hotmail.com wrote:

On 30 Apr, 20:09, Bri <[e-mail]:
[...]

>To do what you want, you need to have only one frontend that all users
open and have that set to Exclusive. Or use code to keep track of when
someone opens the FE and not allow anyone else in (dangerous because
what happens if the frontend is terminated abnormally before it can
'logout'?)

I'm taking a different approach - I'm searching for the existence of
the locking file for the backend - if it exists, then the application
informs the user that there's another person attached to the database
and exits. Now I have the problem of how to do this, but I've got a
cunning plan....

Checking for the existence of the LDB file is easy. Just use the DIR
function. A fan of Blackadder are we? :D

>A better question would be 'Why can only one person be in the app at one
time?" I can't see why this would be wanted or necessary.

Neither can I, but I'm not the client ;¬}

Ah, clients, always wanting what they don't need because they don't know
better. :) If you can determine why they think they need exclusive use,
perhaps you can suggest a different solution.

--
Bri

['69 Camaro]

Hi, Edward.

They want to amend it so that only one person can be logged in at any
one time.

From other replies in this thread, it sounds like your database is split, as
it should be for a multiuser database. To make it single user, delete the
links to the tables in the front end, and import the tables and
relationships into the front end. Store this file on the network in a
shared directory, but place all the database application users in a Windows
Group that only has read and modify permissions on this directory. No one
else besides Windows Administrators should have access rights to this
directory.

Now, when one user opens the database on the network, he won't have
permission to create an LDB file, which means the database file can't be
opened for shared mode. Any other user who tries to open it at the same
time will be given an error message that user 'Admin' has the file opened
exclusively.

Therefore, it's now only able to be opened exclusively by a single user at a
time.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
<te********@hotmail.comwrote in message
news:11*********************@h3g2000cwc.googlegrou ps.com...

One of my clients has asked me to make a change to one of their Access
applications.

The application is a Front End/Back End standard app. I didn't
develop it, but looking at it tells me that it was done entirely using
the Wizards. There is no log-in procedure.

They want to amend it so that only one person can be logged in at any
one time. So, if Joe Bloggs tries to open the application, and Fred
Jones has already connected, Joe Bloggs will see a message saying
"System exlusively opened by Fred Jones. Please try later" or
something like that, and Access would then close.

My first thought is as follows.

Create a Users table matching users to their network name.

Create a LoggedIn table to store details of the logged-in user. This
table would only ever have one row. Once the user had finished their
work and closed the app., the row would be deleted.

If a user tried to connect, the application would simply check for the
existence of a row in this LoggedIn table.

My one concern is to ensure that the row is ALWAYS deleted however the
user exits the application - has anyone had any experience with this
and any thoughts about possible pitfalls? There's only enough money
for one day's work, so I'm not interested in gold-plating.

TIA

Edward

[teddysnips]

On 1 May, 08:22, Bri <[e-mail]:

[e-mail] wrote:

[...]

I'm taking a different approach - I'm searching for the existence of
the locking file for the backend - if it exists, then the application
informs the user that there's another person attached to the database
and exits. Now I have the problem of how to do this, but I've got a
cunning plan....

Checking for the existence of the LDB file is easy. Just use the DIR
function. A fan of Blackadder are we? :D

I am a fan (how DID you guess, Mistress Ploppy?), and my plan involved
searching for the existence of the LDB file. It was all going so well
until.....

A better question would be 'Why can only one person be in the app at one
time?" I can't see why this would be wanted or necessary.

Neither can I, but I'm not the client ;¬}

Ah, clients, always wanting what they don't need because they don't know
better. :) If you can determine why they think they need exclusive use,
perhaps you can suggest a different solution.

When I called them and told them what I'd done, they changed the
position of the goalposts. They need to know *who* is logged on. So
it's back to the potentially error-prone table-with-single-row-
containing-username-of-logged-in-user-which-is-fine-until-Access-barfs-
at-which-time-the-table-won't-be-emptied-and-someone-will-have-to-go-
into-the-backend-and-clean-out-the-sludge.

Thanks one and all. Very good level of help and response round here.
My ISP could take a lesson from you people.

Edward

[Arno R]

<te********@hotmail.comschreef in bericht news:11**********************@q75g2000hsh.googlegr oups.com...

When I called them and told them what I'd done, they changed the
position of the goalposts. They need to know *who* is logged on. So
it's back to the potentially error-prone table-with-single-row-
containing-username-of-logged-in-user-which-is-fine-until-Access-barfs-
at-which-time-the-table-won't-be-emptied-and-someone-will-have-to-go-
into-the-backend-and-clean-out-the-sludge.

You (not someone) could also help them incase the table isn't emptied.
-Create a procedure that deletes the current user (or any user...) from the table.
-Create a macro that starts this procedure.(which needs to be a function btw)
-Create another shortcut that calls the macro (ShortcutToApp /x unlock)

HTH

Arno R

[paii, Ron]

<te********@hotmail.comwrote in message
news:11**********************@q75g2000hsh.googlegr oups.com...
On 1 May, 08:22, Bri <[e-mail]:

[e-mail] wrote:

[...]

I'm taking a different approach - I'm searching for the existence of
the locking file for the backend - if it exists, then the application
informs the user that there's another person attached to the database
and exits. Now I have the problem of how to do this, but I've got a
cunning plan....

Checking for the existence of the LDB file is easy. Just use the DIR
function. A fan of Blackadder are we? :D

I am a fan (how DID you guess, Mistress Ploppy?), and my plan involved
searching for the existence of the LDB file. It was all going so well
until.....

A better question would be 'Why can only one person be in the app at

one

time?" I can't see why this would be wanted or necessary.

Neither can I, but I'm not the client ;¬}

Ah, clients, always wanting what they don't need because they don't know
better. :) If you can determine why they think they need exclusive use,
perhaps you can suggest a different solution.

When I called them and told them what I'd done, they changed the
position of the goalposts. They need to know *who* is logged on. So
it's back to the potentially error-prone table-with-single-row-
containing-username-of-logged-in-user-which-is-fine-until-Access-barfs-
at-which-time-the-table-won't-be-emptied-and-someone-will-have-to-go-
into-the-backend-and-clean-out-the-sludge.

Thanks one and all. Very good level of help and response round here.
My ISP could take a lesson from you people.

Edward

I your client absolutely needs to know who is logged on, activate Access
security or move up to the free version of SQL server and use it's security.

[Bri]

te********@hotmail.com wrote:

When I called them and told them what I'd done, they changed the
position of the goalposts. They need to know *who* is logged on. So
it's back to the potentially error-prone table-with-single-row-
containing-username-of-logged-in-user-which-is-fine-until-Access-barfs-
at-which-time-the-table-won't-be-emptied-and-someone-will-have-to-go-
into-the-backend-and-clean-out-the-sludge.

Thanks one and all. Very good level of help and response round here.
My ISP could take a lesson from you people.

Edward

So, we are at the point where they will allow multiple people in as long
as they know who they are? Do they need to know when someone was in/out
or who is in right now. And/or do they want to track who creates/edits
records and when? The answers to these questions will determine the best
approach.

--
Bri

[teddysnips]

On 2 May, 00:24, Bri <[e-mail]:
[...]

So, we are at the point where they will allow multiple people in as long
as they know who they are? Do they need to know when someone was in/out
or who is in right now. And/or do they want to track who creates/edits
records and when? The answers to these questions will determine the best
approach.

No, it's simpler than that.

One person only allowed to be connected to the back-end at any one
time.

Any subsequent attempts to connect should inform the user of WHO is
currently connected. This will allow the user who *wishes* to connect
to approach the user who *is* connected and beat them around the head
with a fish until they cede their connection.

In order to achieve this I've simply done a call to apiGetUserName,
and written the value to a table called ConnectedUser. If there is a
value in this table, the user wishing to connect is informed of the
value and then booted out. If no value, their value is written to the
table and this locks the system for subsequent users.

So far, so good.

However, my next problem is to try to work out whether there's a
reliable way to exit this application doing some clean-up on the table
(DELETE * FROM ConnectedUser;) Can you trap the Application.Quit
event? Can you trap someone using the Window Close button (top right
X)

This is seriously doing my head in.

Thanks everyone.

Edward

[Bri]

te********@hotmail.com wrote:

No, it's simpler than that.

One person only allowed to be connected to the back-end at any one
time.

Any subsequent attempts to connect should inform the user of WHO is
currently connected. This will allow the user who *wishes* to connect
to approach the user who *is* connected and beat them around the head
with a fish until they cede their connection.

In order to achieve this I've simply done a call to apiGetUserName,
and written the value to a table called ConnectedUser. If there is a
value in this table, the user wishing to connect is informed of the
value and then booted out. If no value, their value is written to the
table and this locks the system for subsequent users.

So far, so good.

However, my next problem is to try to work out whether there's a
reliable way to exit this application doing some clean-up on the table
(DELETE * FROM ConnectedUser;) Can you trap the Application.Quit
event? Can you trap someone using the Window Close button (top right
X)

This is seriously doing my head in.

Thanks everyone.

Edward

I still think you need to figure out WHY they only want one person in at
a time. Access is perfectly capable of dealing with multi user apps.

That said, you can use the msldbusr.dll to check for the LDB file that
is created for the backend. This will tell you the Machinename of the
user(s) that have the backend connected. See these links for more info
(watch for wrapping):
http://support.microsoft.com/kb/176670
(That page also has a link to the Jet UserRoster in Access 2000 and higher)
http://msdn.microsoft.com/archive/de...techniques.asp
(The bottom of this page has the details on using the DLL)

I would start with a DIR test to see if there is a LDB file, then if
there is, try to delete it (in case the db was exited abnormally, if it
was then the LDB file could be left behind, but it will be deletable).
Now you know that it is being used. Then you can use the msldbusr.dll
functions to determine who is in it.

--
Bri

[paii, Ron]

"Bri" <no*@here.comwrote in message
news:BG4_h.153892$DE1.17936@pd7urf2no...

te********@hotmail.com wrote:

No, it's simpler than that.

One person only allowed to be connected to the back-end at any one
time.

Any subsequent attempts to connect should inform the user of WHO is
currently connected. This will allow the user who *wishes* to connect
to approach the user who *is* connected and beat them around the head
with a fish until they cede their connection.

In order to achieve this I've simply done a call to apiGetUserName,
and written the value to a table called ConnectedUser. If there is a
value in this table, the user wishing to connect is informed of the
value and then booted out. If no value, their value is written to the
table and this locks the system for subsequent users.

So far, so good.

However, my next problem is to try to work out whether there's a
reliable way to exit this application doing some clean-up on the table
(DELETE * FROM ConnectedUser;) Can you trap the Application.Quit
event? Can you trap someone using the Window Close button (top right
X)

This is seriously doing my head in.

Thanks everyone.

Edward

I still think you need to figure out WHY they only want one person in at
a time. Access is perfectly capable of dealing with multi user apps.

That said, you can use the msldbusr.dll to check for the LDB file that
is created for the backend. This will tell you the Machinename of the
user(s) that have the backend connected. See these links for more info
(watch for wrapping):
http://support.microsoft.com/kb/176670
(That page also has a link to the Jet UserRoster in Access 2000 and

higher)

>

http://msdn.microsoft.com/archive/de...techniques.asp

(The bottom of this page has the details on using the DLL)

I would start with a DIR test to see if there is a LDB file, then if
there is, try to delete it (in case the db was exited abnormally, if it
was then the LDB file could be left behind, but it will be deletable).
Now you know that it is being used. Then you can use the msldbusr.dll
functions to determine who is in it.

--
Bri

You would have to do the DIR test on the LDB in a batch or script because
the LDB is created by the 1st user of the MDB. It would already exist before
any Access startup code could be executed.

[Bri]

paii, Ron wrote:

You would have to do the DIR test on the LDB in a batch or script because
the LDB is created by the 1st user of the MDB. It would already exist before
any Access startup code could be executed.

Umm, no, not so. In this case the Frontend would be looking for the LDB
file for the Backend. As long as the test is done before anything makes
a connection to the Backend (like making a reference to a Database
object or opening a form with data or creating a recordset, etc) then
the LDB file will not have been created yet. In a non-split situation
you would be completely correct.

--
Bri

[paii, Ron]

"Bri" <no*@here.comwrote in message
news:YDg_h.156384$6m4.29187@pd7urf1no...

paii, Ron wrote:

You would have to do the DIR test on the LDB in a batch or script

because

the LDB is created by the 1st user of the MDB. It would already exist

before

any Access startup code could be executed.

Umm, no, not so. In this case the Frontend would be looking for the LDB
file for the Backend. As long as the test is done before anything makes
a connection to the Backend (like making a reference to a Database
object or opening a form with data or creating a recordset, etc) then
the LDB file will not have been created yet. In a non-split situation
you would be completely correct.

--
Bri

You are right sir! ;)
If as you said the test is done before any connection is made to the
back-end.