[AccessD] Archiving Records

Susan:

Gotta move the records to reduce the size of the database.  So it sounds
like your INSERT INTO solution would work.  Have the user point to the
external database.  Link the appropriate tables, I guess.  INSERT the
desired records in the target mdb and delete them from the source mdb.

Regards,


Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com
www.bchacc.com
 
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Monday, June 23, 2008 7:02 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Archiving Records

> Is there an easy way to do this without creating a copy of each 
> records by looping through each field in the source record to create 
> the target record and then writing it to the target database?

=======The simplest way is to leave the record where it is and flag it as
active/inactive, whatever. Then, update all your queries to accommodate this
value. I realize this isn't a good solution if the problem is resources. If
resources aren't a problem, I wouldn't go to the trouble to actually move
records.

The next option is to come up with some condition -- the record's date is
usually a good one -- to use as criteria for moving the records to an
archive table -- you don't have to loop through anything, just use a SQL
INSERT INTO to "append" a recordset to an existing table -- it's the same as
an Append query. Then, use the same action with a Delete query on the
original source.

You just need some kind of anchor to identify the records you want to
archive (move).

Susan H. 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com