Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

Thread Tools Rate Thread Display Modes
Old 03-08-2010, 01:45 PM   #1
Registered User
Join Date: Mar 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
juanthomas is on a distinguished road
Compare and Update Records

I am using MS Access 2007. I have created an Address Master Table (Master) from a .csv file.

I frequently get updates (new addresses or changes in address information) to the .csv file. To help with the import process, I created a temp table (tmpCSV); using compare queries I can identify any new records and append them to the "Master" table.

However, the addresses, or phone numbers may change for existing records within the "Master" table.

I want to build a query or VB code that will go through the tmpCSV table and identify changes within columns and then update only the records with changes.

Does anyone have any sample code or suggestions?

juanthomas is offline   Reply With Quote
Old 03-08-2010, 04:48 PM   #2
Join Date: Jun 2007
Location: Universe - Local Group - Milky Way Galaxy - Orion Arm
Posts: 5,640
Thanks: 0
Thanked 100 Times in 47 Posts
ajetrumpet has a spectacular aura about ajetrumpet has a spectacular aura about
Send a message via MSN to ajetrumpet Send a message via Yahoo to ajetrumpet
Re: Compare and Update Records

how big are the two tables? if they're not THOUSANDS of recs long, dlookup might be fine.

do you have a unique identifier for your records in both tables? like a name? an id field?

if you do, maybe something like this would work for a query:
PHP Code:


"[uniquefield] = " & [uniquefield]) = [address], [address], 


"[uniquefield] = " & [uniquefield])),

but then again, if the unique field is always the same between both tables, you can certainly do updating even if the value remains the same....and save some processing time:
PHP Code:

iif(dcount("uniquefield""temptable") > 0


"[uniquefield] = " & [uniquefield]),  [address]),

phone iif(dcount("uniquefield""temptable") > 0


"[uniquefield] = " & [uniquefield]),  [phone]) 

be aware though that using dlookup anywhere in a query when your tables are in the thousands or more for count of records will yield minutes of processing time instead of seconds. it's an incredibly slow function, and usually a last resort with me because of that.
ajetrumpet is offline   Reply With Quote
Old 11-05-2012, 11:32 AM   #3
Newly Registered User
Join Date: Nov 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
lstein4 is on a distinguished road
Re: Compare and Update Records

Does anyone know of a way to do this easily when thousands (a few hundred thousand actually), where I have a unique identifier. I need to compare the delta file for a "hit" on the unique identifier, if I get one replace all the data in the record, if I dont get a hit, add the record as a new record. Thoughts anyone. Is there SQL code anyone has made, for prior projects or even a good add-in ? Any help is appreciated !

lstein4 is offline   Reply With Quote

code , compare , import , tables

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare Records from two tables JerryWoodstock General 3 12-31-2014 02:40 AM
Survey Stats Converter Update Issues. rsmonkey Modules & VBA 2 08-24-2007 06:03 AM
Update several fields and compare strings gasbored Queries 2 08-10-2007 03:04 AM
Update and Compare table records MartinaQ Tables 3 09-01-2005 12:20 PM
OnClick SQL Select -> Compare -> SQL Update m82palmer Modules & VBA 2 04-04-2005 02:30 PM

All times are GMT -8. The time now is 10:39 AM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World