EZ Import

mmcdonal · May 19th, 2006, 08:08 AM

Okay, is there a "Row" field?

What are the fields in the table, and what does your code look like?

mmcdonal

tready · May 19th, 2006, 08:58 AM

This is my actual code now after several edits to try and make it work.:

Code:

    i = 0
    Do Until i = UBound(MyArray)
        objrs.AddNew
        objrs("Field" & i) = Round(Left(MyArray(i), 23), 2)
        If Me.txtStatusBox = "" Then
            Me.txtStatusBox = " Adding Item....." & Round(Left(MyArray(i), 23), 2) & vbCrLf
        Else
            Me.txtStatusBox = Me.txtStatusBox & " Adding Item....." & Round(Left(MyArray(i), 23), 2) & vbCrLf
        End If
        objrs.Update
        i = i + 1
    Loop

This is causing a new row for each value, not creating just one row.

tready · May 19th, 2006, 09:39 AM

Ok, here is the complete code. I fixed the problem.

Code:

Private Sub Command0_Click()
Set fso = CreateObject("Scripting.FileSystemObject")

'Check for existence of file first
'If the file exists, open a connection to the back end, and a recordset

If fso.FileExists("C:\Program Files\ttermpro\toola") Then
    Set objStream = fso.OpenTextFile("C:\Program Files\ttermpro\toola", 1, False, 0)
    Set objConn = Application.CurrentProject.Connection
    Set objrs = CreateObject("ADODB.Recordset")

    objrs.Open "SELECT * FROM TOOL_DATA", objConn, 3, 3

'Start reading the file, break it into lines, and the lines into fields

    Do While Not objStream.AtEndOfStream
    sLine = objStream.ReadLine
    MyArray = Split(sLine, "#")

    If Me.txtStatusBox <> "" Then
        Me.txtStatusBox = ""
    End If


    i = 1
    objrs.AddNew
    Do Until i = UBound(MyArray)
        objrs("Field" & i) = Round(Left(MyArray(i), 23), 2)
        i = i + 1
    Loop
    objrs.Update
    Me.txtStatusBox = " Import Complete....." & vbCrLf & " " & UBound(MyArray) & " record(s) were added!"
 
    Loop
End If
End Sub

For some odd reason I had the

Code:

objrs.addnew

inside the loop

Code:

Do Until i = UBound(MyArray)

which is why it was adding a new record every time. Thnx for your help on that.

My last questions is how do I get it to only read the FIRST OR LAST line of the text file? Is that possible?

mmcdonal · May 19th, 2006, 01:48 PM

If you want it to read the first line of the text file, then just tell it to readline once, without a loop around it. I will have to think about the best way to do the last line. I think you can loop through without posting the values to the table inside the loop, and then just post them after the loop finishes running, since the array will have the last line still in it after it gets out of the loop.

HTH

mmcdonal

tready · May 19th, 2006, 01:52 PM

Yeah, I couldn't figure out how to grab just the last line either. So I used a another sub to recreate the log files after every successful import of records. Thnx for your help.

mmcdonal · May 19th, 2006, 01:55 PM

Oh, I think just posting to the table AFTER the loop instead of in it will do the same thing.

So is this all working now?

mmcdonal

tready · May 22nd, 2006, 09:56 AM

Quote:

quote:Originally posted by mmcdonal
Oh, I think just posting to the table AFTER the loop instead of in it will do the same thing.

So is this all working now?

mmcdonal

Hey,

yeah, everything is working fine. Just a few minor tweaks and it whould be finished. I'm getting rdy to post other thread with a problem I'm having with text getting highlighted. I'm not sure why its doing it.

tready · May 22nd, 2006, 01:55 PM

I have found a new problem. After much testing I found that I'm not importing all the data from the text files. I played with the code but no such luck. Here is the code:

Code:

Set fso = CreateObject("Scripting.FileSystemObject")


