Query to check the if the data is already available.

[kbsudhir]

I am using the below code to update the data from the excel to access.

[VBA]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
[/VBA]

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.

[VBA] 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
[/VBA]

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.



Thanks
Sudhir

[OBP]

I don't think that your If statement will do what you want because you are not "And" ing each name with the date check. ie
If Trim(Range("A" & cnt).Value) = "Joan" and rs.Fields("Date") = CDate(nme)

[kbsudhir]

My Mistake I made it confusing.

[VBA]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"[/VBA]

Normally I have to update only 5members data rest five done by my partner. The above line of code will update only my 5members.

What I think we could is pull the data with condition of team members's name & date if rs.length is > 0 then do not update.

But I do not know how to make such complicated query from excel to access.

This query could help me.

Thanks
Sudhir