Copy table in a split database

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..

if you create a new table for each succeeding year, you're going to need new
queries, new forms, new reports for each one - or you're going to have to
manipulate the SQL and recordsources in each object programmatically. do you
really want to do that?

proper table design would be to have a single table for all the records
currently split up by year. just add one field to the table, to hold the
Year value for each record. then you can enter expenses year after year
without having to change your database structure.

hth


"DQ" wrote in message
...
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..

I new somebody was going to give me an answer like that.
Please do not assume I do not know how to normalize data.
There is a very good reason the tables are set up like I
mentioned, too complicated to explain in this posting so
just trust me on this OK. As for manipulating SQL and
recordsources programatically, already done, so I don't
need new forms and reports.

Previously the DB wasn't split because only a couple of
people used it and never at the same time. But now
everybody in the office has access to it so that is why I
want to split it. Before it was split the copy object
method was all it needed but with the split it has
complicated things.

So, I don't mean to get 'snooty' but can someone please
answer my question and avoid giving me the database 101
lesson.

Thanks in advance..

-----Original Message-----
if you create a new table for each succeeding year,
you're going to need new
queries, new forms, new reports for each one - or you're
going to have to
manipulate the SQL and recordsources in each object
programmatically. do you
really want to do that?

proper table design would be to have a single table for
all the records
currently split up by year. just add one field to the
table, to hold the
Year value for each record. then you can enter expenses
year after year
without having to change your database structure.

hth


"DQ" wrote in
message
...
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..


.

Please do not assume I do not know how to normalize data.

when you post information that describes poor table design, without an
accompanying acknowledgement, then that's the natural assumption people are
going to make.

I new somebody was going to give me an answer like that.

and if you anticipated the outcome, then you could have saved yourself time
and aggravation by making that acknowledgement initially. perhaps somebody
else can answer your question; good luck.


"DQ" wrote in message
...
I new somebody was going to give me an answer like that.
Please do not assume I do not know how to normalize data.
There is a very good reason the tables are set up like I
mentioned, too complicated to explain in this posting so
just trust me on this OK. As for manipulating SQL and
recordsources programatically, already done, so I don't
need new forms and reports.

Previously the DB wasn't split because only a couple of
people used it and never at the same time. But now
everybody in the office has access to it so that is why I
want to split it. Before it was split the copy object
method was all it needed but with the split it has
complicated things.

So, I don't mean to get 'snooty' but can someone please
answer my question and avoid giving me the database 101
lesson.

Thanks in advance..

-----Original Message-----
if you create a new table for each succeeding year,
you're going to need new
queries, new forms, new reports for each one - or you're
going to have to
manipulate the SQL and recordsources in each object
programmatically. do you
really want to do that?

proper table design would be to have a single table for
all the records
currently split up by year. just add one field to the
table, to hold the
Year value for each record. then you can enter expenses
year after year
without having to change your database structure.

hth


"DQ" wrote in
message
...
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..


.

I new somebody was going to give me an answer like
that.

Correction: I should have added, "after I posted my
question" to that sentence. Sometimes I don't have time
to think about and anticipate other people's thoughts and
responses while typing my question. I spend more time
trying to make sure my question is clear. Sorry about
that.

On the other hand I have answered questions to other
people's postings and when I did I just concerned myself
with the question asked. Not if I think their
application or DB could be designed better. I work in an
office where we have to do some pretty unconventional
things to get the desired results so I try to avoid
assumptions knowing how screwed up data requirements can
be from office to office.

Anyway thanks for the effort.

Well, I was thinking the same thing. If a database is not
normalized, it should be pointed out that the design is
not the best it can be.

But, now that it appears it is how you want it, you can do:

Create a copy of the Expenses table, but structure only.
This is important so there are no records in the template
table. Then you can:

Sub TableCopy()
Dim acc As Access.Application
Set acc = New Access.Application
acc.OpenCurrentDatabase (strPathToBackend)
acc.DoCmd.CopyObject , "Template",
acTable, "Expenses2005"
acc.Quit
Set acc = Nothing
CurrentDb.TableDefs("Expenses").SourceTableName
= "Expenses2005"
End Sub




Chris Nebinger

-----Original Message-----

I new somebody was going to give me an answer like
that.

Correction: I should have added, "after I posted my
question" to that sentence. Sometimes I don't have time
to think about and anticipate other people's thoughts and
responses while typing my question. I spend more time
trying to make sure my question is clear. Sorry about
that.

On the other hand I have answered questions to other
people's postings and when I did I just concerned myself
with the question asked. Not if I think their
application or DB could be designed better. I work in an
office where we have to do some pretty unconventional
things to get the desired results so I try to avoid
assumptions knowing how screwed up data requirements can
be from office to office.

