How to write to a text file in Access

dave8

Active Member
Joined
Jul 8, 2007
Messages
272
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...
....
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,037
Office Version
  1. 365
Platform
  1. Windows
This procedure gets called if there is a fault during a prior stage of a process. Edit to suit your process:
VBA Code:
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

vs
 

dave8

Active Member
Joined
Jul 8, 2007
Messages
272
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

Well-known Member
Joined
Jun 3, 2015
Messages
2,037
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jul 8, 2007
Messages
272

ADVERTISEMENT

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

Well-known Member
Joined
Jun 3, 2015
Messages
2,037
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jul 8, 2007
Messages
272

ADVERTISEMENT

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

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,037
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jul 8, 2007
Messages
272
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.

VBA Code:
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

Well-known Member
Joined
Jun 3, 2015
Messages
2,037
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

What not to use in names

- 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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,063
Messages
5,622,470
Members
415,897
Latest member
Barry18180

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top