[AccessD] Archiving Records

Here's another approach Rocky.  Change the name of the table you want to
'archive' to Current+the name of the table. (ie, so if the table is
"tblSomeTable", change it's name to "CurrenttblSomeTable".  Now build a
query, with "CurrenttblSomeTable" select all the fields (not the *) and
save the query as the original table name ("tblSomeTable").  Go into the
design of that query and remove the semicolon at the end, and save the
query.

Now make a blank database in the same folder as the original database,
and name that new blank database Blank.mdb.

Put the following code into a module:

Private Declare Function CopyFile Lib "kernel32" Alias "CopyFileA"
(ByVal lpExistingFileName As String, ByVal lpNewFileName As String,
ByVal bFailIfExists As Long) As Long
Function ArchiveRecords(strTableName As String)
Dim strTemp As String
Dim i As Long
Dim strNewDBName As String
Dim strCurrentPath As String
Dim strSQL As String
Dim qry As QueryDef
Dim rs As ADODB.Recordset
Dim tmpDBName As String
strNewDBName = Dir(Application.CurrentDb.Name)
strCurrentPath = Left(Application.CurrentDb.Name,
Len(Application.CurrentDb.Name) - Len(strNewDBName))
strNewDBName = Left(strNewDBName, Len(strNewDBName) - 4)
tmpDBName = strNewDBName & Format(Date, "DDMMYYYY") & ".mdb"
strTemp = Dir(strCurrentPath & tmpDBName)
i = 1
Do Until strTemp = ""
    tmpDBName = strNewDBName & Format(Date, "DDMMYYYY") & "-" & i &
".mdb"
    strTemp = Dir(strCurrentPath & tmpDBName)
    i = i + 1
Loop
strNewDBName = tmpDBName
CopyFile strCurrentPath & "Blank.mdb", strCurrentPath & strNewDBName,
True
strSQL = "SELECT * INTO " & strTableName & " IN """ & strCurrentPath &
strNewDBName & """ FROM " & strTableName
DoCmd.RunSQL strSQL
strSQL = "DELETE * FROM " & strTableName
DoCmd.RunSQL strSQL
Set qry = CurrentDb.QueryDefs(Mid(strTableName, 8))
strSQL = qry.SQL
strSQL = strSQL & vbCrLf & "UNION SELECT "
Set rs = New ADODB.Recordset
rs.Open strTableName, CurrentProject.Connection, adOpenKeyset,
adLockReadOnly
For i = 0 To rs.Fields.Count - 1
    strSQL = strSQL & rs.Fields(i).Name & ", "
Next i
strSQL = Left(strSQL, Len(strSQL) - 2) & vbCrLf & "FROM " & strTableName
& " IN """ & strCurrentPath & strNewDBName & """"
qry.SQL = strSQL
Set qry = Nothing
MsgBox "Please Compact the Database Now"
End Function

Run the function above with the new table name. ie,
?ArchiveRecords("CurrenttblSomeTable")

Whalla.  All of the records in that table are now stored in a new
database, with the name of the existing database + the current date (and
-1, -2, etc, if this is done multiple times in the same day).  Also, the
'original table' is now a union query, that will display ALL the
records, even though the archived data is stored in separate MDBs.

A few caveats with this approach.  First, I didn't set the warnings off,
you can do that if you want.  Second, if you are using an Autonumber
field in the table you are archiving, if the user then compacts the
database, the autonumber is reset to 0.  The next time you archive, you
will have duplicate primary keys.  To get around this, either change the
SQL statements in the DoCmd.RunSQL statements to leave a few records
behind, or find the last ID, and after they compact the database, insert
and delete a record with that last ID. (which will set the Autonumber
back on track).

In case you are wondering, having a query named with the original table
name, you will  not need to change anything else in your project.  All
forms, reports, code, even other queries should automatically use the
new query just like it was the old table.  The exception to that would
be wherever you are adding records to that table.  That will need to be
changed to use the 'CurrenttblSomeTable'.  Also, any process where you
don't want to see 'archived' records, you can point those to the actual
table too.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
at Beach Access Software
Sent: Monday, June 23, 2008 10:30 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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
 
The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.