Copying Records using VBA SQL statements

[alathwell]

I need to be able to copy a series of records from a set of related tables.

Tables are related as below:

tblEventSeries - Primary key SeriesID -> 1 EventSeries

tblEvents - Related key SeriesID, Primary key EventID -> Can be many Events

tblEventResources - Reelated keys SeriesID , EventID, Primary Key ResourceID
- Can be many resources

I want to copy the contents of each table, but with a new SeriesID, & new
related EventID's but the same ResourceID's as the resources will remain the
same. It is the SeriesEvent and Event details that will be new but with same
of the orginal data reused, but not for instance the same client , clientref
num or event names.

I am succesfully copying records on my previous version which didn't have
the tblSeriesEvent element to the database, but with the new version I just
cannot get the tblEventResouces to copy the correct reords for each tblEvent
related to the tblEventSeries. Somehow I need to retain the tblEvent
EventID's from the original EventSeries and append them to the
tblEventresources, but with the new related EventID & SeriesID.

Any help would be really appreciated.

Regards

alalthwell

 

[Tom van Stiphout]

On Tue, 17 Mar 2009 03:52:04 -0700, alathwell

Are resources reusable? If yes, you may have an incorrect db design
because there should be a M:M between Events and Resources.

That is MUCH better than what I think you are doing, which is a
denormalized design that will most likely cause grief in the long run.

-Tom.
Microsoft Access MVP

 

[alathwell]

For each EventSeries there can be many Events, and for every Event within the
EventSeries there can be be many resources all of which can be different for
each individual Event.

This mean the relationship between tables is 1 Series many Events. 1 Event
many resources.

I want to copy the whole SeriesEvent and all its related Events as well as
all of the related resources for each Event.
The resulting copy should be exactly like the orginal except there will be a
new SeriesID, new EventID's for related Events and the new related SeriesID
and EventID's in the resources table that were copied and appended to the
resources table. Client Name , ClientRef , EventSeries will not be copied
across and need to be added to the new copied EvnetSerie. Selecting a Client
will create a new ClientRef.

The data in the tbleventresources is selected by using dropdowns looking up
from a resource category and resource type tables. Each record entry in the
tbleventresources is unique to the Event and holds both the SeriesID and
EventID.

As I say the version currently operatiing which does hav ethe extra
EventSeries level works perfectly when copying single events.

hope this clears things up a bit.

Regards

alathwell