Append String to Table

[ethoemmes]

I have used the import class module from
http://www.mvps.org/access/modules/mdl0057.htm. This reads my text file
into a string called myString. I am trying to append this string into
table tblTEMPVendors. The string is tab delimited. I am trying to use
the code below to do this but I need some help - How do I loop through
the string if this was a recordset I know i would use do while not
rs.eof but I can't get the syntax for a string variable.

Can anyone help?
Private Sub cmdImportVendors_Click()
Dim rsVendors As New ADODB.Recordset
Dim strAppendSQL As String
Dim strArray() As String

'Call function to return file to import
GetFName
'Call read file procedure
ReadAFile (strFName)

rsVendors.Open "tblTEMPVendors", CurrentProject.Connection,
adOpenDynamic
'Do While '???
strArray = Split(myString, vbTab)
If Not strArray(1) = "Vendor" Then 'exclude column heading
rsVendors.AddNew
rsVendors.Fields(0).Value = strArray(1)
End If
'Loop
rsVendors.Close
End Sub

[pietlinden]

what does your text file look like?

what's the strAppendSQL stuff for? Why not just use the Add method of
the recordset object?

Well, how about this - explain what you have (VERY briefly), and the
basic logic of what you want. After that, it's implementation, and
that's easy.

If the only difference between one file and another is whether it has
field names, then create two import specs (one for each), and then peek
at the first line of the file and stuff it into a variable. If it's
text, then you'd use something like TransferText with a canned import
spec that stated that the first line is column names, otherwise you'd
use TransferText with a different import spec that stated that there
were no column names.

Then you don't need Split, arrays, or any of the rest of that stuff.
So you open a file maybe twice. So what? If you have lots of files,
you can have your code iterate over all the files in a directory and
process them. Much faster than doing it by hand, even if your code
does do some slightly redundant things.

[ethoemmes]

what does your text file look like?

The text is exported from our accounting system but is not very tidy,
therefor I used the import class i mentioned to tidy it up a bit.

what's the strAppendSQL stuff for? Why not just use the Add method of
the recordset object?

I should have removed this, no longer used

I have now got the jist of the code I will be using posted below for
anyone who is interested.

Thanks for your help.

Public Sub ReadAFile(strFileName)
Dim myTextFile As New cImportClass
Dim intError As Integer
Dim strVendor As String
Dim rsVendors As New ADODB.Recordset
Dim myString As String
Dim strarray() As String

myTextFile.FileName = strFileName
myTextFile.NoBlankLines = True
myTextFile.CountOnlyNonBlankLines = False
myTextFile.StripLeadingSpaces = True
myTextFile.StripTrailingSpaces = True
myTextFile.StripNulls = False
myTextFile.OnlyAlphaNumericCharacters = False

rsVendors.Open "tblTEMPVendors", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic

intError = myTextFile.cfOpenFile
If intError = 0 Then
While Not myTextFile.EndOfFile ' Watch for the
end of the file
myTextFile.csGetALine ' Tells the class
to go to a new line
myString = myTextFile.Text ' set your string
= to the current string of the class
If myString <> "" Then
strarray = Split(myString, vbTab)
If Not Mid(strarray(0), 3, 1) = "." Then 'exclude date
If Not strarray(1) = "Vendor" Then 'exclude column
heading
rsVendors.AddNew
rsVendors.Fields(0).Value = strarray(1)
rsVendors.Fields(1).Value = strarray(2)
rsVendors.Fields(2).Value = strarray(3)
rsVendors.Fields(3).Value = strarray(4)
rsVendors.Fields(4).Value = strarray(5)
rsVendors.Fields(5).Value = strarray(6)
rsVendors.Fields(6).Value = strarray(7)
rsVendors.Fields(7).Value = strarray(8)
rsVendors.Fields(8).Value = strarray(9)
rsVendors.Fields(9).Value = strarray(10)
rsVendors.Fields(10).Value = strarray(11)
rsVendors.Fields(11).Value = strarray(12)
rsVendors.Fields(12).Value = strarray(13)
rsVendors.Fields(13).Value = strarray(14)
rsVendors.Fields(14).Value = strarray(15)
If strarray(16) = "x" Then
rsVendors.Fields(15).Value = -1
End If
If strarray(17) = "x" Then
rsVendors.Fields(16).Value = -1
End If
If strarray(18) = "x" Then
rsVendors.Fields(17).Value = -1
End If
If strarray(19) = "x" Then
rsVendors.Fields(18).Value = -1
End If
If strarray(20) = "x" Then
rsVendors.Fields(19).Value = -1
End If

