[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

compacting access database

Hi! I've been trying to compact an ms access database, yet, without getting a separate destination (compacted) mdb file, i.e. I want to keep my old mdb file in compacted shape.

I've tried the following piece of code:

Dim jro As JRO.JetEngine = New JRO.JetEngine()
jro.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\database.mdb, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\database.mdb)

but it seems that jro.CompactDatabase method doesn't like the source and destination file to coincide...

Any ideas???

Thanx in advance!
Damian
0
dgaval
Asked:
dgaval
1 Solution
 
Da_WeaselCommented:
Use a different filename for the source file then after the operation completes, rename the old file to database.mbd.bak and rename the source file to database.mdb
0
 
supunrCommented:
Dim strTmpDB as String
Dim strSourceDB as String

Dim jro As JRO.JetEngine = New JRO.JetEngine()

' check if the temporary file exist. if so delete it
strTmpDBFile = "C:\Database.tmp"
if (Dir(strTmpDB) <> "") then
     Kill strTmpDB
end if

jro.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strSourceDB, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strTmpDB

' if database is successfully compacted, then rename it back
if (Dir(strTmpDB) <> "") then
     rename strTmpDB as strSourceDB
End if
0
 
supunrCommented:
actully change the line....

rename strTmpDB as strSouceDB

to

Name strTmpDB as strSouceDB

Good Luck!
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
supunrCommented:
mmm....forgot anther line....
here is the final code.  

Dim strTmpDB as String
Dim strSourceDB as String

Dim jro As JRO.JetEngine = New JRO.JetEngine()

' check if the temporary file exist. if so delete it
strTmpDB = "C:\Database.tmp"
strSourceDB = "C:\Database.MDB"
if (Dir(strTmpDB) <> "") then
     Kill strTmpDB
end if

jro.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strSourceDB, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strTmpDB

' if database is successfully compacted, then rename it back
if (Dir(strTmpDB) <> "") then
     Name strTmpDB as strSourceDB
End if
0
 
DabasCommented:
Alternatively, use DAO.

DBEngine.CompactDatabase("database.mdb")

Make sure the database is closed first.

Dabas

0
 
nazreCommented:
*********************************************************************
'----------------------------------------
'Name: CompactJetDatabase
'Assumtion: You have opened the database using "Conn" connection object
'----------------------------------------
Public Sub CompactJetDatabase(Location As String, _
    Optional BackupOriginal As Boolean = True)

On Error GoTo CompactErr
   
Dim strBackupFile As String
Dim strTempFile As String

'Check the database exists
If Len(Dir(Location)) Then

    ' If a backup is required, do it!
    If BackupOriginal = True Then
'        strBackupFile = GetTemporaryPath & "backup.mdb"
        strBackupFile = App.Path & "\Backup\backup.mdb"
        If Len(Dir(strBackupFile)) Then Kill strBackupFile
        FileCopy Location, strBackupFile
    End If

    ' Create temporary filename
   'strTempFile = GetTemporaryPath & "temp.mdb"
   strTempFile = App.Path & "\Backup\nTemp.mdb"
    If Len(Dir(strTempFile)) Then Kill strTempFile
    Conn.Close
    ' Do the compacting via DBEngine
    DBEngine.CompactDatabase Location, strTempFile
    ' Remove the original database file
    Kill Location

    ' Copy the temporary now-compressed
    ' database file back to the original
    ' location
    FileCopy strTempFile, Location

    ' Delete the temporary file
    Kill strTempFile
   
    '// Reopen the database
   
    Call OpenDatabase
       
    Msbox "Database Compact Successfully.", "Database", mbOkonly, mbSave

Else
    Msbox "Invalid Operation", "Database", mbOkonly, mbCritical
   
End If

Exit Sub

CompactErr:

Msbox Err.Number & " : " & Err.Description, "Fatal Error", mbOkonly, mbCritical
Exit Sub

End Sub



*********************************************************************
'----------------------------------------
'Name: GetTemporaryPath
'----------------------------------------
Public Function GetTemporaryPath()

Dim strFolder As String
Dim lngResult As Long

strFolder = String(MAX_PATH, 0)
lngResult = GetTempPath(MAX_PATH, strFolder)

If lngResult <> 0 Then
  GetTemporaryPath = Left(strFolder, InStr(strFolder, _
    Chr(0)) - 1)
Else
  GetTemporaryPath = ""
End If

End Function
*********************************************************************
Hope it helps you...
0
 
dgavalAuthor Commented:
Very helpful and prompt answer! Thanks!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now