how to update the text file into access file

[vbgirl]

Public cnName As Connection
Public rsId As Recordset

Private Sub cmdImport_Click()
Dim a(50) As String
Dim ProductID(50) As String
Dim i As Integer
i = 0

          cdbOpenFile.Filter = "Text Files (*.txt)|*.txt| "
          
          'Specify default file name to open
          cdbOpenFile.FileName = ""
          ' Specify default filter to *.txt
          cdbOpenFile.FilterIndex = 1
          
          ' Display the Open dialog box, and
          ' save the selected file in the
          ' variable FileSelect
            cdbOpenFile.ShowOpen



Open cdbOpenFile.FileName For Input As #1
Do Until EOF(1)

Line Input #1, a(i)
ProductID(i) = UCase(Left(a(i), InStr(a(i), ",") - 1))
a(i) = Mid(a(i), Len(ProductID(i)) + 1, Len(a(i)))
'MsgBox ProductID(i), vbOKOnly, "Text File"
i = i + 1
Loop
Close #1

Dim b As Integer
b = i

Path = App.Path
Set cnName = New ADODB.Connection
With cnName
    .CursorLocation = adUseClient
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & "\name.mdb;Persist Security Info=False;Jet OLEDB"
    .Open
End With

Set rsId = New ADODB.Recordset
rsId.Open "ID", cnName, adOpenKeyset, adLockOptimistic

'With rsId
'.MoveFirst
'While Not .EOF
'MsgBox .Fields(0), vbOKOnly, "Access file"
'.MoveNext
'Wend
'End With
With rsId
.MoveFirst
Dim d As Integer
d = 0

For i = 0 To b
Do While Not .EOF
If ProductID(d) = rsId.Fields(0) Then
rsId.Update
End If
rsId.MoveNext
Loop
rsId.MoveFirst
d = d + 1
Next
End With

'when data done all will show a message box "DONE"
'MsgBox "Done", vbOKOnly + vbInformation, "Complete"
End Sub

can anyone help... me.. very urgent...

[selvaganapathy]

Hi, I cannot understand completely, but i guess your problem

For i = 0 To b - 1
   Do While Not .EOF
     If ProductID(i) = rsId.Fields("ID") Then 
         'Update Your Field Here
         'Ex rsId.Field ("Field Name") = a ( i )
          rsId.Update
     End If
     rsId.MoveNext
   Loop
   rsId.MoveFirst
Next

or Simply Use UPDATE SQL

Please Specify Further informations
Your TextFile Format and Database Table Fields to be Updated.

[vbgirl]

1234567,08072008
8767768,08072008
9988776,08072008
7891234,09072008
4567891,10072008
5213789,11072008
7854123,11072008
4561237,14072008
7879145,15072008
5218510,16072008

this is my text file data.. i would like to take this the read color (if have new data will update to access file)

can you help me change my code?

[selvaganapathy]

Only New data, Not Existing?. I thought the red indicated is ID and Second one is SubField. Also i thought If ID exists then You should update the DB. I dont know what happens when ProductID Not Exists?

[vbgirl]

yes only new data will update to access
then how the code write?

[vbgirl]

i only want the red color will update to access file

[selvaganapathy]

You have to update or Add only one Field (ID field).
> Then set the primary key for the Field.
> Now try to add the Field
> IF the field already Exists then It will cause error and Skip this Error
> and Update The next ID

Your Code

For i = 0 To b - 1
     On Error Resume Next
     rsId.AddNew
     rsId(0) = ProductID(i)
     rsId.Update
Next

Dont forget to set the primary key

[vbgirl]

You have to update or Add only one Field (ID field).
> Then set the primary key for the Field.
> Now try to add the Field
> IF the field already Exists then It will cause error and Skip this Error
> and Update The next ID

Your Code

For i = 0 To b - 1
     On Error Resume Next
     rsId.AddNew
     rsId(0) = ProductID(i)
     rsId.Update
Next

Dont forget to set the primary key

ok.. thanks..
i done it...

[vbgirl]

You have to update or Add only one Field (ID field).
> Then set the primary key for the Field.
> Now try to add the Field
> IF the field already Exists then It will cause error and Skip this Error
> and Update The next ID

Your Code

For i = 0 To b - 1
     On Error Resume Next
     rsId.AddNew
     rsId(0) = ProductID(i)
     rsId.Update
Next

Dont forget to set the primary key

but this only udate one 1 data only?