read from Text file using VB and Insert into SQL table until eof text file reach

Dear All,

my text file like following,

a1, b1, c1, d1, e1
a2, b2, c2, d2, e2
a3, b3, c3, d3, e3
a4, b4, c4, d4, e4
.................

and im going to insert this values into SQL Tables with columns, [a, b, c, d,
e]

how im going to do so? i use below src code to do but i can not get the next
line and next line of text file until it finished insert

Private Sub mot_Click()
'Shell ("C:\Program Files\Microsoft Office\Office11\MSACCESS.EXE c:\fisherdb.
mdb /x ImportOpsNilai")

'frmPicCd.Show
Dim db_connect
' Path = App.Path & "\ERP consumable code.xls"

Dim objFSO
Dim objFile, objTextStream
Dim strContent
Const ForReading = 1
Dim FileName, IEPath, strC1 As String
FileName = "U:\bureport\buops\RepNilai.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.GetFile(FileName)

Set objTextStream = objFile.OpenAsTextStream(ForReading,
TristateUseDefault)

If objFSO.FileExists(FileName) Then

Set objFile = objFSO.OpenTextFile(FileName, ForReading)

'While Not objFile.EOF
strC1 = objFile.ReadLine
Dim word
ReDim word(2)
word = Split(strC1, ",")
' Dim t1, t2, t3 As String
' MsgBox RTrim(LTrim(word(44)))
' t1 = Replace(word(0), """", "")
' t2 = Replace(word(1), """", "")
Dim sql
Dim rs As New ADODB.Recordset
sqldb.Execute ("delete from OPS_Nilai")
rs.Open ("Select * from OPS_Nilai"), sqldb, adOpenDynamic,
adLockOptimistic
With rs
.AddNew
!Field1 = RTrim(LTrim(word(0)))
!Field2 = RTrim(LTrim(word(1)))
!Field3 = RTrim(LTrim(word(2)))
!Field4 = RTrim(LTrim(word(3)))
!Field5 = RTrim(LTrim(word(4)))
!Field6 = RTrim(LTrim(word(5)))
!Field7 = RTrim(LTrim(word(6)))
!Field8 = RTrim(LTrim(word(7)))
!Field9 = RTrim(LTrim(word(8)))
!Field10 = RTrim(LTrim(word(9)))
!Field11 = RTrim(LTrim(word(10)))
!Field12 = RTrim(LTrim(word(11)))
!Field13 = RTrim(LTrim(word(12)))
!Field14 = RTrim(LTrim(word(13)))
!Field15 = RTrim(LTrim(word(14)))
!Field16 = RTrim(LTrim(word(15)))
!Field17 = RTrim(LTrim(word(16)))
!Field18 = RTrim(LTrim(word(17)))
!Field19 = RTrim(LTrim(word(18)))
!Field20 = RTrim(LTrim(word(19)))

!Field21 = RTrim(LTrim(word(20)))
!Field22 = RTrim(LTrim(word(21)))
!Field23 = RTrim(LTrim(word(22)))
!Field24 = RTrim(LTrim(word(23)))
!Field25 = RTrim(LTrim(word(24)))
!Field26 = RTrim(LTrim(word(25)))
!Field27 = RTrim(LTrim(word(26)))
!Field28 = RTrim(LTrim(word(27)))
!Field29 = RTrim(LTrim(word(28)))
!Field30 = RTrim(LTrim(word(29)))
!Field31 = RTrim(LTrim(word(30)))
!Field32 = RTrim(LTrim(word(31)))
!Field33 = RTrim(LTrim(word(32)))
!Field34 = RTrim(LTrim(word(33)))
!Field35 = RTrim(LTrim(word(34)))
!Field36 = RTrim(LTrim(word(35)))
!Field37 = RTrim(LTrim(word(36)))
!Field38 = RTrim(LTrim(word(37)))
!Field39 = RTrim(LTrim(word(38)))
!Field40 = RTrim(LTrim(word(39)))

!Field41 = RTrim(LTrim(word(40)))
!Field42 = RTrim(LTrim(word(41)))
!Field43 = RTrim(LTrim(word(42)))
!Field44 = RTrim(LTrim(word(43)))
!Field45 = RTrim(LTrim(word(44)))
.Update

End With
'objFile.SkipLine
'objFile.MoveNext
' Loop
End If

objTextStream.Close
MsgBox "Update completed!!!", vbOKOnly + vbInformation, "Fisher Nilai"
End Sub

kindly advice

thank you

--
Message posted via VBMonster.com
http://www.vbmonster.com/Uwe/Forums...vbasic/200707/1