Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Location
    Oakland, California, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to automate getting external data? (A97 SR2)

    I've been working on creating a report that's ultimately based on external data (a SAS dataset which gets sent to me as an Excel worksheet). Up until now, when I've received a new dataset from the SAS programmer I've incorporated it by importing the worksheet to a new table in my database and then editing the main query to point to the new table instead of an old one. Most of the people in my department are not terribly Access-savvy, and I'd like to make the process of getting the new external data as transparent as possible to them - no editing the query or needing to know how the database is structured. What are the advantages or disadvantages of linking compared to importing? Is one easier to automate?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: how to automate getting external data? (A97 SR2)

    If the field names will always be the same, linking is a better option because you can simply alias the linked table to always have the same name and build your query to use a table with that name. It also relieves you of the bloat caused by importing and later deleting the new table.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    Great Britain
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to automate getting external data? (A97 SR2)

    As a supplementary question...

    I'm already doing something like this - I've got a number of identically-structured datasets in separate tables in one Access database and then an aliased linked table in another database, with queries and reports using it. But, because the tables (naturally) have different names, the only way of switching from one table to another seems to be to delete the link and recreate it from scratch - complete with any settings specific to the target database end (such as the alias).

    Any suggestions on how to automate this? Presumably VBA, but if the old link has to be deleted, what properties of what old objects should be captured to make the new link look to the user as if it's still the old one (except for the changed source table)?

    Peter Wilkinson
    London Guildhall University

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: how to automate getting external data? (A97 SR2)

    By identically structured, do you mean the tables have the same field names? If the do, then all you need to know is which database and which table you want to use. You drop the old link and create a new one to the selected table, but give the table the same alias as you used with the old link. If all your queries, etc., are built to use that alias, that's all you need to do. Or did I misunderstand your question?
    Charlotte

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: how to automate getting external data? (A97 SR2)

    We have used a different trick to do this in a couple of cases. The number of tables and their names in their container databases weren't changing, which allowed this approach to work. The name of a table in the front-end database doesn't have to be the same as the name in the back-end database, so we simply renamed tables, did the work we needed to, and then renamed them back to their original name. Not very elegant, but it worked.
    Wendell

  6. #6
    New Lounger
    Join Date
    Jan 2001
    Location
    Great Britain
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to automate getting external data? (A97 SR2)

    You misunderstood the question slightly. I'm currently handling the problem manually in the way you suggest - but the the source database is far enough down the directory structure on a mostly-occupied 30GB drive that I can take half a minute or so just getting there and checking I'm in the right place. Then, having chosen the appropriate table (from a list of about 30 which are appropriate datasets and another 30 that aren't) and created the new link, alias the newly-linked file appropriately - I've known myself make a typo and spend the next few minutes wondering why things are no longer working.

    So I'd prefer to get Access to handle these details itself. So far as I can see, the only way of doing so is using VBA - but if I do something like

    ....
    Set tdf = CurrentDb.CreateTableDef name:=OldName, source:=UserSelectedTable, connect:=OldConnect
    tdf.Append
    ....

    is there anything I might be missing?

    Peter Wilkinson
    London Guildhall University

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •