Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB 6
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 29th, 2003, 05:11 PM
Registered User
Join Date: Aug 2003
Location: , , .
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

Reply With Quote
  #2 (permalink)  
Old August 29th, 2003, 06:29 PM
Authorized User
Join Date: Jun 2003
Location: Shirleysburg, PA, USA.
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.

Reply With Quote
  #3 (permalink)  
Old September 5th, 2003, 04:58 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
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 :)


Reply With Quote
  #4 (permalink)  
Old November 22nd, 2003, 03:57 PM
Registered User
Join Date: Jul 2003
Location: Edmonton, Alberta, Canada.
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.

Reply With Quote
  #5 (permalink)  
Old May 3rd, 2004, 06:28 PM
Registered User
Join Date: May 2004
Location: Madison, WI, USA.
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

Reply With Quote
  #6 (permalink)  
Old July 7th, 2004, 01:53 PM
Registered User
Join Date: Jul 2004
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

This link may help -
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
The Microsoft Jet database engine cannot open .mdb hunzian78@yahoo.com 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

All times are GMT -4. The time now is 08:55 PM.

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