How to import text file line by line in the exact order?

[shawnntjr]

Hi all,

I have been trying to do this for a month now. I am required to import a text file (ranging from 500000 to 3000000 lines) into an access table. I managed code a function that will import the file, however, the file is not in the right order. There are certain rows that are jumbled up. E.g. Row 123 of the text file is input into record number 321.

I need the file to be imported in that specific order as I have other functions that will be working with the data to extract certain information.

These are the current codes that I am using:

Private Sub Command3_Click()    Dim fs As Object
    Dim filename As String
    Dim tsIn As Object
    Dim sFileIn As String
    Dim Text As String
    Dim sqlcre As String
    Dim sqlsta As String
    
    If IsNull(Me.txtImport) Then
        MsgBox "You forgot to select a file."
    Else
        sFileIn = Me.txtImport
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set tsIn = fs.OpenTextFile(sFileIn, 1)
    
        While Not tsIn.AtEndOfStream
            tmps = tsIn.ReadLine
            sqlsta = "INSERT INTO Table1(Field1) VALUES ('" & Replace(tmps, "'", "''") & "');"
            DoCmd.SetWarnings False
            DoCmd.RunSQL sqlsta
        Wend
        DoCmd.SetWarnings True
        MsgBox "The file has been imported."
    End If
End Sub

How do I make sure that the text file is imported in the right order?

 

[skywriter]

Cross posted:
How to import text file line by line in the exact order?

 

[shawnntjr]

My apologies for not stating my reason for cross posting. I'm on a tight deadline and wish to seek more advice on this matter. Forgive me as I'm still new to these forums.

Thanks skywriter for providing the link to my other post!

 

[skywriter]

Check this out.
Excelguru Help Site - A message to forum cross posters

My apologies for not stating my reason for cross posting. I'm on a tight deadline and wish to seek more advice on this matter. Forgive me as I'm still new to these forums.

Thanks skywriter for providing the link to my other post!

 

[shawnntjr]

Hi all, a user from vbaexpress managed to help me solve my problem. If anyone is facing the same thing, you can try this method.

The records will be added in the correct order, they're just being sorted by the table index.
Remove the index or add an autonumber field and sort that.