Linked table Manager; how can I automate changing of the linked ta

[Guest]

sI have a database I use to do my budget each year it is called
BudgetDatabase.mdb it contains all my FORM, REPORT, QUERIES. In another
Database is where all my data is stored; actually I one for each year... my
2005 data is stored in 2005Data.mdb, my 2006 data is stored in 2006Data.mdb.
I use the Linked Table Managed to link to the database I want. But, I want
others to use this database and I would like to automated the switching
between each year data . So when they select the year 2005 it automatically
links to 2005Data.mdb. When they select 2006 it automatically link to
2006Data.mdb. This could be from a form or maco or something like that.
Thanks.
Brad

 

[Ken Snell \(MVP\)]

See http://www.mvps.org/access/tables/tbl0009.htm for code on how to do the
relinking. You can wrap this with your own code to get the year from the
user and then to use it to establish the backend file name and path.

 

[Guest]

Ken Snell offered you a good solution to the problem; however, the problem
you are having is a result of inadequate database design. It really is not
necessary to maintain a separate database for each year.

The better technique is to have two additional fields in your tables, one
for the year and one for the budget period. Then, rather than relinking,
filter all your reports, forms, and queries by the year. This will then give
you the option of comparing year to year more easily. It will also execute
faster. Relinking can take a little time.