AUTOEXEC MACRO - Record Log file of who logs in and when 1

[ps32208]

Hi,

I need to record who logs in and when to my db and have been told to run an autoexec macro to record a log file. Bearing in mind I know nothing about macros or programming can anyone help?

Regards,
Pete.

 

[rubbernilly]

Setup security through Access so that users get prompted for a sign-on name and password.

Make a table for the Log, call it "LoginLog". Add fields for the UserName (Text), LoginDate (Date/Time; Short Time), and LoginTime (Date/Time; Short Time). Default the LoginDate and LoginTime fields to be Now.

Edit the startup form for the database. If you don't have a startup form, put one in the startup parameters (Tools>Startup...). In the Load event of that form, put this code somewhere:

dim rs as dao.recordset

set rs = currentdb.OpenRecordset("LoginLog", dbForwardOnly)

rs.AddNew
   rs!UserName = CurrentUser
rs.Update

set rs = nothing

Test this out. You should get a record of everyone who logs into your database and when.

HTH

 

[ps32208]

Thanks for this it looks great, where do I find load event in my start up form. My start up form is the switchboard.

 

[rubbernilly]

Show the properties of the Form (View>Properties). You'll get a dialog box that pops up with all of the form properties you can set, including the Events. The easiest way to find it is to click on the Events tab and then find the OnLoad event in the list there.

Click the build button to the right of the event, and choose "Event Procedure". This will take you to where you need to be in the visual basic editor window.

Paste the above code into the procedure.

Also, in the VBE window, double check that you have the DAO library referenced, as you will need this to run the code. Go to Tools>References. Your references will show up, with the selected options at the top and the rest basically alphabetized. If Microsoft DAO 3.6 is not selected, find it in the list and put a checkmark on it.

Then save the database and give it a try.

HTH

 

[ps32208]

LoginLog table created with three fields:
UserName, LoginDate (defauult Now), Login Time (defauult Now).

Returns values such as: 03/11/2005 16:38:10

I can not see an option for Short Date only: Date/Time.

There is no primary key set.

Security is active on DB, username and login required.
Microsoft DAO 3.6 is selected.

However having pasted code into event proceedure, saved and closed the Db, the table does not populate with username upon login.

I have pasted it at the bottom of all the other code but am not having any success with recording the username.

Could I be pasting it into the wrong place?

 

[rubbernilly]

At the table, make the field type Date/Time, then look below for the properties of the field, and change the Format property to Short Date. In fact, the Date field could be defaulted to Date() rather than Now.

Now, as for the code, you want the code in the OnLoad event of the Switchboard. What it sounds like is your module is defaulted to Full Module view. I don't like view for most everyday programming because it gets to be a lot of chaos. It can also cause problems like what you ran into. You pasted the code at the bottom of the rest of the code that you saw, meaning that you did not put it in a procedure at all.

If you want to turn off the Full Module View (believe me, the layout will make more sense to you), click on Tools>Options, and find the checkbox marked "Default to Full Module View." Uncheck it. Now you will show one procedure at a time.

Now go back to Access, back to the properties of your switchboard, and find the OnLoad event. You should already have the words "Event Procedure" there, because the first time you tried to paste the code, it created the procedure, even though you didn't paste your code there. Click on the build button to the right of the field, and then paste your code between the Private Sub declaration and the End Sub line.

(Also, delete the code from where you originally pasted it).

You can move about in the modules in your project by double clicking on the module and then choosing the object and procedure. I had you go back to the Switchboard properties to make sure that "Event Procedure" was listed there. You can navigate to the event procedure for the form in the VBE window and put all the code there you want, but it won't run until you tell the object that in that Event property, it should run the Event Procedure.

HTH

 

[ps32208]

Ok, I have made these changes, must be close now. The code looks like:

Private Sub Form_Load()
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("LoginLog", dbForwardOnly)

rs.AddNew
rs!UserName = CurrentUser
rs.Update

Set rs = Nothing

End Sub

However when I close the design view of the form Switch board I receive a runtime error '3001': Invalid Argument

I click on Debug and the following row of code is highlighted yellow with an arrow beside it:

Set rs = CurrentDb.OpenRecordset("LoginLog", dbForwardOnly)

Any suggestions?

 

[rubbernilly]

Change the dbForwardOnly to dbOpenForwardOnly.

I must have had poppy seeds for lunch.

If that still gives you an error (it should not), then try it without any argument, with the line looking like this:

Set rs = CurrentDb.OpenRecordset("LoginLog")

Let me know how it goes!

 

[ps32208]

brilliant. I tested both, it worked with:

Set rs = CurrentDb.OpenRecordset("LoginLog")

Many thanks for all your help.

Pete

 

[ps32208]

Is there a way to prevent the Non Admin users from viewing the LoginLog Table, as currently they are required to have read, update data and insert data privilages on this table. They are all users of the same user group.

I say this because I don't really want them to see who else is using the database...