Append new data to several tables based on a linked table, and do it programmatically?

[vavroom]

Phew! Long title, hope it describes my question

Here's the situation. I have data generated in csv format by one (non
Access) system, which I can't modify at all. The data + structure I
get from it is what I get, I can't budge it. I will need to generate
this csv once a day, and import the data into my Access application.

I am structuring the Access application differently than the csv, as
the table structure isn't optimised in the csv, not by a long shot, and
I need to build more feature/functions on to it, using the table as it
would be created by the csv is simply not an option.

I can go to my File > Get External Data > Link Table, then go through
that wizard, then use 3 append queries in succession to get the data
out of the csv I just linked into the relevant three tables. That
works.

But, It's a pain in the beeehind to have to take those steps each time
I need to do this. Further, the application is to be distributed to
people, some of whom barely know where the on/off switch on the
computer is. I can (and will) train them, but the easier it is for
them the better it'll be for me.

I can create a form with a command button on them that runs the queries
in succession, that's relatively easy.

What I can't seem to find (and yes, i've looked), is how to start the
"get external data" wizard through that command button rather than
through the file menu. I suspect it's something easy I've missed,
could a kind soul point me in the right direction?


Thanks in advance

 

[John Vinson]

What I can't seem to find (and yes, i've looked), is how to start the
"get external data" wizard through that command button rather than
through the file menu. I suspect it's something easy I've missed,
could a kind soul point me in the right direction?

Do you really need the wizard? I.e. does the csv file frequently
change its layout and format?

If you have a predictable layout of the file, then you can use the
TransferText method from VBA code in order to link to the file. The
code could easily prompt for a filename or use a Windows file dialog
to locate the file, if that's appropriate.

John W. Vinson[MVP]

 

[vavroom]

Do you really need the wizard?

Oh John! I think you've put me on a much better track, thank you

No, the csv file will not change format at all.

I've had a quick look at the help file for TransferText method and I'll
have to play with it some, never used it, so it's "brand new territory"
for me.

This would be wonderful if it worked as I think it might

Thanks again.

nic

 

[vavroom]

Revisiting and reporting on this.

It works perfectly. Just need to figure out how to insert the windows
file dialogue, but that shouldn't be too complicated.

Sooo, thanks a lot John, your suggestion made it all nice.

 

[Douglas J. Steele]

Don't use the Dialog object: use the API calls instead.

See http://www.mvps.org/access/api/api0001.htm at "The Access Web" for a
complete example.