Archive data to another Access DB ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to somehow transfer table rows from one Access DB to another
Access DB. I am dealing with this datrabase that gets really big and
periodically they want to transfer all rows before a certain date to an
archive Access db. The databases will not be linked via the linked table
manager unless I can dynamically set up the link, do the archive, and then
remove the link.
 
Describe "really big" in terms of .mdb file size and number of rows in the
larger tables.

If the file is under 1GB in size and the largest table is under 500,000
records, don't even think about doing it. Instead figure out where there is a
performance problem and fix that. Sometimes something as simple as adding an
index can do wonders.
 
A create table query can copy rows to another database: a
delete query can remove them.

If you have a 1GB database, you should try copying the
database to the destination, then deleting the original database,
to get an idea of how long it takes. (Delete the original
database to be sure that the copy has completed - it may
be a background task, depending on what exactly you try).

Creating and deleting indexes takes a long time: selecting records
takes a long time if there is no index.

(david)
 
Big enough that the performance is seriously impacted unless records are
periodically removed. This is a help desk application and response time is
critical. I cannot overhaul the database design (which is horrible). The only
solution is periodic removal of old records.

Dorian.
 
Big enough that the performance is seriously impacted unless records are
periodically removed. This is a help desk application and response time is
critical. I cannot overhaul the database design (which is horrible). The only
solution is periodic removal of old records.

Dorian.
 
You seem to have missed my point. I know all that. What I need to know is if
can dynamically link to my archive database, transfer the records, then
destroy the link. I don't want the link to be permanent. There will be no
access to the archived records via this application. This database is used
24/7/365, there can be no interruption in service so I cannot 'delete the
database'.

Dorian.
 
Dorian

"The only solution..." implies you've tried other things.

If you want the folks who read these groups to not think up new/creative
"solutions", giving them a bit more description of what you've already tried
would avoid unnecessary "help."

For instance, slow performance can be due to many factors besides table
size. Have you exploered any of those? I ask, not to be difficult, but to
see if there's a possible solution that avoids writing records out to
another database. This "solution" is less than optimal (and may STILL prove
to be necessary).

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Yes, I seem to have missed the point. I thought that you were
asking if it was possible to somehow transfer table rows from
one Access DB to another.

It is certainly possible to dynamically link to an archive database.

However, the idea that you could leave your application running
24/7 while you archive data is false.

The only way to recover the unused space is to compact the
database.

Compacting the database is a process which closes the source
database, copies the remaining data to a new database, deletes
the source database, and renames the new database.

You can't do that while linked applications hold the file open.

(david)
 
Back
Top