External Change Tracking

This article relates to my previous post that describes a change-tracking system for MS-Access. You can view the complete code with comments here: Change Tracking in Access

In a very active database, the tracking table can become quite large, so here are two methods to move the tracking table to a separate file.

Remember - always make a backup copy before making significant changes to your database.

Method 1

Create a new blank database.
Import the tracking table from the original file.
Delete the tracking table from the original file.
In the original file, create a link to the table in the new file.

...all of the above can be done using the Access GUI.

Method 2

This module will create a new file in the same folder as the original file, move the tracking table and link to it. You would run this only one time...

Sub External_Change_Tracker()
Const MyTable = "tbl__ChangeTracker"
'
Dim dst As DAO.Database
Dim MyNewFile As String
Dim src As DAO.Database
Dim tdf As DAO.TableDef
'
On Error GoTo err_sub
'
' create new db
MyNewFile = CurrentProject.Path & "\Change_Tracker.mdb"
Set dst = CreateDatabase(MyNewFile, dbLangGeneral)
' copy the tracking table
DoCmd.CopyObject dst.Name, MyTable, acTable, MyTable
' drop local tracking table
DoCmd.DeleteObject acTable, MyTable
' link to table in external file
Set src = CurrentDb
Set tdf = src.CreateTableDef(MyTable)
With tdf
.Connect = ";DATABASE=" & dst.Name
.SourceTableName = MyTable
End With
src.TableDefs.Append tdf
exit_sub:
Exit Sub
'
err_sub:
MsgBox Err.Description, vbCritical, "Error # " & Err.Number
Resume exit_sub
'
End Sub

You can view the complete code here: External_Change_Tracker

Whether you use method 1 or 2, you will still need to perform a Compact & Repair to reclaim the space used by the deleted table.