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...

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.

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?

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?

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

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

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...

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?

This article has been dead for over six months. Start a new discussion instead.