Reading a text file into Access

I have a text file that has more than one record per line. I know that there are 129 characters per record, but I am not sure how many records there are per line.

I want to read the txt file and put each 129 characters into a record in access.

heres a function that will do it for you...

VB Code:

Public Function returnRecordsArray(inFile As String, recLen As Long) As Variant
Dim strIn As String
Dim fileNo As Long
Dim recs() As Variant
Dim count As Integer
 
    fileNo = FreeFile
    Open inFile For Binary As fileNo
    strIn = Input(LOF(fileNo), fileNo)
    strIn = Replace(strIn, vbCrLf, "")
    count = Len(strIn) \ recLen
    ReDim recs(count)
    For count = 0 To (Len(strIn) \ recLen)
        recs(count) = Left(strIn, recLen)
        strIn = Mid(strIn, recLen + 1)
    Next count
    returnRecordsArray = recs
End Function

now just set a variant array to the function e.g...

VB Code:

Dim myRecs() As Variant
myRecs = returnRecordsArray("c:\temp\test.txt", 5)

of course you could change the function to return whatever you wanted