KeepDesignChangeHistory

[Voisine]

Hi All,

I have a replicated database which has puzzled me for some time because it is growing in size extremely quickly. After doing some research I have found that this is because I have a number of delete queries running but because the KeepDesignChangeHistory property defaults to 1000, the deleted records are being stored in MSysTombstone for 1000 days, which is way longer than necessary.

I want to reduce KeepDesignChangeHistory to 30 days. I looked on the microsoft support website which told me I need MS Replication Manager to do this, but when I tried to procure it I was told that MS Replication Manager is not available with Office XP!

Does anyone know how I can change the KeepDesignChangeHistory property in Office XP?

I'd greatly appreciate your assistance.

Thanks.

 

[Voisine]

Sorry, I actually need MS Replication Manager for Office 2003, not XP. Any assistance greatly appreciated.

 

[ghudson]

Compacting the db will remove the unseen [deleted] junk in the db.

 

[VanRoekel.Clint]

Compact on close

Normally a compact creates a new smaller database but under Tools, Options, General tab you can set the database to compact on close and the database will be compacted to the same name. If you open and close the database frequently the time required to compact may be an issue.

 

[ghudson]

Yes. That is why I run this function to only compact the db on close if it exceeds my preset amount when the db is being closed.

'Used with setting the status bar message
Public vStatusBar As Variant

Public Function CompactOnClose()
On Error GoTo Err_CompactOnClose

    If FileLen(CurrentDb.Name) > 5000000 And CurrentUser <> "programmer" Then '5 megabytes
        Application.SetOption ("Auto Compact"), 1
        Application.SetOption "Show Status Bar", True
        vStatusBar = SysCmd(acSysCmdSetStatus, "The application must be compacted, please do not interfere with the Compacting process!")
    Else
        Application.SetOption ("Auto Compact"), 0
    End If

Exit_CompactOnClose:
    Exit Function

Err_CompactOnClose:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_CompactOnClose

End Function

 

[Voisine]

My database is set to compact on close, but that is not what is causing the large file size.
See http://support.microsoft.com/kb/q182464/



Thanks.

 

[ghudson]

The Access 2003 help files does not have any information about the Replication Manager. Not does the Access 2003 Developers Extensions help files. Maybe Microsoft forgot about this problem for the Access 2003 db's since the KB article # 182464 you referenced is for Access 97.

Just a wild thought but what if you down saved your db to Access 97, changed the settings using the 97 version of the Replication Manager and then converted the db to Access 2003?

 

[Voisine]

Luckily for me there is a genius out there called Michael Kaplan. I've just been referred to his site http://www.trigeminal.com/ because he's designed some software called TSI Synchronizer which does exactly the job I need it to do. And he's been kind enough to distribute it for free!