Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    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.

    Function CompactDb()
    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"
    End If
    End Function

    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.

    Many thanks
    Gerbil (AKA Kevin)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts