Greetings Charles,

       Based on the details you gave, I worked with the following code and it worked fine. Have a look at it and try it out. Please let us know how it goes. Gud luck.

xxxxxxSTARTxxxxxxx

Option Explicit
'Declare Variables

Dim cnAccess As ADODB.Connection
Dim data As Variant
Dim strSQL As String
Dim nFileNum As Integer, sNextLine As String, lLineCount As Long

Private Sub Command1_Click()
   
    ' Get a free file number
nFileNum = FreeFile

' Open a text file
Open "" & App.Path & "\commaExportedData.txt" For Input As nFileNum
lLineCount = 0

'Clean Table2 before inserting records
    cnAccess.Execute ("DELETE FROM Table2")
    cnAccess.Close
   
'Read the contents of the file
Do While Not EOF(nFileNum)
   Line Input #nFileNum, sNextLine
   lLineCount = lLineCount + 1

  
   'Split the records from text file
   data = Split(sNextLine, ";")
   cnAccess.Open
  
   'Insert Values into Table2
   strSQL = "INSERT INTO Table2(ID,value1,value2,Grandtotal) VALUES('" & data(0) & "','" & data(1) & "','" & data(2) & "','" & data(3) & "')"
   cnAccess.Execute strSQL
   cnAccess.Close
  
Loop

' Close the file
Close nFileNum

    MsgBox "Export Complete,No. of Records:" & lLineCount

End Sub

Private Sub Form_Load()
       
    Set cnAccess = New ADODB.Connection
    cnAccess.Open _
        "Provider = 'Microsoft.Jet.OLEDB.4.0';" _
        & "Data Source = " & App.Path & "\TestDb.mdb;"

End Sub

xxxxxxENDxxxxxxx


On Mon, Jul 26, 2010 at 6:50 PM, kris njoroge <krsnjo@gmail.com> wrote:
Cant remember very well but think goes like this.
You first make a recordset which will hold the input
RecordsetName![FieldName] = ControlContents
write a sub UpdateRecord
here u will call the recordset.update method
This will have called another sub that will actually populate the recordset WritetoRecordset using the syntax
RecordsetName![FieldName] = ControlContents (if the text controls then .txt.

sub UpdateRecord
WriteToRecordset
recordset.update
End sub

Sub WriteToRecord
RecordsetName![FieldName] = F_Name.txt
RecordsetName![FieldName] = l_Name.txt
RecordsetName![FieldName] = phone.txt
End sub

This sub will be called from the save button once the form has been populated.

The connection object has to be instantiated and available as that is what the recordset is going to use. The cn obj holds the parameters that will be used to wirte back to the db.

Hope that helps


On Mon, Jul 26, 2010 at 5:31 PM, Rad! <conradakunga@gmail.com> wrote:
Seems pretty straightforward.

Can't remember with 100% certainty but i think what you want to do is open the file and loop through each line.

For each line execute the Split command, passing as a parameter the semicolon. This should give you an array of 4 strings

You can then write a simple loop to iterate over the array and insert the values into the database.

On Mon, Jul 26, 2010 at 4:05 PM, Charles Mwangi <mwangicharles@gmail.com> wrote:
Hi VB gurus out there,

Kindly provide code for loading the following test script into an access db using visual basic 6

TT10194C2ASD;6700;254722090909;CharlesMwangi;
TT10194C5PST;9034;254722123123;MikeMaingi;

the text has 4 fields separated by semi colons.

The first 3 fields are of fixed length values.

thanks.

_______________________________________________
Skunkworks mailing list
Skunkworks@lists.my.co.ke
http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks
------------
Skunkworks Server donations spreadsheet
http://spreadsheets.google.com/ccc?key=0AopdHkqSqKL-dHlQVTMxU1VBdU1BSWJxdy1fbjAwOUE&hl=en
------------
Skunkworks Rules
http://my.co.ke/phpbb/viewtopic.php?f=24&t=94
------------
Other services @ http://my.co.ke


_______________________________________________
Skunkworks mailing list
Skunkworks@lists.my.co.ke
http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks
------------
Skunkworks Server donations spreadsheet
http://spreadsheets.google.com/ccc?key=0AopdHkqSqKL-dHlQVTMxU1VBdU1BSWJxdy1fbjAwOUE&hl=en
------------
Skunkworks Rules
http://my.co.ke/phpbb/viewtopic.php?f=24&t=94
------------
Other services @ http://my.co.ke



--
"Humility grows a little more each time i genuinely listen to the experience of another human being". Brahma Kumaris

_______________________________________________
Skunkworks mailing list
Skunkworks@lists.my.co.ke
http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks
------------
Skunkworks Server donations spreadsheet
http://spreadsheets.google.com/ccc?key=0AopdHkqSqKL-dHlQVTMxU1VBdU1BSWJxdy1fbjAwOUE&hl=en
------------
Skunkworks Rules
http://my.co.ke/phpbb/viewtopic.php?f=24&t=94
------------
Other services @ http://my.co.ke