Overwriting records when importing data 1

JimmyDix · May 30, 1999

I have a database that uses a VBA module to import comma delimited data into a table. I want this data to overwrite what is in the table if a given field is the same. Is there a way to do this?

Lehtoant · Jun 1, 1999

Hi, 
 
I would first import text file to temporary table (safe!) and then delete the old records and then insert records to target table: 
 
Something like this: 
 
DELETE TargetTable.Field1 
FROM TargetTable INNER JOIN TempTable ON TargetTable.Field1 = TempTable.Field1 
WHERE (((TargetTable.Field1)=[TempTable]![Field1])); 
 
And: 
 
INSERT INTO TargetTable ( Field1, Field2, Field3 ) 
SELECT TempTable.Field1, TempTable.Field2, TempTable.Field3 
FROM TempTable; 
 
You can write these queries as modules or macros and run them without warnings (DoCmd.SetWarnings False) 
 
Good luck, 
Al

JimmyDix · Jun 7, 1999

Didn't quite work...had to use 
 
DoCmd.RunSQL "DELETE * " & _ 
"FROM DRAWING " & _ 
"WHERE DRAWING.[Drawing Number] IN " & _ 
" (SELECT [Drawing Number]" & _ 
" FROM TempData); ", -1 
 
instead. Thanks for the help!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Overwriting records when importing data 1

JimmyDix

MIS

Lehtoant

MIS

JimmyDix

MIS

Similar threads

Part and Inventory Search

Sponsor