Audit trail assistance in access 97

[Access97VBstumpted]

I am develooping an audit trail script in VB for an Access 97 application. Is
there a visual basic 6.0 script that can determine if a table field is a
primary key? I wish to capture the unique primary key for each record that
is viewed/updated/created. Here is a snippet of how my code is structured,
it only updates a log table when a new record is created.

Function AuditTrail()
On Error GoTo Err_Handler

Dim dbs As DAO.DATABASE
Dim rs As DAO.Recordset
Dim MyForm As Form, C As Control, xName As String
Set MyForm = Screen.ActiveForm

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("AddressBookLog", dbOpenDynaset)
'If new record, record it in audit trail and exit sub.
If MyForm.NewRecord = True Then
With rs
.AddNew
![NetworkUserID] = NetworkUser()
![ComputerName] = ComputerName()
![UserSecurityID] = User.SecurityID
![ApplicationUserID] = User.UserID
![Application] = dbs.Name
![FormName] = MyForm.Name
'![UniquePrimaryRecordKey] = ?
![RecordID] = MyForm.CurrentRecord
![DataBefore] = "New Record Created"
.UPDATE
End With
End If
TryNextC:
Exit Function

Err_Handler:
If Err.NUMBER <> 64535 Then
MsgBox "Error #: " & Err.NUMBER & vbCrLf & "Description: " &
Err.Description
End If
Resume TryNextC
End Function

 

[Allen Browne]

You can certainly get the primary key, but consider:
- A table may have a compound primary key (key made up of more than one
field).
- A recordset may include multiple tables, each with their own primary key
field(s).
A generic function to return the "primary key" of a recordset might
therefore need to return an array of arrays, i.e. multiple tables' primary
keys, each consisting of multiple fields.

If you just want to identify the AutoNumber field, check the Attributes of
each Field in the Recordset for dbAutoIncrField. Example:

Function GetAutoNum() As String
Dim rs As DAO.Recordset
Dim fld As DAO.Field

Set rs = DBEngine(0)(0).OpenRecordset("SELECT * FROM MyTable;")
For Each fld In rs.Fields
If (fld.Attributes And dbAutoIncrField) > 0 Then
GetAutoNum = fld.Name
Exit For
End If
Next
rs.Close
End Function

If you want the primary key, that's a function of the table (not of a
field). You can retrieve the indexes this way, test the Primary property to
get the one that's the primary key, and look at its Fields collection to see
which field(s) you have to store to identify the record. This example shows
how to enumerate the indexes of a table:

Function ShowIndexes(strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs(strTable)
For Each ind In tdf.Indexes
Debug.Print ind.Name, IIf(ind.Primary, "Primary", ""), _
IIf(ind.Foreign, "Foreign", ""), ind.Fields.Count
Debug.Print " Field(s): ";
For Each fld In ind.Fields
Debug.Print fld.Name;
Next
Debug.Print
Next

Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Function


When I put together some audit-trail code, I did not genericize it to the
level you are aiming for. If you're interested, it's at:
http://allenbrowne.com/AppAudit.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

I am develooping an audit trail script in VB for an Access 97 application.
Is
there a visual basic 6.0 script that can determine if a table field is a
primary key? I wish to capture the unique primary key for each record that
is viewed/updated/created. Here is a snippet of how my code is
structured,
it only updates a log table when a new record is created.

Function AuditTrail()
On Error GoTo Err_Handler

Dim dbs As DAO.DATABASE
Dim rs As DAO.Recordset
Dim MyForm As Form, C As Control, xName As String
Set MyForm = Screen.ActiveForm

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("AddressBookLog", dbOpenDynaset)
'If new record, record it in audit trail and exit sub.
If MyForm.NewRecord = True Then
With rs
.AddNew
![NetworkUserID] = NetworkUser()
![ComputerName] = ComputerName()
![UserSecurityID] = User.SecurityID
![ApplicationUserID] = User.UserID
![Application] = dbs.Name
![FormName] = MyForm.Name
'![UniquePrimaryRecordKey] = ?
![RecordID] = MyForm.CurrentRecord
![DataBefore] = "New Record Created"
.UPDATE
End With
End If
TryNextC:
Exit Function

Err_Handler:
If Err.NUMBER <> 64535 Then
MsgBox "Error #: " & Err.NUMBER & vbCrLf & "Description: " &
Err.Description
End If
Resume TryNextC
End Function