appending data table only


starstarstarstarstar Excellent

Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » Appending data to a table only where it won't create a duplicate

Appending data to a table only where it won't create a duplicate

resolvedResolved · High Priority · Version 2007

Tue 20 Mar 2012, 17:22 replyReply Delegate Timothy said...

Timothy has attended:
Access Advanced course

Appending data to a table only where it won't create a duplicate

Hi there,
I am copyng data from one table A to another B when a change has been made to the data in A. A is accessed via a form.
The change is made via an append query. Criteria limits the data in A but you cannot set criteria to append to table B if a field in B is different to that in A but not if it is the same.

I have attempted so far to create a new table with the data I want (this is meant to be temporary) in VBA.
The next step is to compare these values to those in Table B and where they are different append. Where they are the same to do nothing. I have used the following code but have made changes where appropriate to make the names fit my database. Consider shoes as B and import as the table created from the SQL code.

Public Function UpdateTableSeekNoStatus() As String
Dim dbs As Database, rstShoes As Recordset, rstImport As Recordset
Dim NumRec As Long

' Return reference to current database.
Set dbs = CurrentDb

' Create a dynaset-type Recordset object based on ShoeImport table.
Set rstImport = dbs.OpenRecordset("ShoeImport", dbOpenDynaset)

' Create a table-type Recordset object based on Shoes table.
Set rstShoes = dbs.OpenRecordset("Shoe")
' Set index for SEEK
rstShoes.Index = "PrimaryKey"


Do Until rstImport.EOF = True

rstShoes.Seek "=", rstImport!StockNumber, rstImport!Width

If rstShoes.NoMatch Then
If rstImport!TRNCOD = "U" Then
'Add a new Record

rstShoes!StockNumber = rstImport!StockNumber
rstShoes!ShoeName = rstImport!ShoeName
rstShoes!Color = rstImport!Color
rstShoes!Width = rstImport!Width
rstShoes!VendorNumber = rstImport!VendorNumber
rstShoes!SKUNumber = rstImport!SKUNumber
rstShoes!Gender = rstImport!Gender
rstShoes!Brand = rstImport!Brand
rstShoes!TRNCOD = rstImport!TRNCOD
rstShoes!CurrentDate = rstImport!CurrentDate
rstShoes!CurrentTime = rstImport!CurrentTime


End If


Where the problem exists for me, I think, is that 'Seek' requires an index to be used as a key. In my circumstances there is no key that relates between both tables, in fact there is no index in the created table. Is there another way of achieving what I am after, am I being overly elaborate with this.

For upcoming training course dates see: Pricing & availability

Tue 27 Mar 2012, 10:48 replyReply Trainer Jacob said...

RE: Appending data to a table only where it won't create a dupli

Hello Tim

Thanks for your comprehensive post outlining what you are trying to do.

Well we may be able to assist but this will take us beyond the scope of this forum and more into a consultancy type requirement:

1) You send us your working files (we're happy to sign an NDA or equivalent if need be)
2) We review your files and if a solution is quickly found we will let you know. If the solution requires dedicated trainer time to work on it, we will advise this and associated costs.
3) We won't take any action until you are updated and give us the go ahead.

If you would like to discuss this further please do get in touch with our enquiries team:

Kind regards


Mon 2 Apr 2012: Automatically marked as resolved.


Access tip:

Deleting duplicate records from a table

You cannot delete records tables where there duplicate records. A way around this is to create a new table which wont hold the duplicates. and then deleting the old table.

1. Use a make-table query based on this table only. IMPORTANT - Ensure that you include all of the fields from your original table or you may loose data.

2. Open the query's property sheet by using VIEW, QUERY, PROPERTIES, and set the Unique Values property to Yes

3. Because you have selected the Unique Values to Yes when you run the query, Access creates a new table without duplicate records. You can now delete the old table and rename the new one.

View all Access hints and tips


Live dates & prices


Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.