CREATE TABLE [Audit Trail]
(
  id AUTONUMBER NOT NULL PRIMARY KEY,
  user CHAR(16) NOT NULL,
  timestamp DATETIME NOT NULL,
  action CHAR(254) NOT NULL,
  description CHAR(254),
  comment MEMO,
);

Public Sub log(ByVal action As String, _
               Optional ByVal description As Variant = Empty, _
               Optional ByVal memo As Variant = Empty)
  Dim q As String
 
  q = "INSERT INTO [Audit Trail] " & vbCrLf
  q = q & "  ([user], [timestamp], [action], [description], [memo]) " & vbCrLf
  q = q & "  VALUES('" & UserName() & "', " & vbCrLf
  q = q & "         #" & Now() & "#, " & vbCrLf
  q = q & "         '" & action & "', " & vbCrLf
  q = q & "         " & IIf(IsEmpty(description), "NULL", "'" & description & "'") & ", " & vbCrLf
  q = q & "         " & IIf(IsEmpty(memo), "NULL", "'" & memo & "'") & ")"
 
  Database.Execute(q)
End Sub

Function WriteLog(strEvent As String, strProcess As String)
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset

  Set dbs = CurrentDb()
  Set rst = dbs.TableDefs("tblLog").OpenRecordset
  With rst
    .AddNew
    !LogEvent = strEvent
    !LogProcess = strProcess
    .Update
    .Close
  End With
  Set rst = Nothing
  Set dbs = Nothing
  
End Function

Function WriteLog(strEvent As String, strProcess As String)
  Dim sSQL As String

  sSQL = "INSERT INTO tblLog ( LogEvent, LogProcess ) " _
    & "VALUES ('" & strEvent & "', '" & strProcess & "')"

  DoCmd.SetWarnings False
  DoCmd.RunSQL sSQL
  DoCmd.SetWarnings True
  
End Function

Set fs = CreateObject("Scripting.FileSystemObject")
Set ts = fs.CreateTextFile("c:\myLogFile.txt")

For Each i In MyArray
  xl.Workbooks.Open (i)
  Do Until xl.ActiveWorkbook.ReadOnly = False
    xl.ActiveWorkbook.Close (False)
    If GetAttr(i) = vbReadOnly Then SetAttr i, vbNormal
    xl.Workbooks.Open (i)
    If xl.ActiveWorkbook.ReadOnly = False Then
        ts.WriteLine "opened file: " & xl.ActiveWorkbook.Name
        Exit Do
    End If 
Loop    'Loop above till read/write active

'''''More code here when workbook read/write mode
Next

Set fs = CreateObject("Scripting.FileSystemObject")
Set ts = fs.CreateTextFile("c:\myLogFile.txt")

ts.WriteLine "opened file: " & xl.ActiveWorkbook.Name

Sub create_log_file()

' Create File System object
    Set SysObj = CreateObject("Scripting.FileSystemObject")
' Create log file
    Set TextStream = SysObj.CreateTextFile("c:\errorlog.log", True)
' Close logfile
    TextStream.Close

End Sub

Sub write_errors(error_message As String)

' Create File System object
    Set SysObj = CreateObject("Scripting.FileSystemObject")
' Get log file
    Set TextFile = SysObj.GetFile("c:\errorlog.log")
' Open log file for appending
    Set TextStream = TextFile.OpenAsTextStream(8, -2)
' Write error message to log file
    TextStream.WriteLine error_message
' Close log file
    TextStream.Close

End Sub

Option Compare Database
Option Explicit
Private Const logBox As Boolean = True
' get computer name
Private Declare Function apiGetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Private Function ComputerName() As String
Dim sysName As String
Dim sLen As Long
sLen = 10&
sysName = String$(sLen, vbNullChar)
If apiGetComputerName(sysName, sLen) = 0& Then
ComputerName = "Unknown"
Else
ComputerName = Left$(sysName, sLen)
End If
End Function

Public Function OpenLog(obj As Object) As Long
Dim SDoc As String
Dim rs As DAO.Recordset
If logBox Then
SDoc = obj.Name
Set rs = DBEngine(0)(0).OpenRecordset("LOG_TABLE", dbOpenDynaset, dbAppendOnly)
rs.AddNew
rs!OpenDateTime = Now()
rs!CloseDateTime = Null
rs!DocName = SDoc
rs!ComputerName = ComputerName()
rs.Update
rs.Bookmark = rs.LastModified
OpenLog = rs!LogDocID
rs.Close
End If
End Function

Public Function CloseLog(obj As Object) As Long
Dim rs As DAO.Recordset
Dim sqlString As String
Dim SDoc As String
Dim sysString As String
If logBox Then
SDoc = obj.Name
sysString = ComputerName()
sqlString = "SELECT LOG_TABLE.* FROM LOG_TABLE WHERE ((LOG_TABLE.DocName = """ & SDoc & """) AND (LOG_TABLE.ComputerName = """ & sysString & """) AND (LOG_TABLE.CloseDateTime Is Null) AND (LOG_TABLE.OpenDateTime <= Now())) ORDER BY LOG_TABLE.OpenDateTime, LOG_TABLE.LogDocID;"
Set rs = DBEngine(0)(0).OpenRecordset(sqlString)
If rs.RecordCount > 0& Then
rs.Edit
rs!CloseDateTime = Now()
rs.Update
Else
rs.AddNew
rs!OpenDateTime = Null
rs!CloseDateTime = Now()
rs!DocName = SDoc
rs!ComputerName = sysString
rs.Update
End If
rs.Bookmark = rs.LastModified
CloseLog = rs!LogDocID
rs.Close
End If
End Function

Option Compare Database
Private Sub Command0_Click()
test_errorhandler
End Sub
Public Function test_errorhandler()
Dim db As Database
Dim rst As Recordset
On Error GoTo test_errorhandler_Error
Set db = CurrentDb
Set rst = db.OpenRecordset("select * from table1")
rst.Close
test_errorhandler_Exit:
Exit Function
test_errorhandler_Error:
logfile_handler Err, Err.Description, "test_errorhandler()", CurrentDb.Name
Resume test_errorhandler_Exit
End Function
Public Function logfile_handler(ByVal erNo As Long, ByVal erDesc As String, ByVal procName As String, ByVal dbName As String)
On Error GoTo logfile_handler_Error
Dim Log_file As String
Dim msg As String
Log_file = "Path for file\FileName.txt"
Open Log_file For Append As #1
Print #1, Now()
Print #1, "Database : " & dbName
Print #1, "Procedure: " & procName
Print #1, "Error No.: " & erNo
Print #1, "Desc. : " & erDesc
Print #1, String(40, "=")
Close #1
msg = "Procedure Name: " & procName & "Error : " & erNo & " : " & erDesc
MsgBox msg, , "logfile_handler()"
logfile_handler_Exit:
Exit Function
logfile_handler_Error:
MsgBox Err & " : " & Err.Description, , "logfile_handler()"
Resume logfile_handler_Exit
End Function