An Archive Database

[Guest]

I have 2 nearly identical databases. One is the "main" database.
information is updated daily to this database. any "old" information is
archived to the "archive" database via VBA code in the "main" database. This
is information that is older than a specified time (say 1 month).

Now, the information is obsolete in a few more months. Is there a way to
manipulate the "archive" database from the "main" database to delete the
obsolete information?

I know the better idea would be to have a back end DB with a front end.
This would eliminate the entiere DB being opened/transfered over the network,
but as it stands, the entire DB is moved around. So, if someone could help
me/point me in the right direction of being able to manipulate a second db
from inside another DB i would greatly appreciate it.

 

[storrboy]

I have 2 nearly identical databases. One is the "main" database.
information is updated daily to this database. any "old" information is
archived to the "archive" database via VBA code in the "main" database. This
is information that is older than a specified time (say 1 month).

Now, the information is obsolete in a few more months. Is there a way to
manipulate the "archive" database from the "main" database to delete the
obsolete information?

I know the better idea would be to have a back end DB with a front end.
This would eliminate the entiere DB being opened/transfered over the network,
but as it stands, the entire DB is moved around. So, if someone could help
me/point me in the right direction of being able to manipulate a second db
from inside another DB i would greatly appreciate it.

Read up on either the OpenDatabase mehod or using the IN clause in a
query.

 

[Fred Boer]

Dear Mike:

Another option to consider is just to have one database. When a record
becomes "obsolete", instead of moving it to another table, just "flag" it
and use queries to filter out the record. You can do this by adding a
"Yes/No" field to the table, and call it, say, "ObsoleteRecord". Of course
this won't be suitable if you have tables that are too large (i.e. gigabyte
file sizes), but you haven't indicated that file size is an issue.

HTH
Fred Boer

 

[Jeff Boyce]

Mike

First, why? As in "why (i.e., what business purpose) are you 'archiving'
data"? Do you have "too much"? How much?

Next, if you understand that a split database (front-end on each user's PC,
single back-end on the LAN) is the recommended design, what is your current
design ("the entire DB is moved around") doing that the split design
wouldn't? (aside from annoying the network wanks and gobbling network
bandwidth<g>).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Tony Toews [MVP]]

Now, the information is obsolete in a few more months. Is there a way to
manipulate the "archive" database from the "main" database to delete the
obsolete information?

I'm with Jeff. Why bother archiving at all? Surely someone might
find the data useful in a few years for some purpose.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm