CODEWITHSUNDEEP
vbams-access
AccessNewbie
AccessNewbie

Reputation: 1

How to read txt files and import them into access table

I am new to access vba. I am trying to find a way to import a txt file into my access db. I read a lot of articles and forums, and tested a lot of codes, but none of it worked. I was able to narrow down to the code- listed below. The problem that I have is that it runs and runs, and then I have close my db and start again. No error, just endless run. My txt file is not that big and it should not do this unless I have a bug in my code and I do not know how to fix it. Please help.

Dim FileName As String
Dim MostRecentFile As String
Dim MostRecentDate As Date
Dim FileSpec As String
Dim filepath As String
Dim txtStream As Object
Dim strImportRecord As String

filepath = "\\C:\"
 FileSpec = "*.txt*"

FileName = Dir(filepath & FileSpec)

If FileName <> "" Then
    MostRecentFile = FileName
    MostRecentDate = FileDateTime(filepath & FileName)
    Do While FileName <> ""
        If FileDateTime(filepath & FileName) > MostRecentDate Then
             MostRecentFile = FileName
             MostRecentDate = FileDateTime(filepath & FileName)
        End If

       Loop
End If

Set txtStream = CreateObject("Scripting.FileSystemObject").OpenTextFile(MostRecentFile)

Do While Not (txtStream.atendofstream)
        strImportRecord = txtStream.ReadAll
  Loop

DoCmd.TransferText acImportFixed, "myspecification", "mytable", "strImportRecord", False

Upvotes: 0

Views: 1257

Answers (2)

Eric Moon
Eric Moon

Reputation: 131

Try this:

Sub ImportMostRecentTextFile()

Dim FileName As String
Dim MostRecentFile As String
Dim MostRecentDate As Date
Dim FileSpec As String
Dim filepath As String
Dim txtStream As Object
Dim strImportRecord As String

filepath = "C:\Users\moone2\Documents\"
 FileSpec = "*.txt*"

FileName = Dir(filepath & FileSpec)

If FileName <> "" Then
    MostRecentFile = FileName
    MostRecentDate = FileDateTime(filepath & FileName)
    Do While FileName <> ""
        If FileDateTime(filepath & FileName) > MostRecentDate Then
             MostRecentFile = FileName
             MostRecentDate = FileDateTime(filepath & FileName)
        End If
        FileName = Dir()
       Loop
End If

'I don't think you need to load the text....
'------------
'Set txtStream = CreateObject("Scripting.FileSystemObject").OpenTextFile(MostRecentFile)'
'
'Do While Not (txtStream.atendofstream)
'    strImportRecord = txtStream.ReadAll
'Loop
'
'Set txtStream = Nothing
'
'Debug.Print strImportRecord

'DoCmd.TransferText acImportFixed, "myspecification", "mytable", strImportRecord,
'---------------

'Just load from the most recent file, like this:
DoCmd.TransferText acImportFixed, "myspecification", "myTable", MostRecentFile, True

End Sub

Upvotes: 1

Andre
Andre

Reputation: 27644

Your loop to find the most recent file:

Do While FileName <> ""

never ends, because you never assign anything new to FileName in the loop.

You are missing a FileName = Dir() before Loop.

This won't be the only problem, all the things Mike wrote in his comments are correct.

Upvotes: 0

Related Questions