# MS Access 2007 - How to track users ???



## Esraa (Apr 14, 2012)

Hello, i neeeeed your help plzz  

I'm using MS Access 2007, my database will be used by 6 users !

I have 7 tables ( candidates, status, headcount, user ....... etc) 
the candidate fields are ( candidate name, phone , email, ID.....)
the user ( userName, userID ...)

what i want is to do history table for users .. 
(userName, action, in which field and record, time, date) 

for example, if the user "A" update the phone no. of "x candidate" in the table the result will be like this ....

A | update | x | phone | 9:00 am | 15 April


----------



## AlbertMC2 (Jul 15, 2010)

Hi

You can only do this via a form. If you change the database by editing the tables directly it won't work. So for each form you would use the Form AfterUpdate event to write to the User tracking table - if there were any changes made.

I take it your users are already set up?

For more info see this site for some simple VBA programming on how to start setting this up.

The db.execute command would be something like:

```
db.Execute "INSERT INTO [tblAuditTrail] VALUES ('" & [userID] & "','" & modify & "','" & _ [CandidateName] & "','" & updateField & "'," & time() & "," & date() & ");"
```
Where:
[tblAuditTrail] is the name of the table that will track your changes
[userID] is the userID of the user that makes the change
modify is a variable, determined if the record is changed, deleted, inserted(new record)
[CandidateName] is the ID of the record that was changed/deleted/inserted 
updateField is the field that was changed (problem if 2 fields in a record are changed)
Date() and Time() determine date and time at that moment

Note! I have not tested this.


----------



## Esraa (Apr 14, 2012)

thank you so much Albert ..

but i have some questions.. how can i set up the user ?
do i need to link the user's table with other tables ?

I have 8 forms ... how do all of these forms know the user???

thank you again


----------



## AlbertMC2 (Jul 15, 2010)

Hi

Access 2007 (accdb) does not support user level security but....
If you convert your database to 2003 (mdb) then it will.
You can run *Database Tools* tab -> *Administer *group -> *Users and Permissions* -> *User Level Security Wizard*.
This will help you setup users. When done you will have to enter a user name when opening the database. And that is how it will know which user it is.

Alternatively, if you want to keep the Access 2007 format you will have to write something.
You could create a user form that checks a person's username and password against a table that holds all the usernames and passwords.
Access can be setup so that a certain form is opened when the database is opened. The user login form can then be opened automatically. 
The login form will loop until the user either exits or supplies the correct username/password, in which case the next form (switchboard?) will open.
The username or userID will then be held in a variable.
Note this way has a work around and is not very secure.

The user table does not have to be linked to any other table.


----------



## AlbertMC2 (Jul 15, 2010)

Hi

I have created a simple 2007 DB with username/password access. Please see the attached DB.
When you open it will immediately ask for a username and password. 
Choose username="peter" and password="Peter" (case sensitive) then the switchboard will appear - if the correct info is given.
If you want to see the underlying tables, code etc then hold the shift key in while opening the database. This is what I meant about a work around.
Once logged in the UserID is stored in a global variable called usernameVar which can be called from anywhere, anytime.


----------



## Esraa (Apr 14, 2012)

amazing !!
I don't know how to thank you ..
you really save my life!! 

and one more question :uhoh:,, 
how did you hide tables and all stuff on the left ??

Thank you soooo much !!!! :flowers:


----------



## AlbertMC2 (Jul 15, 2010)

Click on the round *Office Button* (top left)
On the menu click *Access Options*
On the left click *Current Database*
This is where you get your startup options, Application options, what menus to hide and display options for the specific/current database.


----------



## Esraa (Apr 14, 2012)

sorry I'm asking too much !

users & password are working perfect !!
but when i tried to call usernameVar in another form .. nothing is happend !!


----------



## AlbertMC2 (Jul 15, 2010)

Sorry. yes I see that now. I declared the global variable incorrectly.
Open the modGlobVar module under the Modules Object group.
Then instead of 

```
Dim usernameVar As Integer
```
type in

```
Public usernameVar As Integer
```


----------



## Esraa (Apr 14, 2012)

it doesn't work too !


----------



## AlbertMC2 (Jul 15, 2010)

Hi

See the attached database.
Once logged in the switchboard will appear. Open the test form (in edit or add mode)
Click on the button. Note a message box appears with the User number/ID.
I used the user number/ID in case there are 2 people with the same name.
The code used for the message box is:

```
MsgBox usernameVar, vbOKOnly
```
so it seems that the variable usernameVar is global as it is not local to the form frmTest (ie. has not been declared in frmTest).


----------



## Esraa (Apr 14, 2012)

yeeees it works great .... 
thanks again!!

& i wonder if there are more than one user at the same time... the value of the usernameVar will has any problem or conflict ????


----------



## AlbertMC2 (Jul 15, 2010)

The variable will be local to the PC. So each PC logged in as a different user will hold their own User ID. In fact, as far as I understand it, each instance of Access will hold its own instance of the global variable. So there shouldn't be any conflicts.

As for problems. I have read that if there are any problems in your application your global variables will reset. I do not know when or what constitutes a "problem" or why it would do this.

I tend to stay away from global variables as it is not considered good programming practice. However it is the easier alternative.


----------



## Esraa (Apr 14, 2012)

thanx alot Albert !
I spent long time to work on this code :



> db.Execute "INSERT INTO [tblAuditTrail] VALUES ('" & [userID] & "','" & modify & "','" & _ [CandidateName] & "','" & updateField & "'," & time() & "," & date() & ");"


I tried Also many codes to insert data in table but they didn't work at all


----------



## AlbertMC2 (Jul 15, 2010)

Hi

You will have to show me what code you are using. The code you supplied in the previous post was the code I supplied in post 2 and was meant as an example of the command that you would use to update an audit trail table.
Read post2 again and modify the code according to your database.

Alternatively see these 2 sites on how to create an audit trail for any changes made.
Note that you will have to modify these 2 as well to get them to work with your database:
1. How to create an audit trail of record changes in a form in Access 2000
2. Access/VBA Tutorials - Creating an Audit Trail


----------

