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

[shawnntjr]

Hi all,

I am in need of serious help. I have a problem that's been bugging me for quite some time 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 can I ensure that the file has been imported in the exact same order as it was in the text file? Any help would be greatly appreciated!

[skywriter]

This is cross posted on Mr. Excel, but for some reason I am not allowed to post a link.

Post denied. New posts are limited by number of URLs it may contain and checked if it doesn't contain forbidden words.

[shawnntjr]

This is cross posted on Mr. Excel, but for some reason I am not allowed to post a link.

Post denied. New posts are limited by number of URLs it may contain and checked if it doesn't contain forbidden words.

Ok, I just read up on this cross posting thing, and I apologise if I have caused any inconvenience. I am just on a really tight deadline and this is the final piece to solve my project. I wish to seek a wider range of advice from more experts hence the cross post. Once again, I'm sorry.

[skywriter]

They're not, you posted on both boards. It's called cross posting and if you do it you should make it known a post a link to the other post.

[jonh]

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.

[shawnntjr]

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.

JONH. YOU SAVED ME. I CAN'T BELIEVE ONE SINGLE TABLE PROPERTY OPTION CAUSED ME SO MUCH FRUSTRATION.
MANY THANKS TO YOU MAN! :)