Results 1 to 2 of 2
Thread: Autocompact a backend db (2003)
2008-11-08, 04:11 #1
- Join Date
- Jul 2008
- Suffolk, United Kingdom
- Thanked 0 Times in 0 Posts
Autocompact a backend db (2003)
I am trying to write some code that will compact a backend database.
I want the on exit event of the front end to trigger the compact and repair if the backend is over a certain size. Below is some code I was using to get the file size of the front end before I split the db into FE and BE. This code originally turned the compact on exit on or off, however this then creates a new db normall db1.dbs on exit, meaning the other users links no longer work.
Dim DatabaseName As String
Dim fs, f, s, filespec
Dim strProjectPath As String, strProjectName As String
strProjectPath = Application.CurrentProject.Path
strProjectName = Application.CurrentProject.Name
filespec = strProjectPath & "" & strProjectName
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)
s = CLng(f.Size / 1000000) 'convert size of app from bytes to Mbís
If s > 100 Then
MsgBox "Database needs compacting"
I have been playing with this for a while now and the best I have got is to compact the backend it a different file name, I cant get it to compact onto itself.
Gerbil (AKA Kevin)
2008-11-09, 09:51 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: Autocompact a backend db (2003)
I must be dense - I don't see any instruction in your code that will actually compact any database, whether frontend or backend.
If compacting a database creates a database db1.mdb (surely not .dbs ?), you probable have Access 2003 SP3 and haven't applied the Access 2003 post-Service Pack 3 hotfix package: December 18, 2007 yet. You should do so!