Compacting an Access 2003 Database

  • Thread starter Thread starter Wayne Wengert
  • Start date Start date
W

Wayne Wengert

I am using VB Express 2005 and in the code I want to compact the active
database. I am using the code below (I've left out the Try/Catch code for
clarity). I get an error on the File.Move that complains that the
file(DatabaseName) is in use by another application? Since I close the
connection, I don't understand this error?


==================== Code ===============
.....
myConn.Close()

CompactDB(myDBFilePath)

.....

Public Function CompactDB(ByVal DatabaseName As String)

Dim db As dao.DBEngine

Dim AppPath = System.AppDomain.CurrentDomain.BaseDirectory()

Dim sUFN As String

Dim sFN As String = System.IO.Path.GetFileNameWithoutExtension(DatabaseName)

sUFN = AppPath & "\" & sFN & Format(Now, "MMddyyyyHHmmss.mdb")

File.Move(DatabaseName, sUFN)

db = New dao.DBEngine()

db.CompactDatabase(sUFN, AppPath & "\" & sFN)

CompactDB = True

End Function
 
I had one situation where a datareader was not being closed. This was
causing the problem.

Wayne
 
¤ I am using VB Express 2005 and in the code I want to compact the active
¤ database. I am using the code below (I've left out the Try/Catch code for
¤ clarity). I get an error on the File.Move that complains that the
¤ file(DatabaseName) is in use by another application? Since I close the
¤ connection, I don't understand this error?
¤
¤
¤ ==================== Code ===============
¤ ....
¤ myConn.Close()
¤
¤ CompactDB(myDBFilePath)
¤
¤ ....
¤
¤ Public Function CompactDB(ByVal DatabaseName As String)
¤
¤ Dim db As dao.DBEngine
¤
¤ Dim AppPath = System.AppDomain.CurrentDomain.BaseDirectory()
¤
¤ Dim sUFN As String
¤
¤ Dim sFN As String = System.IO.Path.GetFileNameWithoutExtension(DatabaseName)
¤
¤ sUFN = AppPath & "\" & sFN & Format(Now, "MMddyyyyHHmmss.mdb")
¤
¤ File.Move(DatabaseName, sUFN)
¤
¤ db = New dao.DBEngine()
¤
¤ db.CompactDatabase(sUFN, AppPath & "\" & sFN)
¤
¤ CompactDB = True
¤
¤ End Function
¤

Try using ADO/JRO instead:

http://support.microsoft.com/default.aspx?scid=kb;en-us;306287


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Paul;

I appreciate the response but what do I gain by using the ADO approach vs
the DAO approach? I know it is the "newer" technology but so many times it
seems like there is change for the sake of change?

Wayne
 
You're right -- there's not a whole lot to be gained in this instance
since DAO does everything you need it to when only Jet is involved. I
suspect that what is happening is that even though the connection is
closed, it isn't being released. You can check this out by putting a
break point in your code after the close and take a look at the file
system to see if the .ldb file is still there.

Mary
 
¤ Paul;
¤
¤ I appreciate the response but what do I gain by using the ADO approach vs
¤ the DAO approach? I know it is the "newer" technology but so many times it
¤ seems like there is change for the sake of change?
¤
¤ Wayne

DAO is a monolithic object library and limited to a single threaded model. While that may not be the
source of your problem I'm recommending you try JRO since you are having a problem, not simply
because it's newer.

You may also want to use System.Runtime.InteropServices.Marshal.ReleaseComObject to try and release
any existing DAO objects. Of course the first thing you may want to try is explicitly closing the
database using Close method.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top