Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by vavroom@gmail.com, Jul 28, 2006.

  1. vavroom@gmail.com

    vavroom@gmail.com
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On 12 Jul 2006 19:19:33 -0700, "vavroom@gmail.com" <vavroom@gmail.com>
    wrote:

    >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]
     
  4. vavroom@gmail.com

    vavroom@gmail.com
    Expand Collapse
    Guest

    > 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
     
  5. vavroom@gmail.com

    vavroom@gmail.com
    Expand Collapse
    Guest

    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.
     
  6. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    <vavroom@gmail.com> wrote in message
    news:1153177357.171399.8420@35g2000cwc.googlegroups.com...
    > 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.
    >
     

Share This Page