[RESOLVED] Need Help Converting Text File to Recordset

[crmfghtr]

Hello Everybody, I have a little experience with ADO Recordsets. I made a Database Program with VB6, Using Access 97 as the Database.The Program works Just Fine. I am a Teacher, I get Updated Information from What I believe was Created In a DOS Batch File that When Activated Creates a Text File Containing Student Information. I want to Be Able to Convert the Text File to a ADO Recordset then Save the Information into my Program's Database. The Information I want to retrieve is as Follows:

StudentNo:
LastName:
FirstName:
Facility:
Class:
Teacher:
Grade:

I'm not sure where to begin, Any help would be appreaciated.
Thanks
Ready to be a Student

[Hack]

How is the text file layed out? What type of format is it in?

[crmfghtr]

I opened the file in MSAccess 97, it said the fields were fixed Width. One other thing I left out, the text characters of the file on each line I only need the first 70. The rest of the Line I do not need, since all of the information is contained in the first 70.
The Text file contains anywhere between 5800 to 6200 lines (Records)
I though of creating a Recordset that would search the File for only the Information that matches my Records that way I would not have to load the entire file.

Maybe like this:

Dim cn as New ADODB.Connection
Dim rs as New ADODB.Recordset
Dim sSqlstr as String,cnstr as string,conn as string
Dim MyLines As String,MyFields as String
Dim i as Long
 
UpdateCon 'Read from the Registry
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnstr = Conn ' DBPath String
 
'Select the Students
 
sSqlstr = "Select StudentNo,LastName,DateAsgn,Facility,ClassNm,Teacher FROM Students WHERE Title = 'Student' "
 
cn.Open cnstr
rs.CursorLocation = adUseClient
rs.Open sSqlstr, cn, , , adCmdText
 
  If Not (rs.bof = True) and Not (rs.eof = true) then
      rs.MoveFirst
 
     While Not rs.eof
         for i = 1 to UBound(MyLines)
      Open App.path & "\" & "Update.txt" for Input as #1
       'Here is where I am Stuck
 
       Close # 1
 
 
wend
 
end if

[crmfghtr]

Well after a few hours here is what I came up with it works. If anyone has a better solution I'm all Ears... thanks ...

Sub OpenText()
Dim Student as string,LName as string,FName as String,HClass as string,MyGrade as String,DateAsgn as String,Fac as String,MyLine as String
Dim MyRow as Long, recs as Long
 
recs = 0
MyRow = 1
 
'Read the Database Connection String
        UpdateCon
        Set cn = New ADODB.Connection
        Set rs = New ADODB.Recordset
        cnstr = Conn
 
        'Select the Records
        strSql = "SELECT StudentNo,Last,First,Facility,MyClass,DateAsgn,Grade FROM Students  WHERE Status = 'Assigned' ORDER BY StudentNo" ' 
        cn.Open cnstr
        rs.CursorLocation = adUseClient
        rs.Open strSql, cn, , , adCmdText
 
        If Not (rs.BOF = True) Or Not (rs.EOF = True) Then
            rs.MoveFirst
            
            While Not rs.EOF
            Student = rs.Fields("StudentNo").value
            ' Open the selected file.
            Open Filename For Input As #1
            ' Change the mouse pointer to an hourglass.
                Screen.MousePointer = 11
                Do While Not EOF(1)   ' Check for end of file.
                
                Line Input #1, MyLine  ' Read line of data.
                'Read the Data Needed
                MyLine = Left(MyLine, 80)
 
                If Left(MyLine, 6) = StudentNo Then
                Student = StudentNo
                'Break up the Data
                LName = Mid(MyLine, 8, 23)
                RTrim (LName) 'Trim any spaces
                
                FName = Mid(MyLine, 31, 17)
                RTrim (FName) 'Trim any spaces
                
                HClass = Mid(MyLine, 48, 10)
                RTrim (MyClass) 'Trim any spaces
                
                Fac = Mid(MyLine, 59, 2)
                
                Grade= Mid(MyLine, 65, 3)
                
                DateAsgn = Mid(MyLine, 70, 10)
 
                'Set the Coloumn Names
                FrmDown.VSFlexGrid2.TextMatrix(0, 1) = "Student #"
                FrmDown.VSFlexGrid2.TextMatrix(0, 2) = "Last Name"
                FrmDown.VSFlexGrid2.TextMatrix(0, 3) = "First Name"
                FrmDown.VSFlexGrid2.TextMatrix(0, 4) = "Facility"
                FrmDown.VSFlexGrid2.TextMatrix(0, 5) = "Class"
                FrmDown.VSFlexGrid2.TextMatrix(0, 6) = "Grade"
                FrmDown.VSFlexGrid2.TextMatrix(0, 7) = "Assigned"
 
                FrmDown.VSFlexGrid2.TextMatrix(MyRow, 1) = Student
               FrmDown.VSFlexGrid2.TextMatrix(MyRow, 1) = LName
                 FrmDown.VSFlexGrid2.TextMatrix(MyRow, 1) = FName
               FrmDown.VSFlexGrid2.TextMatrix(MyRow, 1) = Facility
              FrmDown.VSFlexGrid2.TextMatrix(MyRow, 1) = HClass
               FrmDown.VSFlexGrid2.TextMatrix(MyRow, 1) = Grade
              FrmDown.VSFlexGrid2.TextMatrix(MyRow, 1) = DateAsgn
         
         recs = recs + 1
    MyRow = MyRow + 1
    FrmDown.VSFlexGrid2.Rows = FrmDown.VSFlexGrid2.Rows + 1
 
    End If
       
    Loop
 
Close #1
 
rs.MoveNext
Wend
 
Screen.MousePointer = 0
FrmDown.VSFlexGrid2.Rows = FrmDown.VSFlexGrid2.Rows - 1 'Delete the Extra Row
 
End If
 
 
Set rs = Nothing
Set cn = Nothing
 
end Sub

[SpeedyDog]

You just want to put the data file into an Access Database?

Access will input Fixed Length and Delimited Files automatically.
Just goto File > Get External Data > Import.

From there, you want to change your FileType to like TEXT or what ever.
Select the file.. and you can select the lengths of the fields correct?

Sometimes using Access' features can be much easier than building them yourself.

-SpeedyDog