Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB 6
Pro VB 6 For advanced Visual Basic coders working in version 6 (not .NET). Beginning-level questions will be redirected to other forums, including Beginning VB 6.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 29th, 2003, 05:11 PM
Registered User
Join Date: Aug 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Dwight
Default compacting an open mdb using VBA

I want to try to compact a database after I have deleted several tables and while the mdb is still open.

Some background to why I think I need to do this - I will be working with very large datasets and after the user selects the process to perform I am going to be exporting the result to excel and deleting the table(s) of calculations and attempt to compact the database so that the user can select another process without worrying about running out of space in the database.

Any assistance would help.

Resource Analyst

Old August 29th, 2003, 06:29 PM
Authorized User
Join Date: Jun 2003
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts

Hello Dwight

I don't know if I am exactly right on this but my partner and I have a program running on a server and several client machines using an access database and for us to do a backup and compact, we had to close the connection, do the backup and then reopen the connection.
Maybe this doesn't help at all but this seemed to be our only work around. if there is another way I also would be interested in trying it.

Old September 5th, 2003, 04:58 AM
joefawcett's Avatar
Wrox Author
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts

To compact an Access database you need the Jet Replication Objects. Plenty of examples on Google on how to use. I'm not sure if this works on open databases, it's a long time since I've used them :)


Old November 22nd, 2003, 03:57 PM
Registered User
Join Date: Jul 2003
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

I'm using Access 2002 and tried to program a compact & repair sequence from within the VBA code of the Access database I wanted to compacted. I had planned to use the Application.CompactRepair method, compact to a temporary MDB file, then switch the temporary MDB for the current but closed database if that was successful.

Unfortunately, the database would not shut down because it was the current database and contained the VBA code! (at least, I think that was the problem).

I now include an instruction to the user to use the Compact/Repair command from the Tools...Database Utilities pulldown. Wish I knew how that commmand works successfully! If you want to see my failing code, I can include it in another append.

Old May 3rd, 2004, 06:28 PM
Registered User
Join Date: May 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

I also couldn't find a way to compact an Access database while the database was open. However, I do have code that will compact an Access database from a VB application that is using the Access database for data. I don't know if it will help anyone or not. I didn't write this code. It was in an application that I inherited and now have to support.

Private Sub CompressB_Click()
  If MsgBox("Run compress now?", vbYesNo) = vbYes Then
    Screen.MousePointer = vbHourglass
    On Error Resume Next
    Kill gsDBPath + "stoppay2.mdb"
    On Error GoTo 0
    DBEngine.CompactDatabase gsDBPath + "stoppay.mdb", gsDBPath + "stoppay2.mdb"
    Kill gsDBPath + "stoppay.mdb"
    Name gsDBPath + "stoppay2.mdb" As gsDBPath + "stoppay.mdb"
    Screen.MousePointer = vbDefault
    MsgBox "Compress is complete."
  End If
End Sub

Old July 7th, 2004, 01:53 PM
Registered User
Join Date: Jul 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

This link may help -

Similar Threads
Thread Thread Starter Forum Replies Last Post
The Microsoft Jet database engine cannot open .mdb [email protected] ASP.NET 1.0 and 1.1 Professional 13 February 1st, 2007 12:47 PM
Open mde or mdb access file from vb6 gorgo Pro VB 6 1 February 20th, 2006 01:39 AM
Using VB6 SP2 to Open Access 2003 .mdb DWSTERRETT VB Databases Basics 1 June 13th, 2005 08:24 AM
Can't open .MDB database saxitalis C# 1 November 7th, 2003 04:18 PM
Beginning C# Databases - Can;t open .MDB file saxitalis All Other Wrox Books 0 November 6th, 2003 03:56 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.