Query to write to Txt File

[psulions83]

Hi,

There are lots of topics on writing to txt files and userforms but I am not sure if I follow how to apply there. I have the below query that I would love to have a log file within Access table or have it output a text file to a folder. The debug print immediate window out put is sort of what I am looking for. Is this possible?

Public Function loopTable()


Dim strSQL As String
Dim rs As DAO.Recordset
Dim SrcFilePath As String, HaveFile As Boolean




Dim ImprtSpec, TblNm, FileNm As String

Debug.Print "File Load procedure Started at " & Now()
strSQL = "SELECT * FROM Tbl_Data_Load" 
Set rs = CurrentDb.OpenRecordset(strSQL)

If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
While (Not rs.EOF)

ImprtSpec = rs.Fields("ImportSpecNm")

TblNm = rs.Fields("TableName")
FileNm = rs.Fields("FileName")

SrcFilePath = Application.CurrentProject.Path & "" & FileNm
Debug.Print rs.Fields("FileName")




If Dir(SrcFilePath) = "" Then


Debug.Print "No " & rs.Fields("FileName") & " Exists"

Else


Debug.Print rs.Fields("FileName") & " File Now Loaded"

DoCmd.TransferText acImportDelim, ImprtSpec, TblNm, Application.CurrentProject.Path & "" & FileNm, True



End If
rs.MoveNext
Wend
End If
rs.Close
Set rs = Nothing

Debug.Print "File Loaded - " & Now()
End Function

 

[ranman256]

you dont want a recordset, you want a query. Make IT get the data.
and you want EXPORT the data (not import)
and make sure you do have the ImprtSpec

DoCmd.TransferText acExportDelim, "ImprtSpec", "qsQuery", Application.CurrentProject.Path & "" & FileNm, True

 

[psulions83]

Thank you for your response. I don't think i was clear enough with my initial post.

The query i have works as intended. It picks up the files I want and loads them....I want to record what it actually loaded so there is a record of it.

ie File 1, 2, 3 & 4 were loaded I want a log that says file name and time it was loaded.

File 1 Loaded - 11122018 1322
File 2 Loaded - 11122018 1322
File 3 Loaded - 11122018 1323
File 4 Loaded - 11122018 1323
File 5 - Does not exist

The files are specified in a table with those import specs. Does that make sense?

 

[stumac]

a log file within Access table or have it output a text file to a folder

Which way would you rather - to a table or to a CSV? Do you want it to overwrite the previous records or append it to a table/new text file?

 

[psulions83]

Hi Stumac,

I think writing it to a table within access and appending new records each time would give a good log of what was done.


Thanks

 

[stumac]

So I would create a Table (in the example below I have named TblLogFile) with the fields - ID (Autonumber), TimeStamp, Filename, Status

I have made changes to your code but was just for my own readability, the main bits I have added are in blue:

Dim rs As Recordset, [COLOR=#0000ff]rs2 As Recordset[/COLOR]
Dim SrcFilePath As String, HaveFile As Boolean, ImprtSpec As String, TblNm As String, FileNm As String
[COLOR=#0000ff]Dim LoadState As String[/COLOR]
Set rs = CurrentDb.OpenRecordset("Tbl_Data_Load")
[COLOR=#0000cd]Set rs2 = CurrentDb.OpenRecordset("TblLogFile")[/COLOR]
[COLOR=#0000cd]rs2.AddNew
rs2!Timestamp = Now
rs2!Status = "File Load procedure Started"
rs2.Update[/COLOR]
Debug.Print "File Load procedure Started at " & Now() & vbLf
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
While (Not rs.EOF)
ImprtSpec = Nz(rs!ImportSpecNm)
TblNm = rs!TableName
FileNm = rs!FileName
SrcFilePath = Application.CurrentProject.Path & "" & FileNm
Debug.Print rs!FileName
If Dir(SrcFilePath) = "" Then
Debug.Print "No " & rs!FileName & " Exists"
[COLOR=#0000ff]LoadState = "No " & rs!FileName & " Exists"[/COLOR]
Else
Debug.Print rs!FileName & " File Now Loaded"
[COLOR=#0000ff]LoadState = rs!FileName & " File Now Loaded" [/COLOR][COLOR=#008000]'Not sure if this is right place to update and say it is loaded given its loaded AFTER this[/COLOR]
DoCmd.TransferText acImportDelim, ImprtSpec, TblNm, Application.CurrentProject.Path & "" & FileNm, True
End If
[COLOR=#0000ff]With rs2
.AddNew
!Timestamp = Now
!FileName = rs!FileName
!Status = LoadState
.Update
End With[/COLOR]
rs.MoveNext
Wend
End If
Set rs = Nothing
[COLOR=#0000ff]Set rs2 = Nothing[/COLOR]

 

[xenou]

You could also have a field in your table with the import date/time and the import source (so the table would be self-documenting and might help you sort out the data in case of problems).

 

[psulions83]

This works awesome.....the only thing that is not coming back in the log table is when the procedure finishes. I tried a couple things but this did not work. Is the last debug.print supposed to put that in?

 

[stumac]

Must have missed that, if you just add the record before the 'Wend':

[COLOR=#0000cd]rs2.AddNew
rs2!Timestamp = Now
rs2!Status = "File Load procedure Complete"
rs2.Update[/COLOR]

 

[psulions83]

good idea. Thank you!