kbsudhir
07-07-2008, 05:00 AM
I am using the below code to update the data from the excel to access.
Set rs = New ADODB.Recordset
rs.Open "select * from DSAccuracyTable", cn, adOpenStatic, adLockOptimistic
Do Until Range("A" & cnt) = "Total"
rs.AddNew
If Trim(Range("A" & cnt).Value) = "Joan" Or Trim(Range("A" & cnt).Value) = "Mel" Or Trim(Range("A" & cnt).Value) = "Mnd" Or Trim(Range("A" & cnt).Value) = "FranV" Or Trim(Range("A" & cnt).Value) = "MayO" Then
rs.Fields("CS_Name") = Range("A" & cnt).Value
rs.Fields("TotalSkus") = Range("B" & cnt).Value
rs.Fields("DescChanges") = Range("C" & cnt).Value
rs.Fields("ProdGrpChanges") = Range("D" & cnt).Value
rs.Fields("CPUChanges") = Range("E" & cnt).Value
rs.Fields("MediaCodeChanges") = Range("F" & cnt).Value
rs.Fields("TotalSkusChanged") = Range("G" & cnt).Value
rs.Fields("Date") = CDate(nme)
rs.Fields("User") = VBA.Environ("UserName")
rs.Update
End If
cnt = cnt + 1
Loop
rs.AddNew
rs.Update
rs.Close
But the problem is, the table will get updated in the access every time click the button i.e if I click more than once then sam data will get updated again and again which is wrong.
Hence I want to check if data of a particular name & date is already updated in the table it should not get updated instead it should go to the next row of the excel
Ex.
If Trim(Range("A" & cnt).Value) = "Joan" Or Trim(Range("A" & cnt).Value) = "Mel" Or Trim(Range("A" & cnt).Value) = "Mnd" Or Trim(Range("A" & cnt).Value) = "FranV" Or Trim(Range("A" & cnt).Value) = "MayO" Then
In the above code I only updating the data for the five associates,
now if Joan's name is already updated for the date 07/01/2008 Then it should go and check the next name ex, Mel.
Date is taken from the name of the file.
Any help regarding this will be helpful.
:doh: :doh:
Thanks
Sudhir
Set rs = New ADODB.Recordset
rs.Open "select * from DSAccuracyTable", cn, adOpenStatic, adLockOptimistic
Do Until Range("A" & cnt) = "Total"
rs.AddNew
If Trim(Range("A" & cnt).Value) = "Joan" Or Trim(Range("A" & cnt).Value) = "Mel" Or Trim(Range("A" & cnt).Value) = "Mnd" Or Trim(Range("A" & cnt).Value) = "FranV" Or Trim(Range("A" & cnt).Value) = "MayO" Then
rs.Fields("CS_Name") = Range("A" & cnt).Value
rs.Fields("TotalSkus") = Range("B" & cnt).Value
rs.Fields("DescChanges") = Range("C" & cnt).Value
rs.Fields("ProdGrpChanges") = Range("D" & cnt).Value
rs.Fields("CPUChanges") = Range("E" & cnt).Value
rs.Fields("MediaCodeChanges") = Range("F" & cnt).Value
rs.Fields("TotalSkusChanged") = Range("G" & cnt).Value
rs.Fields("Date") = CDate(nme)
rs.Fields("User") = VBA.Environ("UserName")
rs.Update
End If
cnt = cnt + 1
Loop
rs.AddNew
rs.Update
rs.Close
But the problem is, the table will get updated in the access every time click the button i.e if I click more than once then sam data will get updated again and again which is wrong.
Hence I want to check if data of a particular name & date is already updated in the table it should not get updated instead it should go to the next row of the excel
Ex.
If Trim(Range("A" & cnt).Value) = "Joan" Or Trim(Range("A" & cnt).Value) = "Mel" Or Trim(Range("A" & cnt).Value) = "Mnd" Or Trim(Range("A" & cnt).Value) = "FranV" Or Trim(Range("A" & cnt).Value) = "MayO" Then
In the above code I only updating the data for the five associates,
now if Joan's name is already updated for the date 07/01/2008 Then it should go and check the next name ex, Mel.
Date is taken from the name of the file.
Any help regarding this will be helpful.
:doh: :doh:
Thanks
Sudhir