Results 1 to 2 of 2
2003-07-09, 03:20 #1
- Join Date
- Feb 2001
- Silicon Valley, USA
- Thanked 94 Times in 90 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.
2003-07-09, 06:46 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 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.