Open and Display Contents of Text Log Files in List Box

Hi all,

My application is an Access 2013 split db with 3 users.

For logging, I am sending Application Specific Errors (For example: a Function Error..) to an internal table named tbl_logs. For display and review purposes, I populate these logs to a form list box control. No dramas with this method.

However, for general Application Usage Logging, (For example: user exported a report..) I am writing logs to a text file which requires opening them manually.

I am wanting to fully automate this process preferably with the capacity to display the logs in a list box control on a userform.

Below is the best code I have found that will open a text file for display. When I tested it, I get an error on line 9 which I assume is to do with the variable fName.

Public Sub populate_TxtLog()
'
Dim F, fName

fName = CurrentProject.path & "\logs.txt"
'Const LogFileName As String = "C:\FOLDERNAME\TEXTFILE.LOG"
Dim FileNum As Integer, tLine As String
    FileNum = FreeFile ' next file number
    Open fName For Input Access Read Shared As #F ' open the file for reading
    Do While Not EOF(FileNum)
        Line Input #FileNum, tLine ' read a line from the text file
    Loop ' until the last line is read
    Close #FileNum ' close the file
    MsgBox tLine, vbInformation, "Last log information:"
End Sub

Open in new window


Has anyone got any ideas or previous examples of how one can go about this?

Many thanks,
Lai
Laila JacksonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robert ShermanOwnerCommented:
This isn't a complete solution to your question, but it will get you past the error message you are receiving and you might be able to pick up the rest from that point...

The error on line 9 is caused by use of the wrong variable.  It looks like you declared variable F on line 3, but then on line 7 you declare FileNum.  I'm assuming these two variables were meant to do the same thing, which is to hold the file number.  
On line 8 you assign FreeFile to the variable FileNum.  But, then on line 9 you are say "As #F" where I think you meant to say "As #FileNum".   Far as I can tell, the variable F isn't needed so you can remove that declaration from line 3.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Laila JacksonAuthor Commented:
Got it working from your advice.

Changed it to:

Public Sub populate_TxtLog()
'declare variables ->
Dim FileNum As Integer, tLine As String, fName As String

'Assigning the full file name and path to fName (FileName) variable ->
fName = CurrentProject.path & "\logs.txt"

    FileNum = FreeFile ' next file number
    Open fName For Input Access Read Shared As #FileNum ' open the file for reading
    Do While Not EOF(FileNum)
        Line Input #FileNum, tLine ' read a line from the text file
    Loop ' until the last line is read
    Close #FileNum ' close the file
    MsgBox tLine, vbInformation, "Last log information:"
End Sub

Open in new window


Getting it to populate a userform listbox is above my skill level so I will need more assistance in obtaining a solution for this post.

Thanks, Lai
0
Robert ShermanOwnerCommented:
Also, how long are these log files going to be?  Is your intent to show the actual contents of the log file in a textbox?  Are you hoping to show the entire file or just some smaller subset of it?  You will run into problems if these logs become very large.  I believe there is a limit of 64K lines/rows and if you're setting the contents via the RowSource property, that has a limit of 2048 characters.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Laila JacksonAuthor Commented:
A single user session writes around 30 log entries / transaction. Users conduct approximately 400 transactions per day / 5 days per week.

My intent is to display the entire content of the log file.

From your previous feedback, it would seem that I could end up in a lot of trouble later on unless I revert to table logs instead but that would defeat my logic behind text logs which was to avoid bloated log tables?
0
Robert ShermanOwnerCommented:
If you're going to keep writing to one log file, that file will become bloated just as tables will.  Do you need to keep this log data indefinitely, or can you purge it once it gets past a certain age?  You'll probably want to find some way to keep the amount of log data that you'll be dealing with at once down to a manageable size.  

Maybe you want to base the log filename on the date, so that you each file contains one day's worth of transactions?  Based on your numbers, we're already talking about 12,000 log entries per day per user.  

What is the purpose of displaying this log data on a form?   It doesn't sound very useful when you consider how much text you're talking about here.
0
Laila JacksonAuthor Commented:
I agree entirely with your comments.

At present, there are no guidelines on how much data we should retain so I have the luxury of a clean sheet of paper.

I believe a billing cycle of 4 weeks should suffice to start with and we can adjust accordingly.  Even still, we are talking about a lot of data.

Just checked the records for today and found that a lot of the logged data is really pure "informational" stuff only.  For example, one user ran 7 reports for 151 customers.  I can attack this task myself to reduce the logging footprint over time as well.

We need to have the ability to audit records where users have confirmed acceptance of certain business rules (i.e. allowing discounts and part bill payments etc..)  but this information is sitting inside the system tables anyway so its really just a case of mucking in with SQL as required.

To this end, thanks heaps for your help. My text file problem is resolved so I can live with that result and build on our dialogue to this point.

Thanks, Lai
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.