Reading a text file into Access

[ajharmon]

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.

[chrisa_uk]

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

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...

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

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