End If
End If
End If
Wend
Else
' handle the error here!
MsgBox Error(intError)
End If

myTextFile.cfCloseFile
Set myTextFile = Nothing
rsVendors.Close
End Sub

[ethoemmes]

what does your text file look like?

The text is exported from our accounting system but is not very tidy,
therefor I used the import class i mentioned to tidy it up a bit.

what's the strAppendSQL stuff for? Why not just use the Add method of
the recordset object?

I should have removed this, no longer used

I have now got the jist of the code I will be using posted below for
anyone who is interested.

Thanks for your help.

Public Sub ReadAFile(strFileName)
Dim myTextFile As New cImportClass
Dim intError As Integer
Dim strVendor As String
Dim rsVendors As New ADODB.Recordset
Dim myString As String
Dim strarray() As String

myTextFile.FileName = strFileName
myTextFile.NoBlankLines = True
myTextFile.CountOnlyNonBlankLines = False
myTextFile.StripLeadingSpaces = True
myTextFile.StripTrailingSpaces = True
myTextFile.StripNulls = False
myTextFile.OnlyAlphaNumericCharacters = False

rsVendors.Open "tblTEMPVendors", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic

intError = myTextFile.cfOpenFile
If intError = 0 Then
While Not myTextFile.EndOfFile ' Watch for the
end of the file
myTextFile.csGetALine ' Tells the class
to go to a new line
myString = myTextFile.Text ' set your string
= to the current string of the class
If myString <> "" Then
strarray = Split(myString, vbTab)
If Not Mid(strarray(0), 3, 1) = "." Then 'exclude date
If Not strarray(1) = "Vendor" Then 'exclude column
heading
rsVendors.AddNew
rsVendors.Fields(0).Value = strarray(1)
rsVendors.Fields(1).Value = strarray(2)
rsVendors.Fields(2).Value = strarray(3)
rsVendors.Fields(3).Value = strarray(4)
rsVendors.Fields(4).Value = strarray(5)
rsVendors.Fields(5).Value = strarray(6)
rsVendors.Fields(6).Value = strarray(7)
rsVendors.Fields(7).Value = strarray(8)
rsVendors.Fields(8).Value = strarray(9)
rsVendors.Fields(9).Value = strarray(10)
rsVendors.Fields(10).Value = strarray(11)
rsVendors.Fields(11).Value = strarray(12)
rsVendors.Fields(12).Value = strarray(13)
rsVendors.Fields(13).Value = strarray(14)
rsVendors.Fields(14).Value = strarray(15)
If strarray(16) = "x" Then
rsVendors.Fields(15).Value = -1
End If
If strarray(17) = "x" Then
rsVendors.Fields(16).Value = -1
End If
If strarray(18) = "x" Then
rsVendors.Fields(17).Value = -1
End If
If strarray(19) = "x" Then
rsVendors.Fields(18).Value = -1
End If
If strarray(20) = "x" Then
rsVendors.Fields(19).Value = -1
End If

End If
End If
End If
Wend
Else
' handle the error here!
MsgBox Error(intError)
End If

myTextFile.cfCloseFile
Set myTextFile = Nothing
rsVendors.Close
End Sub