archive old records in split db

[jedder18]

Looking to clean up a huge db that is split.
would like to move previous yrs data of the 2 tables to a separate archived table.
How do I do this without screwing up the link?
I've previously copied tables and removed old rows then put table back, with that becoming messy as the queries were then looking for a different table. Those were not from a split db.

 

[Ranman256]

VER 1:
I have a backup button to copy the BE to the backup area,

sub BackupDb()
vDb = "BE_data.accdb"
vSrc = "\\server\folder\" & vDb
vTarg = vDb & "_Backup" & Format(Now, "yymmdd-hhnn") & ".accdb"
Copy1File vSrc, vTarg
end sub

Public Sub Copy1File(ByVal pvSrc, ByVal pvTarg)
Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFile pvSrc, pvTarg
Set FSO = Nothing
End Sub

then if I want to view old data, I click a button that resets the link paths from the Current db, to the archive db that I choose.

VER 2:
The old db is linked into the current db with links to old tables,
tEmployees (current file)
tEmployees_Old (links to old db)

then there are queries that pull data from the old db.
If I want them combined with the new data , I make a union query of both current and old.

 

[theDBguy]

Hi jedder18. How big is this "huge" db? What is the file size?

 

[The_Doc_Man]

First and foremost, make a backup copy of the DB before you do anything else that would potentially remove data. Keep it around just in case things go badly.

There are two or three approaches. They depend on how much you want to do with your old data after it is "officially" old.

Method 1: Simply add a Y/N flag to the record that says "Officially Old" (using whatever name seems right for the intent.) Mark the records meeting the criteria. Then when you look at that table intending only to work with current data, include a where clause in the query that says (more or less) "WHERE ... AND ( OFFICIALLY_OLD = FALSE )". As long as you are not approaching 1 Gb table size or 2 Gb total database size, you are good as gold.

Method 2: For size reasons, you want to really get rid of the old records but you might still wish to look at them. As it happens, you can have multiple back-ends linked to the same front-end. SO... you can build your archiving table in a second file that will contain up to 1 GB in this table. Permanently link to this "Archive back-end" from the FE. Then have the same flag noted in my method 1 and use it for two queries: First an "INSERT INTO archiving_table (field-list) SELECT field-list FROM active-table WHERE OFFICIALLY_OLD = TRUE ;" followed by "DELETE * FROM active-table WHERE OFFICIALLY_OLD = TRUE ;".

Once you do this, you need to carefully perform a Compact & Repair before letting this system go active again. Then as suggested, you can query the archiving table separately or in conjunction with a UNION query. The pitfalls in method 1 and 2 BOTH revolve around an Access limit that says no recordset can grow to greater than 1 Gb and no single back-end file can grow to greater than 2 Gb.

Method 3: (Included for completeness purposes) If your dataset is getting that large that you really NEED to do this AND you want to retain the data for running analytics on a regular basis, consider something like an active SQL backend - like SQL Server, MySQL, or any one of a number of other similar products. Then up-convert your Access back end to an active-SQL backend. This might be more than you wanted to do, but to give you a complete list, I felt I should include it.

Method 4: For the case where you will NOT be referencing the old data but would like to keep it around for snorts & giggles. Instead of doing an INSERT INTO for another table, look at exporting the table to an Excel workbook based on a query that selects the obsolete records. DoCmd.OutputTo can take a query and put it out in another format.

 

[jedder18]

the 2 tables that are large in size have records from 2009-2019
we are trying to remove 09-16 for the queries, forms, reports, etc to run faster.
I'd like to be able to copy the tables, which I already did, then remove the old data from current copy, so as not to mess up the links.
I find when I copy a table, fix it, rename, the queries get really messed up with the links. And that's just in a regular db that is not split.
I don't really know how to do this in a split db.

 

[Cronk]

When developers talk about the size of a database file, it is not measured in the number of years of data, nor the number of records. It is the size measured in megabytes.



Access has a limit of 2 GB. Accordingly, I wouldn't call a database large until it is over say 1.5 GB

 

[Mark_]

the 2 tables that are large in size have records from 2009-2019
we are trying to remove 09-16 for the queries, forms, reports, etc to run faster.
I'd like to be able to copy the tables, which I already did, then remove the old data from current copy, so as not to mess up the links.
I find when I copy a table, fix it, rename, the queries get really messed up with the links. And that's just in a regular db that is not split.
I don't really know how to do this in a split db.

Were this me, I would use Doc's #1 solution and put in your "Old" flag. I'd then set up queries to either exclude these records, Report ONLY on these records, or both.

Your existing forms/reports would then be changed to point to the proper query instead of your existing query. Should be rather straight forward without much of a change to your users except they won't see "Old" records.

Now if you don't have your forms / reports running off of queries, you see one of the reasons this is so often used.

 

[AccessBlaster]

I would just archive the whole table like the picture below, and then dump the original data. But then again, I am not an expert and very unorthodox

 

[Galaxiom]

the 2 tables that are large in size have records from 2009-2019
we are trying to remove 09-16 for the queries, forms, reports, etc to run faster.

That in itself wouldn't be a problem if you are using appropriate indexes and efficiently constructed queries.

 

[The_Doc_Man]

Now that we know there is a SPEED issue, Galaxiom's suggestion is perfect for what you are trying to do. Indexing on the record's date is highly efficient. Heck, if the only thing you are doing is removing by date, you don't even need the flag I mentioned. A WHERE clause built on a specific "cutoff" date will be just as efficient.