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

 

[psulions83]

Can a column be added to the log file to add a record count?

 

[stumac]

Yeah you could do that - the best way would involve the comment Xenou said above as then you could also identify the records that were imported - you would need to have an extra field that links back to the import table though so not sure if that's an option. The other way is to count the records in the table prior to import, count them post import and then work out the difference. you should also consider renaming your files post import if you are going this way as if not there is a danger they could be imported twice!?!

Dim rs As Recordset, rs2 As Recordset
Dim SrcFilePath As String, HaveFile As Boolean, ImprtSpec As String, TblNm As String, FileNm As String
Dim LoadState As String
[COLOR=#0000cd]Dim NumRecs As Long[/COLOR]
Set rs = CurrentDb.OpenRecordset("Tbl_Data_Load")
Set rs2 = CurrentDb.OpenRecordset("TblLogFile")
rs2.AddNew
rs2!Timestamp = Now
rs2!Status = "File Load procedure Started"
rs2.Update
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
        
        If Dir(SrcFilePath) = "" Then
            Debug.Print "No " & rs!FileName & " Exists"
            LoadState = "No " & rs!FileName & " Exists"
        Else
            LoadState = rs!FileName & " File Now Loaded" [COLOR=#008000]'Not sure if this is right place to update and say it is loaded given its loaded AFTER this
[/COLOR]           [COLOR=#0000cd]NumRecs = DCount("*", TblNm)[/COLOR]
            DoCmd.TransferText acImportDelim, ImprtSpec, TblNm, Application.CurrentProject.Path & "" & FileNm, True
            [COLOR=#0000cd]NumRecs = DCount("*", TblNm) - NumRecs[/COLOR]
        End If
        
        With rs2
            .AddNew
            !Timestamp = Now
            !FileName = rs!FileName
            !Status = LoadState
            [COLOR=#0000cd]![/COLOR][COLOR=#ff0000]RecordsImported [/COLOR][COLOR=#0000cd]= NumRecs [/COLOR][COLOR=#008000]' The red field name should be the field you are going to import the number into.[/COLOR]
            .Update
        End With
        rs.MoveNext
        
    Wend
    
    rs2.AddNew
    rs2!Timestamp = Now
    rs2!Status = "File Load procedure Complete"
    rs2.Update
End If
Set rs = Nothing
Set rs2 = Nothing

The bits in blue are the bits I think you would need to change - I haven't tested any of it though.

 

[psulions83]

Stumac,

This works great!

Thank you very much for the help!