access security

[pdono]

Is there any way to log out users in a VBA program. I know how to obtain a list of available users through VBA, but I was wondering if users could be log out through code.

Is the only way to log out users in an Access database is to use the security wizard?

 

[thornmastr]

On any back end database on your server define a table which can be read by all users. This table should have one row for every Access application at your site. It should have a down time date, a down time start time, and a down time end time. All users are linked to this table with read only capability. Each user has a very small hidden form which is always active and whose entire purpose is to fire a timer event. When that event fires, the application checks its row on the table. If the downtime date is today, it checks the down start time and down end time. If the date and time matches, the application does an immediate application.quit

This table is also checked when an Access application first starts. If downtime is scheduled at that date and time, the application quits and will not start until the downtime date/time is no longer today’s date/time restraint.

You as the DBA of course have read.write access to that table.

Simple and effective.

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[pdono]

Should I use a "d lookup" function to use in the timer event on the form to look up the values in the table?

Thank-you for your help.

By any chance, do you have any samples you could send me.

The other solutions I heard of was logging users out through NT functions or having admin rights on
my company network server.

Thanks!!

 

[thornmastr]

I've got the code floating around somewhere. I'll get back to you probaby some time Monday afternoon. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[thornmastr]

The following provides a methodology to allow the developer to schedule and enforce periods of downtime for any number of databases.

Define a database, ie, TimeCtl. All users should have read access to the database, but must not be able to change/move/delete the database or anything within the database.
Define a table, tblLogOut on the TimeCtl database having the following fields per row.
--ApplicationName text(50) primary key
--LogoffStart date/time
--LogoffEnd datetime
--Inactive Boolean

you will have one row for every active database. The application name is the database name only without the mdb. It is not the full path name. For example, if you database is DrugCourt.mdb the applicationname on tblLogOut is DrugCourt.
LogoffStart is the date and time you want to insure that all users cannot access applicationname. LogoffEnd is the date and time the users can begin to use applicationname again.
If the inactive flag is true, the record will never be checked again. This allows you to keep a historical track of your scheduled down time.

In TimeCtl, create a module called gfunctions (global functions). Cut and paste the following function into gfunctions.



Public Function funShutDown(DatabaseName As String) As Boolean: funShutDown = False

Dim db As Database
Dim rs As Recordset
Dim strSQL As String

DatabaseName = UCase(DatabaseName)
Dim myDate As Date: myDate = Now()
strSQL = "SELECT * FROM TBLLOGOUT WHERE APPLICATIONNAME = '" _
& DatabaseName & "' AND INACTIVE = 0 AND #" & myDate & "# BETWEEN LOGOFFSTART AND LOGOFFEND"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount = 0 Then
GoTo OutShutDown
Else
funShutDown = True
End If

OutShutDown:
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

End Function


WATCH OUT FOR WORD WRAP.

On each database that is now represented on tblogout, establish a reference to the timecontrol database. This will allow you to call funShutDown from that database.
Link tbllogout to your database. Again, we don’t care if the user can see this table as long as he can’t do anything else but see information.

In your application database create an unbound form with one textbox. Place the following code on the open form event.


Private Sub Form_Open(Cancel As Integer)

If funShutdown(UCase("drug court&quot) = True Then
Application.Quit
End If

End Sub

This insures that a user who attempts to log on during the scheduled down time will fail to do so.

Set the forms timer interval to 300000 (5 minutes). The following is the forms timer event.

Private Sub Form_Timer()

If funShutdown("drug court&quot = True Then
Application.Quit
End If

End Sub

In the startup routine of your database, open this form hidden.

Every 5 minutes, your database will test if it should shut down. If down time is scheduled it will shut down within 5 minutes of the downtime start time.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Guest_imported]

Thanks!! This is a great solution to my issue!!

 

[thornmastr]

if you will tell me your email address and the version of Access you are using, i will zip you a sample. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[pdono]

I had this procedure working in a database that my boss deleted by accident! I recreated the procedure. However, I am getting a bad syntax error in my SQL portion of the code.
Below is the code I have in my module.

strSQL = "select * from logout where applicationname = " _
& DatabaseName & " and inactive = 0 and #" & mydate & "# between logoffstart And logoffend"

I have the Between loggoff part on the same line as the databasename in my actual function.
Set rs = db.OpenRecordset(strSQL, dbopendynaset)

Could you please help me find my "Hommer Simpson" error in the syntax? Thanks!!

 

[thornmastr]

strSQL = "SELECT * FROM TBLLOGOUT WHERE APPLICATIONNAME = '" _
& DatabaseName & "' AND INACTIVE = 0 AND #" & myDate & "# BETWEEN LOGOFFSTART AND LOGOFFEND"
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[AndyCole]

You've missed the single quotes around the DatabaseName variable. The line should read;

...applicationname = '" & DatabaseName & "' and ...

Cheers

Andy

 

[pdono]

Thanks!

I am having one last issue. When I try to open the form to run the code, I am getting a "type mismatch" error on
the open record set command. (Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

I did double check my table. I do have the log start and end fields as date data types and the inactive as [yes no] and database name as text.

Is there any other reason why the code would error out on the open recordset statement stating type mismatch?

Thanks!

 

[thornmastr]

This is DAO code. Youer default may be ADO, include DAO in your references and recompile. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[pdono]

Thanks!. The re-compile solved the last issue!!!