Import Logfile

[jstevens]

I'm not sure where to start importing a log file into Access. The log file contents is listed below.

I would like fields associated with the User, Date/Time and the request to the server as the requirement is to report upon them. An example would be to find all users selecting the application named BUDGET and the respective Date/Time. The application name can change as there are many applications to select from. The log file contains a limited sample of data.

Each record has two rows; the first having the Date/Time and the second contains the request and user information. There is a blank row between record sets.

[Tue Oct 16 10:45:05 2013]Local/Essbase0///Info(1051164)
Received login request from [IPAddress]

[Tue Oct 16 10:45:07 2013]Local/Essbase0///Info(1051187)
Logging in user [User_01] from [IPAddress]

[Tue Oct 16 10:45:09 2013]Local/Essbase0///Info(1051035)
Last login on Wednesday, October 15, 2012 09:04:22 AM

[Tue Oct 16 10:45:11 2013]Local/Essbase0///Info(1051001)
Received client request: Get Application Info (from user [User_01])

[Tue Oct 16 10:45:12 2013]Local/Essbase0///Info(1051001)
Received client request: Select Application/Database (from user [User_01])

[Tue Oct 16 10:45:14 2013]Local/Essbase0///Info(1051001)
Received client request: List Databases (from user [User_01])

[Tue Oct 16 10:45:15 2013]Local/Essbase0///Info(1051009)
Setting application BUDGET active for user [User_01]

[Tue Oct 16 10:47:46 2013]Local/Essbase0///Info(1051037)
Logging out user [User_01], active for x minutes

Your suggestions are appreciated.

Regards,
John

[Pat]

I would import that file into a table (call it Import).
Then I would process that table via VBA reading 3 records at a time and parse the data accordingly and output that to a separate table

[jstevens]

Pat,

Thanks for the reply. What would be the VBA code to update the new table? I have a new table named "Data" with a date field named "ProcessingDate".

I have written code to Open the recordset of a table named "Import"

Code: Select all

With ParseNamesImport
     Do While Not .EOF
          If InStr(![Field1], "Local/ESSBASE0") >0 Then
               oDate = Mid(![Field1],6,20)
               
               .AddNew

               Data.![ProcessingDate] = oDate    'This is where I'm having a challenge in adding/writing to a new table named "Data"
          End If

     Loop
End With

I'm familiar with Excel VBA but not so much with Access.

Regards,
John

[jstevens]

Pat,

After further exploration, I resolved writing to the other table.

I opened another recordset to the table I needed to write to and performed the necessary write/update to the table.

Thanks for your suggestion as I learned something new,
John