If fso.FileExists("C:\Program Files\ttermpro\toola") Then
    Set objStream = fso.OpenTextFile("C:\Program Files\ttermpro\toola", 1, False, 0)
    Set objConn = Application.CurrentProject.Connection
    Set objrs = CreateObject("ADODB.Recordset")

    objrs.Open "SELECT * FROM TOOLA_RAW", objConn, 3, 3


    Do While Not objStream.AtEndOfStream
    sLine = objStream.ReadLine
    MyArray = Split(sLine, "#")

    'If Me.txtStatusBox <> "" Then
        'Me.txtStatusBox = ""
    'End If

    intToolA = UBound(MyArray)
    'i = 0
    a = 1
    objrs.AddNew
    objrs("RECORDID") = [Forms]![frmInput].[intRecordID]
    Do Until i = UBound(MyArray)
        objrs("Field" & a) = Left(MyArray(a), 23)
        'i = i + 1
        a = a + 1

    Loop

    Loop
    If intToolA <> 0 Then
        strMsg = " Import Complete....." & vbCrLf & " " & UBound(MyArray) & " record(s) were added from TOOL A!" & vbCrLf
        Me.txtStatusBox = strMsg
        objrs.Update
        objrs.Close
    Else
        objrs.Cancel
        objrs.Close
    End If
Else
    objrs.Cancel
End If

If fso.FileExists("C:\Program Files\ttermpro\toolb") Then
    Set objStream = fso.OpenTextFile("C:\Program Files\ttermpro\toolb", 1, False, 0)

    objrs.Open "SELECT * FROM TOOLB_RAW", objConn, 3, 3

    Do While Not objStream.AtEndOfStream
    sLine = objStream.ReadLine
    MyArray = Split(sLine, "#")

    'i = 0
    b = 1
    intToolB = UBound(MyArray)
    objrs.AddNew
    objrs("RECORDID") = [Forms]![frmInput].[intRecordID]
    Do Until b = UBound(MyArray)


        objrs("Field" & b) = Left(MyArray(b), 23)
        'i = i + 1
        b = b + 1

    Loop

    Loop
    If intToolB <> 0 Then
        strMsg = strMsg & " Import Complete....." & vbCrLf & " " & UBound(MyArray) & " record(s) were added from TOOL B!" & vbCrLf
        Me.txtStatusBox = strMsg
        objrs.Update
        objrs.Close
    Else
        objrs.Cancel
        objrs.Close
    End If
Else
    objrs.Cancel
End If

If fso.FileExists("C:\Program Files\ttermpro\toolc") Then
    Set objStream = fso.OpenTextFile("C:\Program Files\ttermpro\toolc", 1, False, 0)

    objrs.Open "SELECT * FROM TOOLC_RAW", objConn, 3, 3

    Do While Not objStream.AtEndOfStream
    sLine = objStream.ReadLine
    MyArray = Split(sLine, "#")

    'i = 0
    c = 1
    intToolC = UBound(MyArray)
    objrs.AddNew
    objrs("RECORDID") = [Forms]![frmInput].[intRecordID]
    Do Until c = UBound(MyArray)

        objrs("Field" & c) = Left(MyArray(c), 23)

       'i = i + 1
        c = c + 1

    Loop

    Loop
    If intToolC <> 0 Then
        strMsg = strMsg & " Import Complete....." & vbCrLf & " " & UBound(MyArray) & " record(s) were added from TOOL C!" & vbCrLf
        Me.txtStatusBox = strMsg
        objrs.Update
        objrs.Close
    Else
        objrs.Cancel
        objrs.Close
    End If
Else
    objrs.Cancel
End If

intTotal = (intToolA + intToolB + intToolC)

If intTotal <> 0 Then

    CreateNewLogFiles
    Me.txtStatusBox = Me.txtStatusBox & " New Log Files Created." & vbCrLf
    Me.txtStatusBox = Me.txtStatusBox & " "
    Me.txtStatusBox.Locked = True
Else
    Me.txtStatusBox = " Log Files Are Empty......" & vbCrLf
    Me.txtStatusBox = Me.txtStatusBox & " " & vbCrLf
    Me.txtStatusBox.Locked = True

End If

And i tried:

Code:

    i = 0
    c = 1
    intToolC = UBound(MyArray)
    objrs.AddNew
    objrs("RECORDID") = [Forms]![frmInput].[intRecordID]
    Do Until c = (UBound(MyArray) + 1)

        objrs("Field" & c) = Left(MyArray(i), 23)

       i = i + 1
        c = c + 1

But that didn't work either. This would be starting at 0 of the array. It give the error cannot set ToolC_RAW.Field1 = zero-length string. Anyone think they can help??

mmcdonal · May 22nd, 2006, 02:28 PM

What line is throwing the error?

mmcdonal