In your Access app:

1. Open the Table in design view
2. Add an index to the table to prevent duplicate records.

This is done using the "Indexed" property at the bottom of the screen. It
would also help if you had a primary key defined in the table.

P


"Pete T" wrote in message
om...
I thought I had worked out a solution to my coding and correcting a
Looping problems, but it has turned out to be a much greater problem.

In our office we have multiple staff members needing to Update and/or
Add data to an Access Database from Excel, that is because none of
these staff members have direct access to the database, only through
Excel.

I can access the database, and the proper table and Add data to the
Table from the Excel Macro. However, it will add everytime. So if
staff add rows later, and push the macro they end up added the same
rows over and over. I want the rows which match existing data in the
access table to be updated if necessary, and add if not found.

Coding is right for the connection to the Database and recordsets.

I attempted to use the following :

Dim rs As Recordset
Dim ColE As Varient
.......
For R = 4 to 300
ColE = Worksheets("Contacts").Cells(R,5).Value
If ColE = "" Then
Exit For
End If
Do While Not rs.EOF
If Worksheets("Contacts").Cells(R,5).Value = rs.Fields("SSN") Then
rs.Edit
rs.Fields("From") = Worksheets("Contacts").Cells(R,3).Value
rs.Fields("Type") = Worksheets("Contacts").Cells(R,4).Value
' etc.etc. (total of 12 fields of data
rs.Update
Else
rs.MoveNext
rs.AddNew
rs.Fields("From") = Worksheets("Contacts").Cells(R,3).Value
rs.Fields("Type") = Worksheets("Contacts").Cells(R,4).Vales
' etc. etc.
rs. Update
End If
Loop
Next
rs.Close
Set rs = Nothing
dbs.Close
Set dbs = Nothing

I can see the problem in that I am looping through the Excel
spreadsheet okay, but failing to loop through the Database Table???
No sure how to do this?

Thanks for any help.