Anyway thanks for the effort.


.

I new somebody was going to give me an answer like that.
Please do not assume I do not know how to normalize data.

What other assumption could Tina have made, seeing that you described an
un-normalized database and that she did not know your background. She was
working off of what she knew, and I would hazard a guess that any other
experienced database developer, myself included, would have made the same
assumption.

There is a very good reason the tables are set up like I
mentioned, too complicated to explain in this posting so
just trust me on this OK.

Now that I would like to hear about. I can't even begin to imagine a "good
reason" for that. I can imagine that some "boss" may have ordered it done
that way. I can imagine that some inexperienced developer created the
database and it might be too much work to undo (I've seen some designs that
were worth the time or effort to fix). However, I can't imagine a "good
reason" for it. There is never a "good reason" for bad database design.

Chris N. has given you a solution and I hope it does what you want, but I
think you should reconsider your design altogether. You'll be happy in the
long run if you do.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"DQ" wrote in message
...
I new somebody was going to give me an answer like that.
Please do not assume I do not know how to normalize data.
There is a very good reason the tables are set up like I
mentioned, too complicated to explain in this posting so
just trust me on this OK. As for manipulating SQL and
recordsources programatically, already done, so I don't
need new forms and reports.

Previously the DB wasn't split because only a couple of
people used it and never at the same time. But now
everybody in the office has access to it so that is why I
want to split it. Before it was split the copy object
method was all it needed but with the split it has
complicated things.

So, I don't mean to get 'snooty' but can someone please
answer my question and avoid giving me the database 101
lesson.

Thanks in advance..

-----Original Message-----
if you create a new table for each succeeding year,
you're going to need new
queries, new forms, new reports for each one - or you're
going to have to
manipulate the SQL and recordsources in each object
programmatically. do you
really want to do that?

proper table design would be to have a single table for
all the records
currently split up by year. just add one field to the
table, to hold the
Year value for each record. then you can enter expenses
year after year
without having to change your database structure.

hth


"DQ" wrote in
message
...
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..


.

Thanks for your help Chris..


-----Original Message-----
Well, I was thinking the same thing. If a database is
not
normalized, it should be pointed out that the design is
not the best it can be.

But, now that it appears it is how you want it, you can
do:

Create a copy of the Expenses table, but structure
only.
This is important so there are no records in the
template
table. Then you can:

Sub TableCopy()
Dim acc As Access.Application
Set acc = New Access.Application
acc.OpenCurrentDatabase (strPathToBackend)
acc.DoCmd.CopyObject , "Template",
acTable, "Expenses2005"
acc.Quit
Set acc = Nothing
CurrentDb.TableDefs("Expenses").SourceTableName
= "Expenses2005"
End Sub




Chris Nebinger

-----Original Message-----

I new somebody was going to give me an answer like
that.

Correction: I should have added, "after I posted my
question" to that sentence. Sometimes I don't have
time
to think about and anticipate other people's thoughts
and
responses while typing my question. I spend more time
trying to make sure my question is clear. Sorry about
that.

On the other hand I have answered questions to other
people's postings and when I did I just concerned
myself
with the question asked. Not if I think their
application or DB could be designed better. I work in
an
office where we have to do some pretty unconventional
things to get the desired results so I try to avoid
assumptions knowing how screwed up data requirements
can
be from office to office.

Anyway thanks for the effort.


.

.

Now that I would like to hear about. I can't even begin
to imagine a "good
reason" for that. I can imagine that some "boss" may
have ordered it done
that way. I can imagine that some inexperienced
developer created the
database and it might be too much work to undo (I've
seen some designs that
were worth the time or effort to fix). However, I can't
imagine a "good
reason" for it. There is never a "good reason" for bad
database design.


Obviously you've never worked for a government finance
dept! Again, I am not going to go into detail but let me
just say this. We contracted (at big bucks) some
specialists to come in and see what they could do and
they couldn't do a thing. As badly as they wanted to try
to normalize the tables, and believe me they tried, they
soon realized that it was impossible. Everything they
tried always failed to meet many key requirements and
after we spent all those big bucks they turned around and
said "sorry but with your requirements, what you need to
do with and how you manipulate your data, we can't do
anything for you." Proving once again that sometimes the
theories and methods one learns in school doesn't always
nicely apply to the real world. Actually, what am I
saying, the government has never been a part of the real
world!!..

Anyway, I thanked Chris for the coding help and I thank
all of you for your input..

Obviously you've never worked for a government finance
dept!

No, but have worked for some not-for-profit org finance departments and I
feel your pain. Still, not a "good reason", but one imposed by a "boss" --
government requirements.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm