Overwriting records when importing data 1

[JimmyDix]

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]

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

 

[JimmyDix]

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