Hi t,
It depends on how tight an audit trail you need. Would something along
the lines of this air code be acceptable? (The WriteToFile() function is
at the end of this message).
Dim strSpecName As String
Dim strFileSpec As String
....
strSpecName = "blah blah"
strFileSpec = "D:\Folder\File.txt"
....
On Error GoTo Fail:
DoCmd.TransferText acImport strFileSpec, strSpecName, ...
WriteToFile "Imported " & strFileSpec & _
" using " & strSpecName & vbCRLF, _
"D:\Folder\LogFile.txt", False
Fail:
On Error Resume Next
Otherwise, consider using a schema.ini file instead of an import spec.
The advantages are (1) schema.ini is visible to the file system so can
be logged or recorded by whatever software you're using for the audit
trail, and (2) the textfile name needs to appear as a section header in
schema.ini.
Function WriteToFile(Var As Variant, _
FileSpec As String, _
Optional Overwrite As Long = True) _
As Long
'Writes Var to a textfile as a string.
'Returns 0 if successful, an errorcode if not.
'Overwrite argument controls what happens
'if the target file already exists:
' -1 or True (default): overwrite it.
' 0 or False: append to it
' Any other value: abort.
Dim lngFN As Long
On Error GoTo Err_WriteToFile
lngFN = FreeFile()
'Change Output in next line to Append to
'append to existing file instead of overwriting
Select Case Overwrite
Case True
Open FileSpec For Output As #lngFN
Case False
Open FileSpec For Append As #lngFN
Case Else
If Len(Dir(FileSpec)) > 0 Then
Err.Raise 58 'File already exists
Else
Open FileSpec For Output As #lngFN
End If
End Select
Print #lngFN, CStr(Nz(Var, ""));
Close #lngFN
WriteToFile = 0
Exit Function
Err_WriteToFile:
WriteToFile = Err.Number
End Function
