copy table in split DB

[DQ]

Hi,

First of all I do not have much experience with split
DB's. I have a DB (Access 2002) that is split. In the DB
I have tables for each year eg. Expenses2002,
Expenses2003, Expenses 2004, etc. What I want to do is in
the front end interface have a button that will copy the
a table in the back end to set up the expenses table for
the next year. Eg. copy the table in the back end DB
Expenses2004 (data and structure) to a new table in the
back end DB called Expenses2005. Once this has been done
the new Expenses2005 table in the back end DB should be
linked the front end DB so it can be accessed through the
front end interface.

The code I used to copy the back end table is

DoCmd.CopyObject "C:\DB\db2_be.mdb", "Expenses2004,
acTable, "Expenses2005

This is how far I got. The code does copy the 2004 and
makes the 2005 table in the back end but the new table in
the back end appears to be linked only to itself.

So my question is how do I (what code do I use) take that
newly created table in the back end and create a link to
the front end?? Keeping in mind that this is all being
done from the front end DB.

Any help on this would be greatly appreciated because I
am sick of wasting time trying to find out how from the
help files!!..

Thanks in advance..
..

 

[Nikos Yannacopoulos]

Dear Friend,

I'm afraid you are trying to solve the wrong problem! The real problem is
your design; you should not have separate tables per year, you should
instead store all your Expenses in one table, and just add an extra field
for the year (if it cannot be retrieved form, say, a document date field; if
it can, then you don't need the extra field). This will make your database
much more cohesive, and you won't need to copy and link tables anymore, or
duplicate forms, queries, reports etc.

HTH,
Nikos