Results 1 to 2 of 2
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Splitting a table with VBA (or Plan B) ((2000-2002))

    I'm working on synchronizing a monthly tab-delimited output file with our accounting system. The "Table Analyzer" wizard will let me divide off two separate tables and create keys in the original flat file. I then can add some columns to these tables to map the data. But it's too much to expect the person who will have to do this to carefully make this structure and use the appropriate table names each month so that my queries and code will work. Therefore, I think a VBA solution is required, but I don't see an easy way to automate or emulate this functionality.

    Plan B would be to break the rules of normalization and use the full data elements as keys. Then I could build my two additional tables with SELECT DISTINCT make table queries. I guess for 250-500 rows, the inefficiency is not too bad. But I keep thinking Access might supply a more elegant solution. Yes? No? Any suggestions?

    Thanks in advance for your input.

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

    Re: Splitting a table with VBA (or Plan B) ((2000-2002))

    I can only offer some very general ideas:

    1. You can use DAO or ADOX to manipulate the structure of tables. If the imported flat file has the same structure each month, it should be possible to simulate the actions of the Table Analyzer wizard.

    2. You could also create a series of action queries (make table, append, update and/or delete queries) that have to be executed one after another. Create and test them interactively, then create a little VBA routine that executes these queries.

Posting Permissions

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