appending data table only
Request a callback

We'll call during UK business hours

Name:
Number:
0207 987 3777

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"

rstImport.MoveFirst

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.AddNew

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

rstShoes.Update

End If

Loop

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.
Cheers
Tim

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: info

Kind regards

Jacob

Mon 2 Apr 2012: Automatically marked as resolved.

 

Please browse our web site to find out more about
ms-access-course and other Microsoft training courses.

Access tip:

Calculating The Difference Between Dates

If you wish to calculate the time between two date fields, this can be done in a number of ways:

1. As a calculated field in a query
2. As a calculated control in a form or report
3. As a calculation in a VBA procedure.

The basic syntax to get the number of days between two dates is:

=[One Date Field] - [Another Date Field]

You can also use one of the following functions:

=Month([One Date Field] - [Another Date Field])
which calculates the number of months between the two fields

=Year([One Date Field] - [Another Date Field])
which calculates the number of years between the two fields.

Another function is the DateDiff() function.

It uses an argument to determine how the time interval is measured. For example:

=DateDiff("q",[One Date Field] - [Another Date Field])
returns the number of quarters between the two fields.

Other intervals that can be used in this expression are as follows:
"yyyy" - Years
"m" - Months
"d" - Days
"w" - Weekdays
"ww" - Weeks
"h" - Hours
"n" - Minutes
"s" - Seconds

View all Access hints and tips



MS Project training
Course rating:
4.8 stars - based on 5149 reviews
Microsoft Certified Partner Institute of Leadership and Management - Certified Courses Learning and Performance Institute - Accredited Training Provider Security Seal verified by visa, mastercard securecard

Connect with us: Facebook · Twitter · Google+ · LinkedIn · Pinterest

2nd Floor, CA House, 1 Northey Street
Limehouse Basin, London, E14 8BT
United Kingdom