Copy table in a split database

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

 

[tina]

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]

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

 

[tina]

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]

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.

 

[Chris Nebinger]

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

 

[Lynn Trapp]

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.

 

[DQ]

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

.

 

[DQ]

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

 

[Lynn Trapp]

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.

 

[DQ]

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.

And pain it is!!.. And yes, it's that way because IT
people don't get much input on how government organizes
their finances, we're slaves to the whims of elected
officials who don't care about the best way to organize
and store data. Basically each years expense table has to
be "frozen in time" for different types of history and
year over year comparisons, that's why a new one has to
be created each fiscal year. But it at the same time
previous years information has to be dynamic because of
the acrual and reorganization accounting methods. So each
year's table does contain previous years information that
can and has to be changed. It gets even more complicated
than that but that's the case in a nutshell..

Thanks for your sympathy and understanding!!..

 

[Lynn Trapp]

And pain it is!!.. And yes, it's that way because IT

people don't get much input on how government organizes
their finances, we're slaves to the whims of elected
officials who don't care about the best way to organize
and store data. Basically each years expense table has to
be "frozen in time" for different types of history and
year over year comparisons, that's why a new one has to
be created each fiscal year. But it at the same time
previous years information has to be dynamic because of
the acrual and reorganization accounting methods. So each
year's table does contain previous years information that
can and has to be changed. It gets even more complicated
than that but that's the case in a nutshell..

Does ANYONE get any input on how government organizes stuff. Isn't that an
oxymoron -- government organization. Still, from what you have said so far,
I'm not sure why those "experts" y'all hired gave up so easily. I believe I
could recreate exactly what you are doing with a single table, with the
added date field AND a field to mark a transaction as "frozen". The other
complications you mention might throw some other parameters in there, but I
still believe it could be done. Of course, like I said originally, that begs
the question of whether or not it's worth the time and effort.

Good luck with it.