Error Log On Network For Multiple Users

mrfitness · Mar 12th 2008

I am creating an error log as a text file for my application. Since it is being used by many different users, I decided to keep it logged in a specific folder (that is protected) on our network.
I have tested the code and it works, even if I have the log file open when I run the module, it still writes to the log file and saves (as long as I don't save the opened log file after the module runs)
What I am worried about is when 2 or more users are trying to write to the log file at the same time. I wanted to add something that checks if the file is open, and if it is, wait a second or two and then try to write to it again. Maybe try it a few times?

Below is my sample code.
Note that I am using the log creation in another module.

Code

Sub datatopass()


LogInformation Worksheets("Email").Range("L2").Value & "|" & _
Application.UserName & "|" & _
Worksheets("Email").Range("M2").Value & "|" & _
Worksheets("Email").Range("N2").Value & "|" & _
"other things" 


end sub


'*******other module**************
Public LogFileName As String


Sub LogInformation(LogMessage As String)
'Const LogFileName As String = "\\n530fs1\PCLFileShares\pcl_reposit\Pricing_Tools\PAT\Security\" & Int(Now()) & "\PAT_APPROVALS.LOG"
Dim FileNum As Integer
Dim mdate
'get month in text
If Month(Int(Now())) = 1 Then
    mdate = "Jan"
    ElseIf Month(Int(Now())) = 2 Then
    mdate = "Feb"
    ElseIf Month(Int(Now())) = 3 Then
    mdate = "Mar"
    ElseIf Month(Int(Now())) = 4 Then
    mdate = "Apr"
    ElseIf Month(Int(Now())) = 5 Then
    mdate = "May"
    ElseIf Month(Int(Now())) = 6 Then
    mdate = "Jun"
    ElseIf Month(Int(Now())) = 7 Then
    mdate = "Jul"
    ElseIf Month(Int(Now())) = 8 Then
    mdate = "Aug"
    ElseIf Month(Int(Now())) = 9 Then
    mdate = "Sep"
    ElseIf Month(Int(Now())) = 10 Then
    mdate = "Oct"
    ElseIf Month(Int(Now())) = 11 Then
    mdate = "Nov"
    ElseIf Month(Int(Now())) = 12 Then
    mdate = "Dec"
End If
ydate = Year(Int(Now()))


LogFileName = "\\n530fs\PTSecurity\" & ydate & "_" & mdate & "_APPROVALS.LOG"
    FileNum = FreeFile ' next file number
    Open LogFileName For Append As #FileNum ' creates the file if it doesn't exist


'*****around here is where I want to put the check and wait?************


    Print #FileNum, LogMessage ' write information at the end of the text file
    Close #FileNum ' close the file
End Sub

Display More

**Ger Plante** · Mar 12th 2008

Re: Create Error Log On Network For Multiple Users

Quote

What I am worried about is when 2 or more users are trying to write to the log file at the same time.

Well, its easy enough to test... take a look at this code for example:

Code

Public Sub test()


Open "c:\test.txt" For Append As #1 ' creates the file if it doesn't exist


             
    Print #1, "TEST!!" ' write information at the end of the text file
[B][U][I]    'next line errors[/I][/U][/B]
Open "c:\test.txt" For Append As #1 ' creates the file if it doesn't exist


             
    Print #1, "TEST!!" ' write information at the end of the text file




Close #1


End Sub

Display More

Basically it tries to open the same text file twice, but it is not closed after the first time.

So if it happens that two people / processes try and open the file for output you will generate a Run Time Error 55 : File already open.

So before your Open statement, you need an "on error resume next" statement and keep looping while the err.number = 55

HTH
Ger

mrfitness · Mar 12th 2008

Re: Create Error Log On Network For Multiple Users

Quote from Ger Plante

So before your Open statement, you need an "on error resume next" statement and keep looping while the err.number = 55

Well doesn't this part of the code make sure it doesn't error on open:

Code

FileNum = FreeFile ' next file number
    Open LogFileName For Append As #FileNum ' creates the file if it doesn't exist

So in essence the text file will open no matter what, but only one of the open files will save? Maybe I should change FileNum to a 1?

**Ger Plante** · Mar 13th 2008

Re: Error Log On Network For Multiple Users

The Open statement will attempt to open the text file. If it is ALREADY open then the Open statment will fail (this is what I was showing you with my code above). So no, the Open statement wont "Wait" until the file is open. Thats why you need to let the error happen (error 55) and trap it afterwards. If you run my code you will see what I am mean.

The number 1 is irrelevant. Its just a number assigned to the file. the Freefile function will generate this number and should always be used. I just took a short cut and replaced it with 1. Having a file number assigned to a file is just a VBA way of taking a short cut by not having to constantly refer to the file name.

HTH
Ger

mrfitness · Mar 13th 2008

Re: Error Log On Network For Multiple Users

Quote from Ger Plante

The Open statement will attempt to open the text file. If it is ALREADY open then the Open statment will fail (this is what I was showing you with my code above).

What I did to test was that I went on the network and opened the text file that the program writes to. Then I ran the program and thought I would get an error on the OPEN statement (as I had the text file opened) but it didn't error out at all (I even put in an error handler in the code)

Instead, after the program ran, I closed the text file, re-opened it, and it contained the new data just written from the program!

Please try doing a similar test on your computer with your code using "c:\test.txt", and remove the 2nd open statement in your code above. Let me know if you also do not get an error so I know I am not going crazy.

Thank you so much!

**Ger Plante** · Mar 14th 2008

Re: Error Log On Network For Multiple Users

Interestingly, you're right... when the Operating System has the Log open (in Notepad), the you can Open for Append and write to it as you have in your code. No problem. Remember to always close an open text file.

However, if Excel opens the text file, you can not open it again for Append without causing a "File already open" error...

You'll need to play around with this a bit more... use two machines and a shared out text file (on a network, or shared folder). On one machine, just step through your code (or my code) and when the text file is open, go to another machine and try and open that same text file for append again (same code, different machine, accessing the same file).

My bet is that it will crash again saying the file is already open.

Ger

mrfitness · Mar 14th 2008

Re: Error Log On Network For Multiple Users

Quote from Ger Plante

Use two machines and a shared out text file (on a network, or shared folder). On one machine, just step through your code (or my code) and when the text file is open, go to another machine and try and open that same text file for append again (same code, different machine, accessing the same file).
My bet is that it will crash again saying the file is already open.
Ger

Funny you mention that, I did test it out on 2 computers at the same time with the log file on the network. I ran the same code at the exact same time with the notepad open and did not receive any errors. When I closed notepad and re-opened it, both our logs were there! I did try this test many times, and I even put a toggle break on each of our VBA windows right before it writes to the log file and pressed the 'Play' button at the same time and it still worked.

Participate now!

Share