13. maj 2005 - 12:36Der er
27 kommentarer og 1 løsning
Log over Aktivitetsrapport (Audit over brugerhændelser)
Jeg har en funktion som gør at jeg kan se hvilke forms og reports de forskellige brugere har åben (i realtime). Mit problem er at jeg gerne vil ha en log over hændelserne fx i en tabel, hvor jeg kan se at bruger xxx har haft "form xxx" åben, og hvornår den er åbnet. På den måde vil jeg kunne se hvad de forskellige brugere har haft åben på angivne tidspunkter.
Eventuelt en funktion som gør at hver gang der er ændringer (fx en bruger åbner en formular) så skrives det til en tabel.
Hej turpin, jeg ved ikke om det er det du er ude efter, men på denne url:http://www.nyholm.dk/makeiteasy/Home.asp, er det fremragende eks. under menuen download/gem historik på tabeller, måske lidt inspiration der :o)
Umiddelbart ser det ikke lige ud som om jeg kan bruge det (eller også er jeg bare for grøn). Lidt mere specifikt, så gør den funktion som jeg har nu det at den skriver til en tabel, men den bliver ved med at skrive til det samme felt (record). Jeg ville gerne have den til at skrive hver hændelse til et nyt felt (record), således at jeg har en log over brugen af databasen (hvilke "forms" og "reports" som har været åbne).
Hej turpin Isnt it just a case of modifying the SQL which UPDATES a record to INSERT a new record instead. I could imagine that there is some code INSERT's a record for a specific user+form/report if not found or UPDATES if found. So you would need to alter this to INSERT a record with the information you want to see
her er koden til det modul som jeg bruger, kan du få noget ud af det?
Option Compare Database Option Explicit
Global adbfrm As Object Global adbfrmnm As String Global adbfrmnm_l As String Global adbfrmno As Integer Global adbrep As Object Global adbrepnm As String Global adbrepnm_l As String Global adbrepno As Integer Global adbdate As Date Global adbuser As String Global abduserchk As String
Global adballnm As String Global adballno As Integer
Public Sub adbActive() Dim TmpActDb As DAO.Database Dim TmpAct As DAO.Recordset Dim A As Integer Dim B As Integer Dim C As Integer
Set TmpActDb = CurrentDb Set TmpAct = TmpActDb.OpenRecordset("activity_q") Set adbfrm = Nothing adbfrmnm_l = 0 adbrepnm_l = 0 A = 0 B = 0 C = 0 adballnm = "" adballno = 0 adbfrmnm = "" adbfrmno = 0 adbrepnm = "" adbrepno = 0 adbdate = Now() adbuser = CurrentUser For Each adbfrm In Forms adbfrmnm = adbfrmnm & adbfrm.name & "; >> " Next adbfrm For Each adbrep In Reports adbrepnm = adbrepnm & adbrep.name & "; >> " Next adbrep
adbfrmno = Forms.Count adbrepno = Reports.Count
adbfrmnm_l = Len(adbfrmnm) If Len(adbfrmnm) > 0 Then adbfrmnm_l = adbfrmnm_l - 5 adbfrmnm = Left(adbfrmnm, adbfrmnm_l) If Len(adbfrmnm) > 0 Then adbfrmnm = "FORMS >> " & adbfrmnm
adbrepnm_l = Len(adbrepnm) If Len(adbrepnm) > 0 Then adbrepnm_l = adbrepnm_l - 5 adbrepnm = Left(adbrepnm, adbrepnm_l) If Len(adbrepnm) > 0 Then adbrepnm = "REPORTS >> " & adbrepnm
If Len(adbfrmnm) > 0 Then A = 1 If Len(adbrepnm) > 0 Then B = 2 C = A + B Select Case C Case 0 adballnm = "Nothing open, but MSAccess has not closed down" Case 1 adballnm = adbfrmnm Case 2 adballnm = adbrepnm Case 3 adballnm = adbfrmnm & "; << " & adbrepnm Case Else MsgBox "Opened forms/reports count is incorrect" End Select adballno = adbfrmno + adbrepno
If TmpAct.RecordCount < 1 Then TmpAct.AddNew Let TmpAct("usrnme") = adbuser Let TmpAct("opened") = adballnm Let TmpAct("numopen") = adballno Let TmpAct("date") = adbdate TmpAct.UPDATE Else TmpAct.MoveFirst Do Until TmpAct.EOF = True abduserchk = TmpAct("usrnme") If abduserchk <> adbuser Then If TmpAct.EOF = True Then TmpAct.AddNew Let TmpAct("usrnme") = adbuser Let TmpAct("opened") = adballnm Let TmpAct("numopen") = adballno Let TmpAct("date") = adbdate TmpAct.UPDATE End If 'Else If abduserchk = adbuser Then TmpAct.Edit Let TmpAct("opened") = adballnm Let TmpAct("numopen") = adballno Let TmpAct("date") = adbdate TmpAct.UPDATE End If End If If abduserchk = adbuser Then TmpAct.Edit Let TmpAct("opened") = adballnm Let TmpAct("numopen") = adballno Let TmpAct("date") = adbdate TmpAct.UPDATE End If TmpAct.MoveNext Loop End If TmpAct.Close Set TmpActDb = Nothing End Sub
Public Sub adbActiveClose() Dim TmpActDb As DAO.Database Dim TmpAct As DAO.Recordset
Set TmpActDb = CurrentDb Set TmpAct = TmpActDb.OpenRecordset("activity_q") Set adbfrm = Nothing adballnm = "Logged Out" adballno = 0 adbdate = Now() adbuser = CurrentUser
If TmpAct.RecordCount < 1 Then TmpAct.AddNew Let TmpAct("usrnme") = adbuser Let TmpAct("opened") = adballnm Let TmpAct("numopen") = adballno Let TmpAct("date") = adbdate TmpAct.UPDATE Else TmpAct.MoveFirst Do Until TmpAct.EOF = True abduserchk = TmpAct("usrnme") If abduserchk <> adbuser Then If TmpAct.EOF = True Then TmpAct.AddNew Let TmpAct("usrnme") = adbuser Let TmpAct("opened") = adballnm Let TmpAct("numopen") = adballno Let TmpAct("date") = adbdate TmpAct.UPDATE End If 'Else If abduserchk = adbuser Then TmpAct.Edit Let TmpAct("opened") = adballnm Let TmpAct("numopen") = adballno Let TmpAct("date") = adbdate TmpAct.UPDATE End If End If If abduserchk = adbuser Then TmpAct.Edit Let TmpAct("opened") = adballnm Let TmpAct("numopen") = adballno Let TmpAct("date") = adbdate TmpAct.UPDATE End If TmpAct.MoveNext Loop End If TmpAct.Close Set TmpActDb = Nothing End Sub
men behøver det at være så indviklet? Det kan vel gøres med en tilføjelsesforespørgsel. Definer dine værdier og lav forespørgslen a la: INSERT INTO tabel ( kolonner ) VALUES (værdier)
Looks a rather complicated routine, I'm sure it could be simplified. If you can send me your dB then I may get time to look at it and make some modifications. But if anyone else can help then great, as I'm rather busy the next day or two.
Har prøvet at erstatte TmpAct.Update med med TmpAct.Insert, men det bliver ikke genkendt som en handling - derfor duer den ikke.
Det andet som du nævner med at lave en "Append Query" har jeg også prøvet at rode rundt med, men jeg kan ikke finde ud af at få den til at køre automatisk hver gang der er ændinger. Det skal lige nævnes at mine programmerings egenskaber kan ligge på et meget lille sted!
The function I need is to add a record every time a form/report is opened (not timebased). What would be great is if it could then log the whole record (including all fields from the form) so that it also makes a new entry if I move to the next record
Hope this is understandable, otherwise please write again.
If a user opens a form ex. "Kunder". Then i need the system to log the whole form to another table + the user and time. If the user moves to the next record (costumer) then this should be logged to.
That way I would have a complete log of whats been viewed and by whom and when.
I dont think its a problem logging the time a form/report is opened, this can be done using only one table but if you want to show the changes you make to the data (tables/queries) on a form then this is not so simple. aandersen has given a link (13/05-2005 13:14:19) to ThomasJepsen's example which could be used for this.
So it is NOT just modifying your code which you use already as much more information needs to be saved.
I can also see that you have some code in a function (AuditData) which records changes to a record in memo field, cant you use this for recording all changes?
The funktion AuditData() records all changes in the tables and that function is ok. But the other function I need is a log of the user activity. If a user has opened [forms.kunder], then I need a log of when it was opened, the user who opened it and the whole information from that record (Firmanavn, adresse, by et.c.). If the user moves on to the next record (kunde) then this should also be written to the log.
In that way I have a complete log of who accessed the system and what the users have been looking at.
Maybe the system could have a reference table for each table in the DB. Exam. for table "Kunder" there could be a table called "kunder_log". Whenever a user then opens table "kunder" it takes all the information from the current record that the user is viewing and inserts it into table "kunder_log" + the username and timestamp.
You will need a log table for each table you have in the database. Are the log tables to be in activity.mdb?
Are you saying that even if the data is NOT changed then you want to record the information? This is going to give LOTS of records in the log table!
So lets be sure what it is you want.
1: User opens a form. Record record information and user name in a log table. 2: User changes information in a form. Record record information and user name in a log table. 3: User creates a new record. Record record information and user name in a log table.
1: User opens a form. Record record information and user name in a log table. (note: when the form is open, and the user goes to the next record, then I want to record the information from the next record, and so on.. So the answer to the text underneeth is YES "Are you saying that even if the data is NOT changed then you want to record the information? This is going to give LOTS of records in the log table!")
2: User changes information in a form. Record record information and user name in a log table. 3: User creates a new record. Record record information and user name in a log table. (The AuditData() function takes care of this part as far as I know!)
The log tables should be in activity.mdb
If you can figure out how to do this, then you have saved my day:)
If you can just show it to me in table "kunder", then I guess I can copy paste the code to the rest of the tables.
From your comments I'm not 100% sure what I should do! The comment under 2 and 3 ((The AuditData() function takes care of this part as far as I know!)) make sme think that you only want to see wich records the user has looked at, in other words only from 1:
and if that is correct then it may not be necessary to have ALL fields from the table in a log table. If you linked all tables which you want to log into the activity.mdb then you only need to log the primary key(ID) of the records the user looks at and then make a query joining the log table to the linked table. This will save you having duplicate information in the log and original table. But if you also want to record the changes a user makes in the log table then you will need a copy with all fields+user+date/time
"But if you also want to record the changes a user makes in the log table then you will need a copy with all fields+user+date/time" Can this be done? The way I understand this, is that the log table contains information about views, changes and new records (is that correct?)
"and if that is correct then it may not be necessary to have ALL fields from the table in a log table. If you linked all tables which you want to log into the activity.mdb then you only need to log the primary key(ID) of the records the user looks at and then make a query joining the log table to the linked table. This will save you having duplicate information in the log and original table"
I think it would be preferable to have the whole table logged, otherwise would I still be able to se the exact information that the user has viewed?
But it would be ok to have the AuditData() record all creations and changes, and then have a seperate logtable for each table wich shows what has been viewed (fields + user + date/time).
This is what I will do for you and I will do it only for "kunde" form/table, then you can try and do it for the other forms/tables.
I will make a table in activity named kunde_log. In this table there will be a field which will contain the ID of the record viewed (foreign key). A field for the user and another for tehh date and time. I will link the table "kunde" into the activity.mdb and then make a query where I join tables "kunde" and "kunde_log" on ID. This will show you the information for the record viewed, NOT changed or inserted.
In form "kunde" I will make some code which inserts a record (ID, user, data and time) into "kunde_log" which will now be linked into the database. This will be done for all records you view. If you have your eyes closed then this still gets done :o)
So table kunde_log will contain the ID+user+date/time. Will I be able to to tell what was in table "kunder" at the time the user viewed a record, if that record changes at a later time?
Otherwise I think it would be better to have all the fields logged into table "kunde_log" (if that's ok!)
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
