Limiting users

[Gerry Abbott]

Hi all,

Have written some code to limit the concurrent users of a database. Use the
Autoexec macro to open a form. When the form opens it increments a value in
a table. When the form closes (i.e. when the database closes, then the
number in the table is decremented. I plan to use this on a front/back end
setup, with a number of front end users on the network.

What i'm concerned about is if for some reason the network fails, and the
database crashes then the number will be one higher than it should, and this
could lead to a progressive problem. Thought of putting an error trap, but
don't this want to trigger and update if the error does not close the
database, as this would lead to the same problem .

Code is below.

Not looking for code, but advice on general solution or principles.
Thanks in advance.

CODE
----------------------------------------------------------------------------
--
Option Compare Database
Option Explicit
'This references a table called tblSysConstants, with fields Id, and Value.
'This constant must be set to the index number of the record holding the
users
'value in the tblSysConstants table
Const mNdx As Integer = 1

Const maxUsers As Integer = 3 'Set the max number of users
'
Function AddUser()
Dim iValue As Integer
Dim sValue As String

iValue = Val(sysConstGet(mNdx)) + 1
sValue = Str(iValue)
Call SysConstPut(mNdx, sValue)
If iValue > maxUsers Then
MsgBox "Sorry but you have reached the limit of users"
Call RemoveUser ' remove the added user before exiting.
DoCmd.Quit
End If
End Function

Function RemoveUser()

Dim iValue As Integer
Dim sValue As String

iValue = Val(sysConstGet(mNdx)) - 1
If iValue < 0 Then iValue = 0
sValue = Str(iValue)
Call SysConstPut(mNdx, sValue)

End Function
Public Function sysConstGet(ndx As Integer) As String
'returns the value in the Value field of tblSysConstants
'when the user arguament of the Id is supplied.
'table should have Id as key field

Dim db As DAO.Database
Dim rsSys As DAO.Recordset
Dim sInt As Integer
Dim sValue As Integer
Dim i As Integer
Set db = CurrentDb
Set rsSys = db.OpenRecordset("tblSysConstants", dbOpenDynaset, dbReadOnly)

With rsSys

.MoveFirst

For i = 1 To .RecordCount
If !id = ndx Then
sysConstGet = Trim(!Value)
Else
.MoveNext
End If
Next i

End With

Set rsSys = Nothing
Set db = Nothing

End Function

Public Function SysConstPut(ndx As Integer, mValue As String)
'This put a user input value into the value field of a record
'in the tblSysConst table. Ndx is the record Id number, and
'mValue is the value to be placed in the 'Value field of this
'record

Dim db As DAO.Database
Dim rsSys As DAO.Recordset
Dim sInt As Integer
Dim sValue As Integer
Dim i As Integer
Set db = CurrentDb
Set rsSys = db.OpenRecordset("tblSysConstants", dbOpenDynaset)

With rsSys

..MoveFirst

..FindFirst !id = ndx
If Not .NoMatch Then
..Edit
!Value = mValue
..Update
Else
MsgBox "No record match"
End If
End With
Set rsSys = Nothing
Set db = Nothing
End Function
--------------------------------------------
--

Gerry
Mitas
Ryemont Abbey,
Leixlip,
Co. Kildare
Phone: [phone]
Fax:[phone]
In**@mitas.ie
www.mitas.ie
_______________

DISCLAIMER:
This e-mail may contain proprietary information,
and is covered by copyright. It may be legally privileged, and is for the
intended
recipient only. If you are not the intended recipient you must not use,
disclose, distribute, copy, print or rely on this e-mail.

[Larry Linson]

Make yourself a "back door" that you can use, that doesn't check the table,
but does allow you to reset it. Hide it cleverly, and don't forget how you
hid it!

Larry Linson
Microsoft Access MVP

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:bN*******************@news.indigo.ie...

Hi all,

Have written some code to limit the concurrent users of a database. Use the Autoexec macro to open a form. When the form opens it increments a value in a table. When the form closes (i.e. when the database closes, then the
number in the table is decremented. I plan to use this on a front/back end
setup, with a number of front end users on the network.

What i'm concerned about is if for some reason the network fails, and the
database crashes then the number will be one higher than it should, and this could lead to a progressive problem. Thought of putting an error trap, but
don't this want to trigger and update if the error does not close the
database, as this would lead to the same problem .

Code is below.

Not looking for code, but advice on general solution or principles.
Thanks in advance.

CODE
-------------------------------------------------------------------------- -- --
Option Compare Database
Option Explicit
'This references a table called tblSysConstants, with fields Id, and Value. 'This constant must be set to the index number of the record holding the
users
'value in the tblSysConstants table
Const mNdx As Integer = 1

Const maxUsers As Integer = 3 'Set the max number of users
'
Function AddUser()
Dim iValue As Integer
Dim sValue As String

iValue = Val(sysConstGet(mNdx)) + 1
sValue = Str(iValue)
Call SysConstPut(mNdx, sValue)
If iValue > maxUsers Then
MsgBox "Sorry but you have reached the limit of users"
Call RemoveUser ' remove the added user before exiting.
DoCmd.Quit
End If
End Function

Function RemoveUser()

Dim iValue As Integer
Dim sValue As String

iValue = Val(sysConstGet(mNdx)) - 1
If iValue < 0 Then iValue = 0
sValue = Str(iValue)
Call SysConstPut(mNdx, sValue)

End Function
Public Function sysConstGet(ndx As Integer) As String
'returns the value in the Value field of tblSysConstants
'when the user arguament of the Id is supplied.
'table should have Id as key field

Dim db As DAO.Database
Dim rsSys As DAO.Recordset
Dim sInt As Integer
Dim sValue As Integer
Dim i As Integer
Set db = CurrentDb
Set rsSys = db.OpenRecordset("tblSysConstants", dbOpenDynaset, dbReadOnly)

With rsSys

.MoveFirst

For i = 1 To .RecordCount
If !id = ndx Then
sysConstGet = Trim(!Value)
Else
.MoveNext
End If
Next i

End With

Set rsSys = Nothing
Set db = Nothing

End Function

Public Function SysConstPut(ndx As Integer, mValue As String)
'This put a user input value into the value field of a record
'in the tblSysConst table. Ndx is the record Id number, and
'mValue is the value to be placed in the 'Value field of this
'record

Dim db As DAO.Database
Dim rsSys As DAO.Recordset
Dim sInt As Integer
Dim sValue As Integer
Dim i As Integer
Set db = CurrentDb
Set rsSys = db.OpenRecordset("tblSysConstants", dbOpenDynaset)

With rsSys

.MoveFirst

.FindFirst !id = ndx
If Not .NoMatch Then
.Edit
!Value = mValue
.Update
Else
MsgBox "No record match"
End If
End With
Set rsSys = Nothing
Set db = Nothing
End Function
--------------------------------------------
--

Gerry
Mitas
Ryemont Abbey,
Leixlip,
Co. Kildare
Phone: [phone]
Fax:[phone]
In**@mitas.ie
www.mitas.ie
_______________

DISCLAIMER:
This e-mail may contain proprietary information,
and is covered by copyright. It may be legally privileged, and is for the
intended
recipient only. If you are not the intended recipient you must not use,
disclose, distribute, copy, print or rely on this e-mail.

[Gerry Abbott]

Thanks Larry,
I use a back-door key to bypass shift, menus, and toolbars, etc, on some of
my applications. They work fine.

However don't think the problem associted with my approach is an 'access'
(literal) one. Am concerned that when I deploy to a client, then this
problem expresses itself, and there's no solution that I can give them,
short of allowing them to reset as you described, or me going in and
changing a value in the table manually.

I'm thinking is there a way to close all links to the back end file, then
reset the value. That way the client could have a reset button, which would
allow them to rectify the problem without my intervention.

However this may create more problems than it solves, un-hooking any open
front ends, The specific application I wish to implement this with is a
read only front end, so data corruption should not be an issue, if i
'kicked-out' any logged on users.

"Larry Linson" <bo*****@localhost.net> wrote in message
news:7p*******************@nwrddc03.gnilink.net...

Make yourself a "back door" that you can use, that doesn't check the table, but does allow you to reset it. Hide it cleverly, and don't forget how you
hid it!

Larry Linson
Microsoft Access MVP

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:bN*******************@news.indigo.ie...

Hi all,

Have written some code to limit the concurrent users of a database. Use

the

Autoexec macro to open a form. When the form opens it increments a value

in

a table. When the form closes (i.e. when the database closes, then the
number in the table is decremented. I plan to use this on a front/back end setup, with a number of front end users on the network.

What i'm concerned about is if for some reason the network fails, and the database crashes then the number will be one higher than it should, and

this

could lead to a progressive problem. Thought of putting an error trap, but don't this want to trigger and update if the error does not close the
database, as this would lead to the same problem .

Code is below.

Not looking for code, but advice on general solution or principles.
Thanks in advance.

CODE

--------------------------------------------------------------------------
--

--
Option Compare Database
Option Explicit
'This references a table called tblSysConstants, with fields Id, and

Value.

'This constant must be set to the index number of the record holding the
users
'value in the tblSysConstants table
Const mNdx As Integer = 1

Const maxUsers As Integer = 3 'Set the max number of users
'
Function AddUser()
Dim iValue As Integer
Dim sValue As String

iValue = Val(sysConstGet(mNdx)) + 1
sValue = Str(iValue)
Call SysConstPut(mNdx, sValue)
If iValue > maxUsers Then
MsgBox "Sorry but you have reached the limit of users"
Call RemoveUser ' remove the added user before exiting.
DoCmd.Quit
End If
End Function

Function RemoveUser()

Dim iValue As Integer
Dim sValue As String

iValue = Val(sysConstGet(mNdx)) - 1
If iValue < 0 Then iValue = 0
sValue = Str(iValue)
Call SysConstPut(mNdx, sValue)

End Function
Public Function sysConstGet(ndx As Integer) As String
'returns the value in the Value field of tblSysConstants
'when the user arguament of the Id is supplied.
'table should have Id as key field

Dim db As DAO.Database
Dim rsSys As DAO.Recordset
Dim sInt As Integer
Dim sValue As Integer
Dim i As Integer
Set db = CurrentDb
Set rsSys = db.OpenRecordset("tblSysConstants", dbOpenDynaset, dbReadOnly)
With rsSys

.MoveFirst

For i = 1 To .RecordCount
If !id = ndx Then
sysConstGet = Trim(!Value)
Else
.MoveNext
End If
Next i

End With

Set rsSys = Nothing
Set db = Nothing

End Function

Public Function SysConstPut(ndx As Integer, mValue As String)
'This put a user input value into the value field of a record
'in the tblSysConst table. Ndx is the record Id number, and
'mValue is the value to be placed in the 'Value field of this
'record

Dim db As DAO.Database
Dim rsSys As DAO.Recordset
Dim sInt As Integer
Dim sValue As Integer
Dim i As Integer
Set db = CurrentDb
Set rsSys = db.OpenRecordset("tblSysConstants", dbOpenDynaset)

With rsSys

.MoveFirst

.FindFirst !id = ndx
If Not .NoMatch Then
.Edit
!Value = mValue
.Update
Else
MsgBox "No record match"
End If
End With
Set rsSys = Nothing
Set db = Nothing
End Function
--------------------------------------------
--

Gerry
Mitas
Ryemont Abbey,
Leixlip,
Co. Kildare
Phone: [phone]
Fax:[phone]
In**@mitas.ie
www.mitas.ie
_______________

DISCLAIMER:
This e-mail may contain proprietary information,
and is covered by copyright. It may be legally privileged, and is for the intended
recipient only. If you are not the intended recipient you must not use,
disclose, distribute, copy, print or rely on this e-mail.

[Larry Linson]

I've implemented a kick-out on an Access client to a server database for a
different purpose -- to allow the Adminstrator to force users off for
necessary maintenance.

I put a timer on a startup form, and in the Timer event, checked the table
in the back end -- if the DBA had put a maintenance warning, it warned the
user at specified intervals in advance, and then at the specified time, did
a DoCmd.Quit. If an attempt was made to restart the client during in a
maintenance period, the client front-end wouldn't start up... that was the
first check. You could do something similar from your backdoor front-end...
set the flag, kick the users off, fix the table, then clear the flag.

Larry Linson
Microsoft Access MVP
"Gerry Abbott" <pl****@ask.ie> wrote in message
news:ds*******************@news.indigo.ie...

Thanks Larry,
I use a back-door key to bypass shift, menus, and toolbars, etc, on some of my applications. They work fine.

However don't think the problem associted with my approach is an 'access'
(literal) one. Am concerned that when I deploy to a client, then this
problem expresses itself, and there's no solution that I can give them,
short of allowing them to reset as you described, or me going in and
changing a value in the table manually.

I'm thinking is there a way to close all links to the back end file, then
reset the value. That way the client could have a reset button, which would allow them to rectify the problem without my intervention.

However this may create more problems than it solves, un-hooking any open
front ends, The specific application I wish to implement this with is a
read only front end, so data corruption should not be an issue, if i
'kicked-out' any logged on users.

"Larry Linson" <bo*****@localhost.net> wrote in message
news:7p*******************@nwrddc03.gnilink.net...

Make yourself a "back door" that you can use, that doesn't check the

table,

but does allow you to reset it. Hide it cleverly, and don't forget how you

hid it!

Larry Linson
Microsoft Access MVP

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:bN*******************@news.indigo.ie...

Hi all,

Have written some code to limit the concurrent users of a database. Use

the

Autoexec macro to open a form. When the form opens it increments a

value in

a table. When the form closes (i.e. when the database closes, then the
number in the table is decremented. I plan to use this on a front/back end setup, with a number of front end users on the network.

What i'm concerned about is if for some reason the network fails, and the database crashes then the number will be one higher than it should,

and this

could lead to a progressive problem. Thought of putting an error trap,

but don't this want to trigger and update if the error does not close the
database, as this would lead to the same problem .

Code is below.

Not looking for code, but advice on general solution or principles.
Thanks in advance.

CODE

--------------------------------------------------------------------------

--

--
Option Compare Database
Option Explicit
'This references a table called tblSysConstants, with fields Id, and

Value.

'This constant must be set to the index number of the record holding

the users
'value in the tblSysConstants table
Const mNdx As Integer = 1

Const maxUsers As Integer = 3 'Set the max number of users
'
Function AddUser()
Dim iValue As Integer
Dim sValue As String

iValue = Val(sysConstGet(mNdx)) + 1
sValue = Str(iValue)
Call SysConstPut(mNdx, sValue)
If iValue > maxUsers Then
MsgBox "Sorry but you have reached the limit of users"
Call RemoveUser ' remove the added user before exiting.
DoCmd.Quit
End If
End Function

Function RemoveUser()

Dim iValue As Integer
Dim sValue As String

iValue = Val(sysConstGet(mNdx)) - 1
If iValue < 0 Then iValue = 0
sValue = Str(iValue)
Call SysConstPut(mNdx, sValue)

End Function
Public Function sysConstGet(ndx As Integer) As String
'returns the value in the Value field of tblSysConstants
'when the user arguament of the Id is supplied.
'table should have Id as key field

Dim db As DAO.Database
Dim rsSys As DAO.Recordset
Dim sInt As Integer
Dim sValue As Integer
Dim i As Integer
Set db = CurrentDb
Set rsSys = db.OpenRecordset("tblSysConstants", dbOpenDynaset,

dbReadOnly)
With rsSys

.MoveFirst

For i = 1 To .RecordCount
If !id = ndx Then
sysConstGet = Trim(!Value)
Else
.MoveNext
End If
Next i

End With

Set rsSys = Nothing
Set db = Nothing

End Function

Public Function SysConstPut(ndx As Integer, mValue As String)
'This put a user input value into the value field of a record
'in the tblSysConst table. Ndx is the record Id number, and
'mValue is the value to be placed in the 'Value field of this
'record

Dim db As DAO.Database
Dim rsSys As DAO.Recordset
Dim sInt As Integer
Dim sValue As Integer
Dim i As Integer
Set db = CurrentDb
Set rsSys = db.OpenRecordset("tblSysConstants", dbOpenDynaset)

With rsSys

.MoveFirst

.FindFirst !id = ndx
If Not .NoMatch Then
.Edit
!Value = mValue
.Update
Else
MsgBox "No record match"
End If
End With
Set rsSys = Nothing
Set db = Nothing
End Function
--------------------------------------------
--

Gerry
Mitas
Ryemont Abbey,
Leixlip,
Co. Kildare
Phone: [phone]
Fax:[phone]
In**@mitas.ie
www.mitas.ie
_______________

DISCLAIMER:
This e-mail may contain proprietary information,
and is covered by copyright. It may be legally privileged, and is for the intended
recipient only. If you are not the intended recipient you must not use, disclose, distribute, copy, print or rely on this e-mail.

[Gerry Abbott]

Thanks again Larry,

Think this is moving in the right direction. Will think more about it in the
next couple of days. Will reply to the thread with conclusions or further
queries.

Regards,

Gerry Abbott

"Larry Linson" <bo*****@localhost.net> wrote in message
news:48*******************@nwrddc01.gnilink.net...

I've implemented a kick-out on an Access client to a server database for a
different purpose -- to allow the Adminstrator to force users off for
necessary maintenance.

I put a timer on a startup form, and in the Timer event, checked the table
in the back end -- if the DBA had put a maintenance warning, it warned the
user at specified intervals in advance, and then at the specified time, did a DoCmd.Quit. If an attempt was made to restart the client during in a
maintenance period, the client front-end wouldn't start up... that was the
first check. You could do something similar from your backdoor front-end... set the flag, kick the users off, fix the table, then clear the flag.

Larry Linson
Microsoft Access MVP
"Gerry Abbott" <pl****@ask.ie> wrote in message
news:ds*******************@news.indigo.ie...

Thanks Larry,
I use a back-door key to bypass shift, menus, and toolbars, etc, on some

of

my applications. They work fine.

However don't think the problem associted with my approach is an 'access'

(literal) one. Am concerned that when I deploy to a client, then this
problem expresses itself, and there's no solution that I can give them,
short of allowing them to reset as you described, or me going in and
changing a value in the table manually.

I'm thinking is there a way to close all links to the back end file, then reset the value. That way the client could have a reset button, which

would

allow them to rectify the problem without my intervention.

However this may create more problems than it solves, un-hooking any open front ends, The specific application I wish to implement this with is a
read only front end, so data corruption should not be an issue, if i
'kicked-out' any logged on users.

"Larry Linson" <bo*****@localhost.net> wrote in message
news:7p*******************@nwrddc03.gnilink.net...

Make yourself a "back door" that you can use, that doesn't check the

table,

but does allow you to reset it. Hide it cleverly, and don't forget how you hid it!

Larry Linson
Microsoft Access MVP

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:bN*******************@news.indigo.ie...
> Hi all,
>
> Have written some code to limit the concurrent users of a database. Use the
> Autoexec macro to open a form. When the form opens it increments a value in
> a table. When the form closes (i.e. when the database closes, then the > number in the table is decremented. I plan to use this on a front/back end

> setup, with a number of front end users on the network.
>
> What i'm concerned about is if for some reason the network fails,

and

the

> database crashes then the number will be one higher than it should, and this
> could lead to a progressive problem. Thought of putting an error

trap, but

> don't this want to trigger and update if the error does not close

the > database, as this would lead to the same problem .
>
> Code is below.
>
> Not looking for code, but advice on general solution or principles.
>
>
> Thanks in advance.
>
>
>
> CODE

-------------------------------------------------------------------------- --
> --
> Option Compare Database
> Option Explicit
> 'This references a table called tblSysConstants, with fields Id, and
Value.
> 'This constant must be set to the index number of the record holding the > users
> 'value in the tblSysConstants table
> Const mNdx As Integer = 1
>
> Const maxUsers As Integer = 3 'Set the max number of users
>
>
> '
> Function AddUser()
> Dim iValue As Integer
> Dim sValue As String
>
> iValue = Val(sysConstGet(mNdx)) + 1
> sValue = Str(iValue)
> Call SysConstPut(mNdx, sValue)
> If iValue > maxUsers Then
> MsgBox "Sorry but you have reached the limit of users"
> Call RemoveUser ' remove the added user before exiting.
> DoCmd.Quit
> End If
> End Function
>
> Function RemoveUser()
>
> Dim iValue As Integer
> Dim sValue As String
>
> iValue = Val(sysConstGet(mNdx)) - 1
> If iValue < 0 Then iValue = 0
> sValue = Str(iValue)
> Call SysConstPut(mNdx, sValue)
>
> End Function
>
>
> Public Function sysConstGet(ndx As Integer) As String
> 'returns the value in the Value field of tblSysConstants
> 'when the user arguament of the Id is supplied.
> 'table should have Id as key field
>
> Dim db As DAO.Database
> Dim rsSys As DAO.Recordset
> Dim sInt As Integer
> Dim sValue As Integer
> Dim i As Integer
> Set db = CurrentDb
> Set rsSys = db.OpenRecordset("tblSysConstants", dbOpenDynaset,

dbReadOnly)

>
> With rsSys
>
> .MoveFirst
>
> For i = 1 To .RecordCount
> If !id = ndx Then
> sysConstGet = Trim(!Value)
> Else
> .MoveNext
> End If
> Next i
>
> End With
>
> Set rsSys = Nothing
> Set db = Nothing
>
> End Function
>
> Public Function SysConstPut(ndx As Integer, mValue As String)
> 'This put a user input value into the value field of a record
> 'in the tblSysConst table. Ndx is the record Id number, and
> 'mValue is the value to be placed in the 'Value field of this
> 'record
>
> Dim db As DAO.Database
> Dim rsSys As DAO.Recordset
> Dim sInt As Integer
> Dim sValue As Integer
> Dim i As Integer
> Set db = CurrentDb
> Set rsSys = db.OpenRecordset("tblSysConstants", dbOpenDynaset)
>
> With rsSys
>
> .MoveFirst
>
> .FindFirst !id = ndx
> If Not .NoMatch Then
> .Edit
> !Value = mValue
> .Update
> Else
> MsgBox "No record match"
> End If
> End With
> Set rsSys = Nothing
> Set db = Nothing
>
>
> End Function
> --------------------------------------------
>
>
> --
>
> Gerry
>
>
> Mitas
> Ryemont Abbey,
> Leixlip,
> Co. Kildare
> Phone: [phone]
> Fax:[phone]
> In**@mitas.ie
> www.mitas.ie
> _______________
>
> DISCLAIMER:
> This e-mail may contain proprietary information,
> and is covered by copyright. It may be legally privileged, and is

for the

> intended
> recipient only. If you are not the intended recipient you must not

use, > disclose, distribute, copy, print or rely on this e-mail.
>
>

[Arno R]

Gerry,
What I've done in some of my apps is the following. Maybe you can use it for your situation.

Maybe my explanation is a bit clumsy, but this still isn't my native language ;-)

Every user needs a different username.
Start application: Username (CurrentUser) is put in a table (just called 'tabUsers') in the backend.
The number of records in this table is checked and when exceeding licence then the application
stops.

Our problem:
When the database crashes then username is still in the database and has to be released,
because when the same user logs in the app stops with a message explaining that user x is already in
the database..
To release a username this user has to log in again using a macro. (commandline .... /x macroname)
This macro deletes the logged-in-user from the table TabUsers
After that the user can log in again the usual way.

Also the app always checks on opening some crucial forms if the CurrentUser() still is present in
tabUsers.
If not (someone else used the release-macro with this username?) the application quits.
Of course this 'security' can be broken, but until now it works satisfactory to me.
Main problem is how and where to hide the number of allowed concurrent users...

--
Hope this helps
Arno R
"Gerry Abbott" <pl****@ask.ie> schreef in bericht news:bN*******************@news.indigo.ie...

Hi all,

Have written some code to limit the concurrent users of a database. Use the
Autoexec macro to open a form. When the form opens it increments a value in
a table. When the form closes (i.e. when the database closes, then the
number in the table is decremented. I plan to use this on a front/back end
setup, with a number of front end users on the network.

What i'm concerned about is if for some reason the network fails, and the
database crashes then the number will be one higher than it should, and this
could lead to a progressive problem. Thought of putting an error trap, but
don't this want to trigger and update if the error does not close the
database, as this would lead to the same problem .

Code is below.

Not looking for code, but advice on general solution or principles.
Thanks in advance.

CODE
----------------------------------------------------------------------------
--
Option Compare Database
Option Explicit
'This references a table called tblSysConstants, with fields Id, and Value.
'This constant must be set to the index number of the record holding the
users
'value in the tblSysConstants table
Const mNdx As Integer = 1

Const maxUsers As Integer = 3 'Set the max number of users
'
Function AddUser()
Dim iValue As Integer
Dim sValue As String

iValue = Val(sysConstGet(mNdx)) + 1
sValue = Str(iValue)
Call SysConstPut(mNdx, sValue)
If iValue > maxUsers Then
MsgBox "Sorry but you have reached the limit of users"
Call RemoveUser ' remove the added user before exiting.
DoCmd.Quit
End If
End Function

Function RemoveUser()

Dim iValue As Integer
Dim sValue As String

iValue = Val(sysConstGet(mNdx)) - 1
If iValue < 0 Then iValue = 0
sValue = Str(iValue)
Call SysConstPut(mNdx, sValue)

End Function
Public Function sysConstGet(ndx As Integer) As String
'returns the value in the Value field of tblSysConstants
'when the user arguament of the Id is supplied.
'table should have Id as key field

Dim db As DAO.Database
Dim rsSys As DAO.Recordset
Dim sInt As Integer
Dim sValue As Integer
Dim i As Integer
Set db = CurrentDb
Set rsSys = db.OpenRecordset("tblSysConstants", dbOpenDynaset, dbReadOnly)

With rsSys

.MoveFirst

For i = 1 To .RecordCount
If !id = ndx Then
sysConstGet = Trim(!Value)
Else
.MoveNext
End If
Next i

End With

Set rsSys = Nothing
Set db = Nothing

End Function

Public Function SysConstPut(ndx As Integer, mValue As String)
'This put a user input value into the value field of a record
'in the tblSysConst table. Ndx is the record Id number, and
'mValue is the value to be placed in the 'Value field of this
'record

Dim db As DAO.Database
Dim rsSys As DAO.Recordset
Dim sInt As Integer
Dim sValue As Integer
Dim i As Integer
Set db = CurrentDb
Set rsSys = db.OpenRecordset("tblSysConstants", dbOpenDynaset)

With rsSys

.MoveFirst

.FindFirst !id = ndx
If Not .NoMatch Then
.Edit
!Value = mValue
.Update
Else
MsgBox "No record match"
End If
End With
Set rsSys = Nothing
Set db = Nothing
End Function
--------------------------------------------
--

Gerry
Mitas
Ryemont Abbey,
Leixlip,
Co. Kildare
Phone: [phone]
Fax:[phone]
In**@mitas.ie
www.mitas.ie
_______________

DISCLAIMER:
This e-mail may contain proprietary information,
and is covered by copyright. It may be legally privileged, and is for the
intended
recipient only. If you are not the intended recipient you must not use,
disclose, distribute, copy, print or rely on this e-mail.

[paii]

Write a application to attempt to delete the .LDB file for your database. If
it succeeds, then nobody has your application open you can then connect to
you user table and clear the user count.

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:bN*******************@news.indigo.ie...

Hi all,

Have written some code to limit the concurrent users of a database. Use the Autoexec macro to open a form. When the form opens it increments a value in a table. When the form closes (i.e. when the database closes, then the
number in the table is decremented. I plan to use this on a front/back end
setup, with a number of front end users on the network.

What i'm concerned about is if for some reason the network fails, and the
database crashes then the number will be one higher than it should, and this could lead to a progressive problem. Thought of putting an error trap, but
don't this want to trigger and update if the error does not close the
database, as this would lead to the same problem .

Code is below.

Not looking for code, but advice on general solution or principles.
Thanks in advance.

CODE
-------------------------------------------------------------------------- -- --
Option Compare Database
Option Explicit
'This references a table called tblSysConstants, with fields Id, and Value. 'This constant must be set to the index number of the record holding the
users
'value in the tblSysConstants table
Const mNdx As Integer = 1

Const maxUsers As Integer = 3 'Set the max number of users
'
Function AddUser()
Dim iValue As Integer
Dim sValue As String

iValue = Val(sysConstGet(mNdx)) + 1
sValue = Str(iValue)
Call SysConstPut(mNdx, sValue)
If iValue > maxUsers Then
MsgBox "Sorry but you have reached the limit of users"
Call RemoveUser ' remove the added user before exiting.
DoCmd.Quit
End If
End Function

Function RemoveUser()

Dim iValue As Integer
Dim sValue As String

iValue = Val(sysConstGet(mNdx)) - 1
If iValue < 0 Then iValue = 0
sValue = Str(iValue)
Call SysConstPut(mNdx, sValue)

End Function
Public Function sysConstGet(ndx As Integer) As String
'returns the value in the Value field of tblSysConstants
'when the user arguament of the Id is supplied.
'table should have Id as key field

Dim db As DAO.Database
Dim rsSys As DAO.Recordset
Dim sInt As Integer
Dim sValue As Integer
Dim i As Integer
Set db = CurrentDb
Set rsSys = db.OpenRecordset("tblSysConstants", dbOpenDynaset, dbReadOnly)

With rsSys

.MoveFirst

For i = 1 To .RecordCount
If !id = ndx Then
sysConstGet = Trim(!Value)
Else
.MoveNext
End If
Next i

End With

Set rsSys = Nothing
Set db = Nothing

End Function

Public Function SysConstPut(ndx As Integer, mValue As String)
'This put a user input value into the value field of a record
'in the tblSysConst table. Ndx is the record Id number, and
'mValue is the value to be placed in the 'Value field of this
'record

Dim db As DAO.Database
Dim rsSys As DAO.Recordset
Dim sInt As Integer
Dim sValue As Integer
Dim i As Integer
Set db = CurrentDb
Set rsSys = db.OpenRecordset("tblSysConstants", dbOpenDynaset)

With rsSys

.MoveFirst

.FindFirst !id = ndx
If Not .NoMatch Then
.Edit
!Value = mValue
.Update
Else
MsgBox "No record match"
End If
End With
Set rsSys = Nothing
Set db = Nothing
End Function
--------------------------------------------
--

Gerry
Mitas
Ryemont Abbey,
Leixlip,
Co. Kildare
Phone: [phone]
Fax:[phone]
In**@mitas.ie
www.mitas.ie
_______________

DISCLAIMER:
This e-mail may contain proprietary information,
and is covered by copyright. It may be legally privileged, and is for the
intended
recipient only. If you are not the intended recipient you must not use,
disclose, distribute, copy, print or rely on this e-mail.