Unanswered: Reference an dbase table based on a variable
I have an access application that I need to have update a dbase table based on the last 2 digits of the year (i.e, the dbase table is located in c:\mytable\table03\item.dbf. The table is always the same name, it just might be located in a different directory. FYI: I do have a field in one of the tables (Fiscalyear) that indicates the two digit year code to assist but the data could be going to more than one dbf at a time depending on when the update may take place.
For the Access environment, you will really want to attach all of those tables to the Access MDB, all at once. Yes, all of them, in advance.
There's just not gonna be a pleasant way to do this, if you must stick with this structure and update these tables in situ to support an existing application. But sometimes in the real world of business these things just can't be helped. "That's why programmers get paid so much" and it's "why they change jobs so much!"
What you may be able to do is to sort the records you're going to update, e.g. by specifying an appropriate order in a query which is the source of input-data to your program. (You're going to be writing a hefty amount of VBA code...) This will allow you to process all of the records for a particular year at once instead of bobble-heading between one dBase table and another. Anything will help.
What you donot want to do is to fiddle around with programmatically attaching and detaching those tables! No, no! Bad doggy! No, no, no!
Thanks for responding. In a perfect world I would change everything to Access as I am updating my portion of the program to Access (due to information I am getting from yet another table being changed over to Access from Dbase) but the tables I am trying to update are still used by another program in Dbase in my company.
The current dbase way I do this is have a field in a table that indicates the basic directory (c:\mytable).
My dbase code trims the year to the last 2 digits and then updates the field to c:\mytable03 or c:\mytable04 as the case may be.
Is there a way to refer to a table in Access as a variable depending on information in another table?