Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Compacting Access2000 Databases using JRO.

Compacting Access2000 Databases using JRO.

Compacting Access2000 Databases using JRO.

I have an Access2000 database developed using ADO and I'm trying to compact the database using code.  The database has been split and it's the Back end which I'm trying to compact on exit.  So far every piece of code I've used has resulted in: You currently have the database open.(Longer error message but you get the idea).

No forms remain open and as far as I know, there is nothing using the back end database.

I've succeeded in this using a DAO database in Access97, but sadly the same code won't work in this instance, even with the relevant references.

Here is the code that I have used:(it fails during compact)
Function CompactDB()

Dim jetengine As JRO.jetengine
Dim fs As Object
Dim strSourceConnect As String
Dim strDestConnect As String
Dim strSourceDB As String
Dim strDestDB As String

'Build connection strings for SourceConnection and
'DestConnection arguments.
strSourceDB = "'W:\Sep_Reps\SEPData.mdb'"
strDestDB = "'W:\Sep_Reps\SEPOld.mdb'"

strSourceConnect = "Data Source=" & strSourceDB
strDestConnect = "Data Source=" & strDestDB & ";" & _
                "Jet OLEDB:Encrypt Database=False"

Set jetengine = New JRO.jetengine
Set fs = CreateObject("Scripting.FileSystemObject")

'Compact the database specified by the strSourceDB
'to the name and path specified by strDestDB
If fs.FileExists("W:\Sep_Reps\SEPOld.mdb") Then
    fs.DeleteFile ("W:\Sep_Reps\SEPOld.mdb")
End If
jetengine.CompactDatabase strSourceConnect, strDestConnect

fs.copyfile "W:\Sep_Reps\SEPOld.mdb", "W:\Sep_Reps\SEPData.mdb", True

Set jetengine = Nothing
Set fs = Nothing

End Function

Any help would be greatly appreciated.

RE: Compacting Access2000 Databases using JRO.

Your error says "it's open".
Are you trying to compact the Backend "_BE" in the "front end"?


Ask me how Bar-codes can help you be more productive.

RE: Compacting Access2000 Databases using JRO.

Okay, I've sussed it now..!!  Whereas Access97 would compact after a Docmd.close command (so that no forms are open), Access2000 seems to be a little tighter and I finally succeeded after moving the code to the On Unload event of the last form to close.

Thanks anyway.

RE: Compacting Access2000 Databases using JRO.

This was interesting if anyone is interested this is the code I use for compacting a database
Public Function CompactDatabase(strDatabaseName As String) As Boolean

    On Error GoTo 0
    Dim strPath As String
    Dim strPath1 As String
    Screen.MousePointer = vbHourglass

    'Save Paths for Database
    strPath = "c:\inetpub\wwwroot\chiroasp\" & strDatabaseName
    strPath1 = "c:\inetpub\wwwroot\chiroasp\" & "BackupOf" & strDatabaseName
    'Repair Database
  '  DBEngine.RepairDatabase strPath
    'Get Size of File Before Compacting
    strPathSize = GetFileSize(strPath)
    'Kill the file if it exists
    If Dir(strPath1) <> "" Then Kill strPath1
    'Compact Database to New Name
    DBEngine.CompactDatabase strPath, strPath1
    ''Kill the file if it exists
    If Dir(strPath) <> "" Then Kill strPath
    'Compact back to original Name
    DBEngine.CompactDatabase strPath1, strPath
    'Kill the file, no need to save it
    If Dir(strPath1) <> "" Then Kill strPath1
    'Get Size of File After Compacting
    strPathSize2 = GetFileSize(strPath)
    CompactDatabase = True
    'Display the Summary
  '  MsgBox UCase(strDatabaseName) & " compacted successfully." _
  '  & vbNewLine & vbNewLine & "Size before compacting:" & vbTab & strPathSize _
  '  & vbNewLine & "Size after compacting:" & vbTab & strPathSize2, vbInformation, "Compact Successful"

    Select Case err
        Case 0
        Case Else
        MsgBox err & ": " & Error, vbCritical, "CompactDatabase Error"
    End Select

Screen.MousePointer = vbNormal

End Function

RE: Compacting Access2000 Databases using JRO.

Thanks to both of you guys for the code examples for compacting.  How is the programmatic version different (in terms of results & best use)from just setting the "Compact on close" option?  

RE: Compacting Access2000 Databases using JRO.

If using a split database, the compact on close option will only compact the front end.  In most cases the front end will not contain any tables and so the size of the front end should not change, or any change in size will be minimal.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close