need to create a log of actions performed by DAO/VBA

[n8kindt]

hi, i have created a program that creates reports one by one and sends them out to the recepients systematically via email. i'm wondering how do i make a summary log report that reports what time a report was sent out, how many were sent out, how many failed, and any other actions performed within the code (with specific details about each one)... in other words, i need more than a count of how many were sent. then i need to export this data as a text file hopefully. where in the world do i start on this one? searching "vba report" gets me nowhere for fairly obvious reasons :)

i can post my code if need be, but i was just looking for someone to point me in the right direction so i can do it by myself.

[Zwoker]

Hi,

It may be a little simplistic, but rather than trying to use any sort of MS Access report, you could just write the status of each activity that your program performs to a text file. You could use code something like the following:

Dim fs As Object
Dim LogFile
 
Set fs = CreateObject("Scripting.FileSystemObject")
Set LogFile = fs.CreateTextFile("ResultsLog.txt", True)
 
<if some table read action succeeded>
LogFile.Writeline "Successfully read " & CustRst.RecordCount & " customer records"
<or if something failed>
LogFile.Writeline "Error is reading from Cust table"
 
LogFile.Close

Obviously this code is a little simple, and is a mixture of real code and pseudo-code, but I'm sure you get the idea.

And if anyone knows what type the LogFile should be declared as I'd appreciate you telling me. I couldn't work it out, but it works like this (in MS Access 2003, anyway).


Let me know if this is what you were looking for.


Regards,
Zwoker.

[n8kindt]

Hi,

It may be a little simplistic, but rather than trying to use any sort of MS Access report, you could just write the status of each activity that your program performs to a text file. You could use code something like the following:

Dim fs As Object
Dim LogFile
 
Set fs = CreateObject("Scripting.FileSystemObject")
Set LogFile = fs.CreateTextFile("ResultsLog.txt", True)
 
<if some table read action succeeded>
LogFile.Writeline "Successfully read " & CustRst.RecordCount & " customer records"
<or if something failed>
LogFile.Writeline "Error is reading from Cust table"
 
LogFile.Close

Obviously this code is a little simple, and is a mixture of real code and pseudo-code, but I'm sure you get the idea.

And if anyone knows what type the LogFile should be declared as I'd appreciate you telling me. I couldn't work it out, but it works like this (in MS Access 2003, anyway).


Let me know if this is what you were looking for.


Regards,
Zwoker.

perfect!! yes, that is exactly what i needed--nothing fancy. i appreciate your help! i'll see if i can get the LogFile declared properly...

[Zwoker]

perfect!! yes, that is exactly what i needed--nothing fancy. i appreciate your help! i'll see if i can get the LogFile declared properly...

The LogFile declaration will work as coded above, it just always bothered me that I couldn't discover what type it should actually be.

I'm glad this is what you were looking for. I wondered if you needed something a bit more sophisticated.


Regards,
Zwoker.

[NeoPa]

A TextStream object might be what you're looking for (See http://www.csidata.com/custserv/onli...ocs/vbs279.htm)

I'm not sure which library this would be found in but I don't believe it's native Access.

An alternative approach (using very similar logic) would be to use Open# in VBA (look in Access Help for Open Statement).

[n8kindt]

A TextStream object might be what you're looking for (See http://www.csidata.com/custserv/onli...ocs/vbs279.htm)

I'm not sure which library this would be found in but I don't believe it's native Access.

An alternative approach (using very similar logic) would be to use Open# in VBA (look in Access Help for Open Statement).

thanks for your post! i will look into that as well...

cheers

[ADezii]

You could use a low level I/O Function in conjunction with a Public Sub-Routine, to produce an impressive Log. A simple example would be:

Public Sub WriteToStatusFile(dteTimeOut As Date, intNumberSent As Integer, strStatus As String)
Dim strMsg As String
 
strMsg = "Report Sent: " & Now() & vbCrLf
strMsg = strMsg & "Number Sent: " & intNumberSent & vbCrLf
strMsg = strMsg & "Status     : " & strStatus
 
Open "C:\Report Log.txt" For Append As #1
Print #1, strMsg
Print #1, "==========================================="
Close #1
End Sub

Sample Call to Routine

Call WriteToStatusFile(Now(), 122, "Success")

Sample Log File resulting from above Calls:

===========================================
Report Sent: 6/3/2008 10:25:43 PM
Number Sent: 122
Status     : Success
===========================================
Report Sent: 6/3/2008 11:58:59 PM
Number Sent: 0
Status     : Failure
===========================================

[FishVal]

Would it be more suitable to store log in a table?
Text files are somewhat not suitable for further analyzing.

Regards,
Fish

[ADezii]

Would it be more suitable to store log in a table?
Text files are somewhat not suitable for further analyzing.

Regards,
Fish

Good point FishVal, and yes it would be more practical and efficient. I guess I just took the easy way out on this one, when I should have ran this option through the OP. Thanks again.

[NeoPa]

It's a fair point Fish, but don't forget the code and ideas can be manipulated to do things different ways. Worth mentioning though.

[n8kindt]

Would it be more suitable to store log in a table?
Text files are somewhat not suitable for further analyzing.

Regards,
Fish

i thought might be an option too... so should i just run an append query to fire off for each time something needs to be logged? i don't need anything too complex. but for history purposes i can see how the table idea is practical.

[NeoPa]

The easiest way to convert ADezii's, already provided, code would be to use RecordSet processing code rather than SQL.

@ADezii
One simple comment I would make on the code provided, is that using FreeFile() to determine the file number (rather than simply using 1) would generally be recommended ;)

[ADezii]

The easiest way to convert ADezii's, already provided, code would be to use RecordSet processing code rather than SQL.

@ADezii
One simple comment I would make on the code provided, is that using FreeFile() to determine the file number (rather than simply using 1) would generally be recommended ;)

It is the generally accepted practice, and I always use it whenever I'm opening more than 1 File at a time, but you are correct in that it should always be used regardless of how many Files are being opened. Thanks for the pointer.

[youmike]

I would strongly recommend the use of a table to store log records. It gives the greatest flexibility in terms of layout to accommodate whatever action or event you might need to log and it can be interrogated by query and report, again in whatever format you need.

The systems that I design all have this need and I include a VB module into which parameters (such as userID, machineID, action, old value, new value, name of changed table, or whatever) are passed from wherever the need arises and written to tblLog. This log table always includes a field into which NOW is written.

[n8kindt]

I would strongly recommend the use of a table to store log records. It gives the greatest flexibility in terms of layout to accommodate whatever action or event you might need to log and it can be interrogated by query and report, again in whatever format you need.

The systems that I design all have this need and I include a VB module into which parameters (such as userID, machineID, action, old value, new value, name of changed table, or whatever) are passed from wherever the need arises and written to tblLog. This log table always includes a field into which NOW is written.

this got me thinking... i have history events set up in 3 different tables. one for status changes, one for purchases, one for address changes, one for general profile history, and i will be creating one more for email receipt history. ok, i think that is more than 3 haha. anyways, i can set up a union query to combine all these records and sort them by time stamp right? or should i just redesign my database to chunk it all into one table?

[NeoPa]

Hard to be absolute without a more in-depth understanding of your particular databse nate, but in general I would go for the single table approach.

UNION queries typically give poor performance due to the difficulty to optimise them (for Access at least).