Unanswered: Moving Table Names; Daily Transactional Data; Any tips?
I'm faced with a dilemma. In my previous job I worked with Access in a more leading way. I designed strong, yet light, databases that are true relational machines. Now I'm forced to analyze daily transactional output for accounts from the mainframe. I have no problems with the concept of doing so, looking for new data, comparing accounts and their history; but this daily output of account history gives me too much data to simply append, append, append and go.
Essentially I need some Access tricks that would give me way to automate these types of processes. The table names change with the date, and I need to use multiple lookup queries on each table. I would rather not go through the process of adding the table, recreating the relationships, and removing the old table in Design View everyday. It's too much work for such a simple task.
One solution would be a built in Find and Replace function in the query's SQL view. Even if a shareware fix exists, my company wouldn't allow it's installation.
I've solved a lot of the problems by creating fixed, update-able or otherwise non-changing tables when possible, but I'm at the end of that streak.
Could I through VBA assign table names to queries? This isn't desired either, but it might help me later so practice is good always.
The reason the table names change is that we have (I'm pulling in their tactics as my own... the source of this problem) been importing the files under their given file names. i.e. History_2_25_2008.txt becomes a table named History_2_25_2008. We do need to keep data, but I think I might have a solution, kind of.
What if I had a table called "History" and imported all new history files into that. Then I'll copy and paste the table, just appending the date?
Sucks, but maybe a solution. Will keep the table names the same in my queries, and allow me to run them without edits eh?