Dynamic VBA

[jane30]

Hi, I need help on creating a dynamic VBA code. With Access97, I'm doing the following;
There are 12 tables,tbl0702, tbl0802..., tbl0603, in the Master.mdb, populated with the data imported from mainframe on a monthly basis. Each month, the data is split by dept and export to Depta0203.mdb, Detpb0203.mdb..., Depte0203.mdb. Each fiscal year, I create new tables in the Master.mdb like tbl0703, tbl0803...tbl0604, and create new dept database like Depta0304.mdb, Deptb0304.mdb, etc. To ensure the data accuracy in the dept database, I use union query to get record count from Master.mdb and the record count from individual dept database and reconcile them. When new fiscal year comes, it's time-consuming to change the table name from tbl0702 to tbl0703, etc and database name from Depta0203.mdb to Depta0304.mdb,etc in the union query. Is there any better way to do this? Any help is appreciated.

 

[Russie]

Millions of them.....

You could try ADO connections and query with SQL if you are confident...

or the easiest way is with linked tables (look em up).

If you link your tables to the mainframe database from your local database you will be able to see the data in the mainframe tables from your local database and you can then code/sql/query them to your hearts content.

when your tables are linked you will see what I mean...

Linked tables aren't recommended by most professionals because jetSQL has to do all the work and you're reliant on it. In this instance it will give you the ability to solve your problem in the short term...

........but it is reccommended that you learn SQL, ADO and VBA principles to do this well.