?
Solved

compacting access database

Posted on 2003-03-03
7
Medium Priority
?
219 Views
Last Modified: 2011-10-03
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
Comment
Question by:dgaval
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 3

Expert Comment

by:Da_Weasel
ID: 8061237
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
 
LVL 11

Expert Comment

by:supunr
ID: 8061429
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
 
LVL 11

Expert Comment

by:supunr
ID: 8061436
actully change the line....

rename strTmpDB as strSouceDB

to

Name strTmpDB as strSouceDB

Good Luck!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 11

Accepted Solution

by:
supunr earned 200 total points
ID: 8061458
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
 
LVL 27

Expert Comment

by:Dabas
ID: 8062372
Alternatively, use DAO.

DBEngine.CompactDatabase("database.mdb")

Make sure the database is closed first.

Dabas

0
 

Expert Comment

by:nazre
ID: 8062897
*********************************************************************
'----------------------------------------
'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
 

Author Comment

by:dgaval
ID: 8067928
Very helpful and prompt answer! Thanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month8 days, 3 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question