Avoid creating duplicates of records that are already exist in BD

[SULI88]

I have inherited an excel spreadsheet that users use to record customer quotes that gets inserted to an access DB table. Multiple quotes generate separate records, one each (max 4 per form)

If a user adds extra quotes to a spreadsheet with quotes that have been processed previously & runs the routine it creates duplicates of the records in the DB

I'm looking for a method to only add the quotes that have been newly entered and ignore the ones that are in the database already.


Below is an extract of the relevant code:

'''The piece that determines the number of quotes on the form'''

If .Range("E16").Value <> "" Then
No_Quotes = 5
ElseIf .Range("E15").Value <> "" Then
No_Quotes = 4
ElseIf .Range("E14").Value <> "" Then
No_Quotes = 3
ElseIf .Range("E13").Value <> "" Then
No_Quotes = 2
Else
No_Quotes = 1
End If

'''Array load'''

ReDim Arr(1 To 23, 1 To No_Quotes + 1) As Variant
Arr(1, 1) = "Quote Number"
Arr(2, 1) = "Line Number"


Arr(3, 1) = "Quantity Requested"
Arr(4, 1) = "Customer"
Arr(5, 1) = "Material"
Arr(6, 1) = "Quantity"
Arr(7, 1) = "Steps"
Arr(8, 1) = "Labour"
Arr(9, 1) = "Waste"

Set MyRange = .Range("C11")

For j = 1 To No_Quotes
Arr(1, j + 1) = MyRange.Offset(j, 0).Value
Arr(2, j + 1) = MyRange.Offset(j, 1).Value
Arr(3, j + 1) = MyRange.Offset(j, 2).Value
Arr(4, j + 1) = MyRange.Offset(j, 3).Value
Arr(5, j + 1) = MyRange.Offset(j, 5).Value
Arr(6, j + 1) = MyRange.Offset(j, 6).Value
Arr(7, j + 1) = MyRange.Offset(j, 7).Value
Arr(8, j + 1) = MyRange.Offset(j, 10).Value
Arr(9, j + 1) = MyRange.Offset(j, 11).Value
Next j

'''DB interaction'''

Set cn = New ADODB.Connection
cn.Open sTarget

Set rs = New ADODB.Recordset
rs.Open "QuotesTBL", cn, adOpenKeyset, adLockOptimistic, adCmdTable

With rs
For j = 1 To No_Quotes
.AddNew
For i = 1 To 9
.Fields(Arr(i, 1)) = Arr(i, j + 1)
Next i
.Update
Next j
End With

Many thanks for any advice!

[ranman256]

before adding run a query to check if the recordset already has that record,
if the rst has no records, then its safe to add new.

or
key the table so its impossible to add duplicates.

[Micron]

Perhaps could also use DLookup("yourField',"yourTable") based on Arr(1, j + 1) = MyRange.Offset(j, 0).Value
or rs.Find or rs.Seek against a table recordset of the current data. I guess it depends on the number of records involved. DLookup simpler, but cannot recall if I've ever used it against 100k's of records or what the practical limit might be.

[SULI88]

Thanks for the replies. I'm just learning to find my way around the excel-access interactions so could you kindly detail how would you implement this in my scenario? The unique info of the quotes are the "Quote Number"s and DB holds up to 5 records for each so the "Line Number" field could be 1 to 5 with the same "Quote Number". The additional quotes entered on the spreadsheet get the next free "Line Number" in the sequence. The number of records in the table is around 50k.

[Micron]

Don't know whom you're addressing, but if me, then did you research DLookup and how to include criteria in it?
These 2 statements are contradictory:

The unique info of the quotes are the "Quote Number"s

field could be 1 to 5 with the same "Quote Number"

if you mean there are up to 5 records. You cannot say the quote number is unique if there are 2 or more records with that value, and I don't think you are saying there are 5 line numbers in one field of one record.

Since I can't see the data that's in those sheet cell references it's not clear to me. It seems DLookup would need to check for a record where quote 77 AND line 3 exist. I'm guessing you'd do that first in your 1 To 9 loop before bothering to set the rs fields values. I'm also guessing your DLookup would be the array references that come next. If that returns 0, the record isn't already in the table, in which case you'd then do .Fields(Arr(i, 1)) = Arr(i, j + 1)

BUT
there's all that rigamarole or you could just append and then run a find duplicates query to remove the dupes. You could try the query wizard for that against a copy of the table that has duplicate records. First check how many you think should be deleted. If you get a warning about deleting 25K records but you expected 5 then you know your query isn't right.

If you're going to post more than a few lines of code, please use code tags (use # button on posting toolbar) and make use of proper indentation. It just makes it so much easier to read. Thanks in advance for that.

[Welshgasman]

Now crossposted to https://www.access-programmers.co.uk...-in-db.322667/ today.

[SULI88]

Hi Micron,


Sorry, I'm new to this.


The DB holds up to 5 records per "Quote Number" in following fields format:
Quote Number Line Number
Q1 1
Q1 2




Essentially these are quotes for the same client for different quantites, etc.


When the user adds the data for 3rd version of the quote on the source spreadsheets later and re-runs the code the result is:


Quote Number Line Number
Q1 1
Q1 1
Q1 2
Q1 2
Q1 3


Based on arnelgp suggestion on access-programmers (link posted by welshgasman, sorry again...) I tried this:
..
..
rs.Open "QuotesTBL", cn, adOpenKeyset, adLockOptimistic, adCmdTable

With rs
For j = 1 To No_Quotes
.Find "[Quote Number] = '" & arr(1, 2) & "'"
.Find "[Line Number] = '" & Arr(2, 2) & "'"
If .EOF Then
.AddNew
For i = 1 To 9
.Fields(arr(i, 1)) = arr(i, j + 1)
Next i
.Update
End If
Next j
End With


It works on the 1st record in the DB but skips the rest as this method doesn't increase the value of the "Line Number" from 1.
Any ideas?
-Thanks-

[Micron]

Nice how you addressed the cross-posting thing but between that and the lack of code tags in your last post (after I pointed that out) I'm going to bow out and let arnelgp help you. Knowing him as I do I'm sure he'll come through for you but IMO easier to just remove the duplicates as mentioned.

You might want to read this to see what the issue is with cross posting and how to do it:

https://www.excelguru.ca/content.php?184