How to write to a text file in Access

[dave8]

I want to be able to create a text file in Access and write a message when an macro query is completed.
Is there a way to do something like this?

For example:

Query1
Do something
....
end query2
"Query1 has completed" < ----- This statement gets written to Text file

Query2
Do somehting
....
end query2
"Query2 has completed" <-------- This statement gets appended to Text file
Query3...
....

 

[Micron]

This procedure gets called if there is a fault during a prior stage of a process. Edit to suit your process:

Public Function WriteToUpdateLog(strMsg As String)
Dim fs, f
Dim fpath As String
Const ForReading = 1, ForWriting = 2, ForAppending = 8

If apPath = "" Then apPath = getApPath '<<use your own tests. This test ensures the file is written to the same folder as the db file via getApPath procedure.
fpath = apPath & "\QueryUpdateLog.log"'<< the name of the text file to write to
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(fpath, 8, True)
f.Write strMsg
f.Close 'closes file & saves changes

Set fs = Nothing
Set f = Nothing
End Function

for creating text file from scratch, see CreateTextFile instead of OpenTextFile

CreateTextFile method (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

vs

OpenTextFile method (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

 

[dave8]

The process is I will have a Function to create a new text file (Public Function CreateLogFile ...) every time my program runs. During the execution of my program, if any error occurs it calls the
Function WriteToUpdateLog. But, I'm not sure how to use the Const - Set f = fs.OpenTextFile(fpath, 8, True) . I can keep this '8' here??

 

[Micron]

My code has a boo boo of sorts - I declared the constants but didn't use any of them, which was an oversight. Could just have easily been
Set f = fs.OpenTextFile(fpath, ForAppending, True)

or forget the constants declaration and use 8

HTH

 

[dave8]

So the way the Function operates is that I can pass a string such as ("Query1 aborted") to strMsg. Is that correct??

Asking basic question because I'm just learning here...

 

[Micron]

N.P. Yes.
I used it in an update routine where several queries were run. If the query failed to run without error, the function was called and a string message was passed to it. It looks like:

step 4:
strMsg = "a message about a query to be run goes here"
db.Execute "nameOfQueryHere", dbFailOnError <<this would raise a run time error; an error handler would then call the function and pass the value of strMsg
do more stuff

step5:
strMsg = "a new message"
and so on

 

[dave8]

In your code you have, "If apPath = "" Then apPath = getApPath... " . Do I have to declare apPath and getApPath in Dim??

 

[Micron]

No, read the comments beside that line. It is only an example of a test - it does not apply to you. You need to determine what your own tests must be. In your case, it might be that after you run the query (or do some other action) you test if Error number property is zero? e.g. after attempting an operation,
If Err.Number = 0 Then <<all is OK

You will need to use On Error statements judiciously - likely On Error Resume Next - to ensure the error number is tested. However, you don't want this in places where ignoring the error would just cause remaining code to run as if nothing happened.

If you put something together as a start and post it, it would give us something to work with and provide more focused guidance. Right now, what you've revealed is rather scarce and leaves a lot of room for interpretation.

 

[dave8]

Here is a bit of code that I have. This program archives some records. The code below is in the Global module. This Function is called in the AutoExec.

Function archiveProcess() as Integer
   On Error GoTo ERR_HANDLER

10:
   DoCmd.SetWarnings False
   DoCmd.OpenQuery "1a-delete-tblARData"      <----  Each of the queries contain SQL statements that goes something like this, "Delete * from tblCurrentAccts where xxxx...  "

   Do I add this here on each label?
   If Err.Number > 0 GoTo ERR_Handler

20:
  DoCmd.OpenQuery "1b-delete-tblCurrentAssignedDate"

30:
  Docmd.OpenQuery "1c-update-tblArchivedTheseAccounts"

...
100:
   ...
   Exit Function

ERR_Handler:

In the event of an error, I want to call the WriteToUpdateLog here(?). In the log file I want to know which query ended in error.
How to program so that if 1a-delete-tblARData abnormally quits, I can add something like this in the log file, "1a-delete-tblARData aborted mmddyy & time".
Likewise, if 1b-delete-tblCurrentAssignedDate quits, I can add this to the log file, "1b-delete-tblCurrentAssignedDate aborted mmddyy & time".

 

[Micron]

In your first post you said you wanted to log the execution of every query. In your last post you said only if the query raises an error. I went with the latter and wrote a simplified version. However, I made the assumption that if there is an error the whole process is terminated. If that is not the case, strMsg2 would need to be declared at the module level and concatenated in the error handler using strMsg values plus a line wrap. Then Resume Next would go to the next line after the one that raised the error and carry on with the next query. I don't know what you're doing with the integer value returned by your function so I left it in.
NOTE: you need to create your log file in the same folder as the db first. See if this helps:

Function archiveProcess() as Integer
  Dim strMsg As String

  On Error GoTo ERR_HANDLER
  DoCmd.SetWarnings False

10:
  strMsg = "1a-delete-tblARData failed at " & Now()
  DoCmd.OpenQuery "1a-delete-tblARData"
20:
  strMsg = "1b-delete-tblCurrentAssignedDate failed at " & Now()
  DoCmd.OpenQuery "1b-delete-tblCurrentAssignedDate"
30:
  etc.

exitHere:
SetWarnings True
Exit Function

errHandler:
Dim fs, f
Dim fpath As String

Set fs = CreateObject("Scripting.FileSystemObject")
fpath = CurrentProject & "\NameOfYourTextFile.log" '<<can use .txt if preferred
Set f = fs.OpenTextFile(fpath, 8, True)
f.Write strMsg
f.Close
Set fs = Nothing
Set f = Nothing
Resume exitHere

End Function

A couple of observations:
- your posted code suggests you forgot to turn warnings back on - not good.
- best to not use special characters or spaces in object names. Suggest you adopt a proper naming convention going forward. See

General: Commonly used naming conventions

MS Access Naming Conventions

_________________________________________________________ Naming Conventions. This document is aimed at the user who is unfamiliar with any naming convention. It is based upon personal experience and the interaction I have had with others. It is not a hard and fast rule. Why use a...

www.access-programmers.co.uk

What not to use in names

Microsoft Access tips: Problem names and reserved words in Access

List of problem field names and reserved words in Microsoft Access and JET

allenbrowne.com

- if you have a lot of queries in this code, you could Dim strQryName and write
20:
strQryName = "1b-delete-tblCurrentAssignedDate"
strMsg = strQryName & " failed at " & Now()
DoCmd.OpenQuery strQryName

That might look worse, but you could copy and paste lines 2 and 3 umpteen times and only have to write the query name once for each call.
EDIT - forgot to say that you probably want a notification message from the error handler if there is a failure, otherwise there is nothing to indicate that the log was written to, or that there was any other sort of failure.