Pros & cons of diff ways to auto import textfile and run report

[Anders_S]

Every week I'm sent a tab-delimited text file, which contains a few thousand
records, on which I want to run the same query&report. What's the best way
to automate this? Importing the text file via TransferText is pretty
straightforward. Where I need some guidance is whether, for example, I'm
better off deleting the old table and then renaming the new table I've
created vs. cleaning out the table that's tied to the report to a temp table
and then doing an append query to add the data to the table that's tied to
the report. Also, what are the pros and cons of doing it using a macro vs.
VBA, or is that no longer an issue?

My constraints are:
* My office runs Access 2000
* Whatever I write needs to be as simple as possible, because folks who
aren't programmers may someday monkey with it (which is why I'm also
considering doing the whole thing via a generic automation tool like
AutoHotkey).

Any thoughts? And if this question's already been answered elsewhere, just
point me in the right direction; I looked around and could find solutions but
not adivce on pros vs. cons.

Thanks,
Anders

 

[John Nurick]

Hi Anders,

There's no need to import the data in the scenario you describe: you can
use a linked table instead.

A) If the text file can always have the same name and location, then

1) Use File|Get External Data|Link to create a linked table connected to
the file.

2) Base your query on the linked table and the report on the query.

Then, each week put the new text file in that location with that name,
and run the report. The linked table will get the new data.

B) Otherwise, use TransferText acLink to create a linked table each
time, then delete the table after use.

 

[Anders_S via AccessMonster.com]

Thanks so much, John!

 

[Klatuu]

I would agree with John's approach
(but I wouldn't let my daughter date him)

 

[John Nurick]

I would agree with John's approach
(but I wouldn't let my daughter date him)

But I'd approach your daughter, not you!

 

[Klatuu]

Good, that would be far less dangerous