logging when user opens database 1

[newguy86]

Here is the deal, I want to create a logging function that records whenever someone opens a database so that I can see which users currently have what databases open.

Now I have my table, SQL statment, and queries already setup and ready. The only piece that I am missing is the event(s) to activate this process. The only Open/Close events that I have found are for opening and closing an actual form in the database, but what I want to do is to have the logging process activated at the startup of the database.

If anyone can provide assistance on this topic, it would be greatly appreciated.

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement?!

 

[PHV]

Use a startup form.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GingerR]

Why not have a form? How do your users navigate in your db? You could at least use a hidden form, so that a record is added to the Log table when the form is opened (but hidden), then OnClose of the form, update the log record to put the date/time that the user quits the db (and the form is closed of course).

In my db's I use an Autoexec macro which calls a function called Launch() which verifies they are supposed to be in the db, what 'type' of user they are, if they are a valid user it adds a record to the log file, does various other functions, then opens a switchboard (form) relevant to the type of user they are. There's a QUIT button on the switchboard form which updates the log record with the date/time the user is leaving the db.

You may want to do the same, but if you use an Autoexec macro and a function, I'm not sure when you'd update the log record with the UserEnd date/time stamp of when they close/quit the db.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[newguy86]

I already have a logger function that is based off of a form. The problem is if a user opens the database, which automatically opens a form/switchboard, and then the user only closes the form, my log will appear as if no users currently have the database open.

I have tried to use hidden forms that open from the startup but everytime I open the database my "hidden" form opens and is visible to everyone. Although, now that I think about it, I may try to setup a hidden form that opens when the startup form/switchboard is opened and then see if the hidden form stays open when I close the startup form/switchboard. But that still leaves me with the issue that some of my db's are setup to only display the Database Window on startup because I am unable to put all of the db objects that I use into a switchboard.

I may use the Autoexec macro and I will look into my options when closing the database.

If anyone has other suggestions please let me know.

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement?!

 

[GingerR]

Sounds like you are giving your users full reign of your database, which I never do, and wonder why you are? I want my users to only go where I want them to go, and no place else.

If you want to keep it like it is, you could make the OnClose of the startup form actually QUIT the database, then the user won't close the form. And if they do, their record will be logged out and the db will close.

Also, if you make a form "hidden" it should not be visible to anyone. I wonder if you are opening it in 'Hidden' mode, or are you just thinking that if you put it behind another form, that this constitutes "hidden"?

Anyhow, some things to think about.......

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[NXMold]

I open a splash screen form to verify user status etc, then when that form completes it opens my "MainMenu". When the main menu closes, the user status is updated (via a custom function UserTable) and the access application quits. That way users cannot accidentally close just the visible forms and leave the DB locked.


Private Sub Form_Unload(Cancel As Integer)
UserTable ("Logged Out")
If IsMDE = True Then DoCmd.Quit
End Sub

 

[GingerR]

So are you saying you are all set, or do you still have a problem?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[newguy86]

The databases that don't use startup forms/switchboards are the back-end databases that are only accessable by Admin Users & Management . These users are constantly creating/editing queries to display the data they have available for reporting or comparison purposes, so they are fully allowed to make changes as necessary in order to complete their task. On a side note, if you know of any way that I can easily create/edit queries and their design without using the database window then I am all ears.

Regardless of this fact, the problem that I am having is that if I need to push a db update thru or if I need to jump in for a few minutes to fix something and someone else has a database open, then I am unable to complete the work until they exit out of that database. So I wanted something that I can easily pull up, and that will tell me exactly who is in the database so that I can go to them directly instead of playing a guessing game with everyone.

Now I like your idea of using the OnClose sub to actually quit the database whenever someone closes the startup form/switchboard and I think that I might try to use it.

Also, there is a good chance that I didn't properly setup the hidden form to not be visible at all. I know that I set the form properties to hidden and in the design view of the form I chose 'No' for the Allow Form View setting. But this was also after I set the hidden form to open on startup instead of opening when another form opens. So I will take another crack at this and we will see.

Thank you for your input and if you or anyone else has anything that they would like to add, please feel free to do so.

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement?!

 

[GingerR]

If you want to know who is in the db, you can use the ldbviewer supplied by microsoft. If all you want to do is know who's in the db, here you go. It's free and easy and dependable. I use a log file to show how much people are using my tools when i report to mgmt. But if all you want is to know who's in there right now so you can ask them to get out, here it is:

http://support.microsoft.com/kb/176670

it's the ldbview.exe. it'll show you machine names. In my company, i can then look up who owns the machines on an internal website.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[newguy86]

GingerR,
I followed the link and the page states that the download applies to Access 97. Do you know if it will work for Access 2003?

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement?!

 

[GingerR]

I use it all the time. I have Access 2003.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[newguy86]

GingerR,
I downloaded the file and tried it out and it worked great. The only problems with it are 1.) because I.T. uses a proprietary naming convention to name each computer. I have to compile a list of all the computer names and who owns that computer, and 2.) I am only able to see one database at a time so I have to run the program seperately for each database.

This is a good program for the time being and this will help me while I work out a more customized solution.

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement?!

 

[Irish1957]

This is how I log users in an out of an Access based system. I have not written an add previously unseen user module but it should be possible but not necessary in my application. It takes the persons network login and uses it.

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function UserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        UserName = left$(strUserName, lngLen - 1)
    Else
        UserName = vbNullString
    End If
End Function

Private Sub Form_Open()
Dim mev1 as string
Dim rst As New ADODB.Recordset
mev1 = UserName()
rst.Open "SELECT * FROM logtable WHERE userid = '" & Mev1 & "'", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
rst![open] = Now()
rst.update
rst.Close


Private Sub Form_Close()
Dim mev1 as string
Dim rst As New ADODB.Recordset
mev1 = UserName()
rst.Open "SELECT * FROM logtable WHERE userid = '" & Mev1 & "'", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
rst![closed] = Now()
rst.update
rst.Close

 

[newguy86]

Irish1957,
Thank you for your input. But as I have stated before, I already have what you are suggesting built into my database. The problem is that if someone closes the form but leaves the database open, then my records show that no one is using the database. And the database is still locked.

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[joelflorendo]

You can do a couple of things to prevent people from closing your form.

1) You can set the Control Box and Close Button properties to NO on the form (on the Format tab towards the bottom)

2) You can use vba to make the form invisible by using the following from any code module that fires at startup:

Docmd.OpenForm "<LogForm>"
form_<LogForm>.Visible = False

The only hang up is that you can't run this from the Logging form itself. I *think* if any field has focus on the form you want to make invisible, it will throw an error.

 

[GingerR]

newguy--how's it going? your IT dept should already have a list of every computer and who owns it.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[newguy86]

GingerR,
Things are going good. I am able to find out that information from our IT dept, but we are currently working on a huge integration project that has our IT resources completely tied up until early next year. So I don't want to keep bothering them to continually lookup different computer names. But I have started logging the different computer names that come up and checking everyone's computer for their computer name, so that should help me out for now.

joelflorendo,
I think somthing similar to your solution may have already been posted here but I will try it and see what happens.

Thanks everyone!

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!