Import external data
-
When importing data from an external file, MSAccess appends the data. Is there a way to have it automatically replace the existing data?
-
Insert into a temp table that will be blown away. Then create an "unmatched" query by left joining my existing table with the temp table. Off the top of my head
INSERT INTO tblExistingTable (Field1, Field2,etc..)
SELECT
TT.Field1,
TT.Field2,
TT.Field3
FROM #TempTable AS TT
LEFT JOIN tblExistingTable AS ET
ON TT.SomeUniqueField = ET.SomeuniqueField --THis is if you arent using the same PKIDs
WHERE ISNULL(ET.SomeUniqueField, '') =''
-- Or you can use:
--WHERE ET.SomeUniqueField IS NULL
-
Why not create a macro that deletes the contents of a predefined table before importing your file into the predefined table. I hope this helps
-
You could also just run a delete query on the table in Access and then import your file. You could roll it all up in a macro or code to make it easier to run. If you do it this way the only disadvantage is if the import fails then your table will be left empty. It depends on your usage and whether this is a critical factor in your process whether you'd want to implement it this way,
Andrew
-
docmd.runsql "Drop Table tableName"
-
Thanks everyone for your replies. These are all viable alternatives and I will keep them in mind. But what I wanted to know is, if there is a system setting within Access that would cause it to replace existing table data instead of appending it. Essentially, deleting all existing rows or truncating the table. I guess I want Access to do the work for me.
-
No, as far as I know there isn't a setting. I think you'll have to do it "the hard way"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply