Docmd.TransferDatabase where relationships exist

[Hugh self taught]

Hi Special people,

I need to replicate a table in my database back end. This is so that I have
a separate table with all last years couples that I can run current year
comparisons to. Ideally I want a newly created table so I start it afresh.

I tried the DoCmd.Transferdatabase method for ease of code but there are
existing relationships. In the immediate term I have resorted to using the
DoCmd.CopyObject & a Delete query turning warnings off & on. Not what I'd
prefer to be doing though. Seems a bit messy.

Any alternatives to my dilema?

 

[Tom van Stiphout]

On Wed, 30 Dec 2009 06:12:01 -0800, Hugh self taught

If I understand you correctly you want to make a copy of a table for
the benefit of having the data for one year together in that table.

This is a REALLY BAD idea and such approach does not belong in a
relational database. Rather you should use queries. For example:
qryCouples2009:
select * from tblCouples
where Year(StartDate) = 2009

or perhaps even better:
qryCouplesThisYear:
select * from tblCouples
where Year(StartDate) = Year(Date)

qryCouplesLastYear:
select * from tblCouples
where Year(StartDate) = Year(Date) - 1

Make sure you have an index (allow duplicates) on the StartDate
column. This will improve performance.
Then you work with these queries like you would with your imagined
tables.

-Tom.
Microsoft Access MVP

 

[Hugh self taught]

Hi Tom,

I understand exactly what you're saying, however to change the way the table
is used will mean an almost complete re-write of the database.

The major reference to the data in this table is the couples' number which
belongs to the male in case they change partners in which case I record who
the old partner was, date of change etc in a "history" table. All to often,
couples (the male) don't return in the new year & their number gets
reallocated to a new couple otherwise they get to keep the same number.

I started developing this database at the beginning of the year for the
association I belong to for them to keep up to date records & records the
points they earn at competitions. I've added (as I've learned along the way)
a bunch of functionality & checks to eliminate the user generating errors. I
had not envisaged the functionality I want to implement now for the start of
the new year.

So for now I need to make a "quick fix" method of getting the data so the
association can keep functioning with this database. Then I can consider a
re-write as a pet project to improve it in the new year at my leisure.