Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table Analyser Wizard - can do in a macro? (2000)

    I'm currently using a macro to automatically import a text file into a table. This table is not best defined and has duplicate information. The Table Analyser Wizard does a good job of splitting the table into two new tables with a many to one relationship between them. However, this procedure is manual (using the Table Analyser Wizard, that is). Is there any way of either using/calling the wizard from the macro, or else performing the same actions as the wizard in the macro? I have a DAP constructed on the two new tables, and I'd like to be able to use Access to automatically read in the text file, split it into two tables and then exit - so that I can use the DAP from IE.

    Any ideas, anyone?

    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Analyser Wizard - can do in a macro? (2000)

    I don't know of a way to have a table split automatically (and I think it would be a dangerous tool). The instruction RunCommand acCmdAnalyzeTable is the equivalent of the menu command Tools | Analyze | Table: it calls the interactive wizard.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Table Analyser Wizard - can do in a macro? (2000)

    Why don't you keep the 2 table split that the wizard created for you.
    Then you can:
    1. Import the text file into another table (called ImportText).
    2. Delete the data from the 2 tables already setup.
    3. Append data from the ImportText table into the "one" table.
    4. Append data from the ImportText table into the "many" table

    Pat

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Analyser Wizard - can do in a macro? (2000)

    Thanks for that, Hans, but even if the wizard was called from the command line, I'd still have to manually enter the details for splitting the file, yes? And although that's easy enough, I'd rather been hoping it could all just happen automatically. Good idea though! Thanks again.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Analyser Wizard - can do in a macro? (2000)

    Hey, neat idea! And it works a treat, too. It's so obvious when you're told about it. I'd been so hung up on splitting the table that I totally overlooked appending from this table into another! Thanks, Pat, you're a star.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Analyser Wizard - can do in a macro? (2000)

    If the structure of the text file doesn't change between imports, the method proposed by Patt would be your best bet, I think. You could link the text file instead of importing it; you should still be able to run the two append queries.

    If the structure of the text file does change, an automatic split would be hard to implement.

Posting Permissions

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