Simple Audit Trail? (1 Viewer)

Hello All,
I am currently using Access 2000. I have a mailing list database which I would like to add a simple audit trail to record when a user exports out the whole database into Excel. This is important to the Management to maintain security of the information of the clients in the database. Currently, I have a form which contains a button. When the button is depressd it then runs a query and then exports the complete database into the client's local hard drive. Here is the current code:

Private Sub Command6_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryDBTOEXCEL", "c:\COMPLETE_DB.xls"
MsgBox "Your Spreadsheet has been created and is located on your local computer hard drive C:\COMPLETE_DB.xls"
End Sub


I currently have a table named tblExport with the fields:
date, user, and computer name. How can I update this table tblExport each time when the user depress this button?
I am new to VBA and any help is appreciated in advance. Thank you very much for your time.

benny,

Add this to your code for the command button:



txtCurrentUser and txtCurrentComputer are hidden textboxes
in your database. They can be populated by an API call and
the user does not interact with them.

This forum has examples.

Wayne

date, user, and computer name.

benny,

A link:

http://www.access-programmers.co.uk...did=40302&highlight=ComputerName+And+Username

Wayne

Hello Wayne,
Thanks for your response. I have tried your code and got a error message: "Compile Error: Syntax Error" . Here is the current code that I have in VBA.

Dim dbs As Database
Dim strSQL As String

Set dbs = CurrentDb
' strSQL = "Insert into tblExport (FieldA, FieldB, FieldC) " & _

strSQL = "Insert into tblExport (USER, COMPUTER NAME, DATE) " & _
"Values("#" & Now() & "#, '" & _
Me.txtCurrentUser & "', '" & _
Me.txtCurrentComputer & "');"
dbs.Execute strSQL

I added the USER, COMPUTER NAME, DATE on the line strSQL because it originally was FieldA, FieldB, and FieldC. I don't think this is causing the problem? I am a novice in VBA, but learning.
Thanks for you continued support in advance. Thanks for your time.

Benny

Benny,

You specify the order as User, Computer Name, and Date
and insert them as Date, User and computer. The order
doesn't match.

The Field Computer Name contains a space (bad idea) and
should be [Computer Name] with the brackets. The lack
of the brackets will make SQL think that it is two
fields and thus a syntax error.

The field Date is a reserved word.

Wayne