Importing Data Into Existing Tables Without Emptying Them First

[Amy E. Baggott]

I have a database that is updated from a non-Access database that does not
allow for a direct link. Twice a day, this other database sends me a set of
5 CSV files. I have a set of code instructions that empties the tables this
data goes into and then imports the data from the files into the existing
tables, then does a few other procedures to link the data up to other tables
that do not get replaced. As you can guess, this is an appallingly
destructive process, and I think it may be part of why the database needs to
be repaired on a semi-regular basis. Is there some way that I can import
only new records from the csv files and update the ones that are already in
there based on the data in the csv files? I had thought of setting up dummy
tables to import into, but it seems to me that this just adds another layer
to my code and would slow it down further (it's already quite slow enough,
thank you) without solving the original problem, as I would still be emptying
and refilling tables.

Is there any other way around this? Can I open a csv file in a recordset in
vba code?

 

[Klatuu]

Yes, you can link to a csv file and use it like a table. You will then have
to create either queries or VBA code to append the records you want.

 

[Amy E. Baggott]

Will the link go away each time the other database replaces the file?

 

[Klatuu]

No, you need to establish the link, do your processing, the destroy the link.
Use the DeleteObject method

 

[Amy E. Baggott]

How do I establish the link using code?

 

[Klatuu]

Use the TransferDatabase method. Look it up in VB Editor Help. You will
find all the specifics there. Good Luck.

 

[Amy E. Baggott]

TransferDatabase does not appear to work with a csv file, or at least does
not list that among the options. The only method that does is TransferText.
Is that what I should be using?

 

[Klatuu]

D'oh!
Too much going on in my head today. Sorry, you are correct. Also, you
can't link to a text file, you have to import it. Every thing else should
work the same, so.
Import the text file to a temporary table. Append to you permanent table,
then delete the temporary table.
My apologies for leading you astray.

 

[Bob M via AccessMonster.com]

I use this code to import a txt file, and then make a table:

DoCmd.SetWarnings False
DoCmd.TransferText acImportDelim, "Apr05 Import Specification", "Apro5",
"Y:\Workbrain\apr05.txt", False, "", 437
DoCmd.OpenQuery "Apro5 Query", acViewNormal, acEdit

A csv file will work the same. I imported the csv manually first to set my
Specification def.(Apr05 Import Specification) I then created a macro to do
the above three tasks and then saved the macro as code.
The importation will ovrewrite the Apr05 table. The Apr05 Query query is a
make table query that will overwrite whatever you name your new table and all
queries, etc. should remain linked. I believe that any external links to the
made table will remain intact. I haven't taken it that far, but you can
experiment to see if it does. If you try it, please reply to this thread as
to your results.
Bob