compact access2000 mdb using vb6 on windows 7 platform 64 bit

morrisbo used Ask the Experts™
I am having a problem with VB6 program running on a Windows 7 Home Premium 64 bit operating system

I am attempting to compact / repair an Access 2000 MDB using the Microsoft Jet and Replication Objects 2.6 Library.

I compile the VB6 program on an XP computer, then run the exe on the XP and on the Windows 7 notebook.

In design mode:

First I dim JRO as JRO.JetEngine
Then Set JRO = New JRO.JetEngine

Then execute the JRO.CompactDatabase method to compact the data base

For testing I exit the sub routine where the compact is executed immediately after the compact instruction.

The source mdb and destination mdb are in the same folder for testing purposes.

Executing the code Within the Windows 7 notebook the source file (MDB) is there unchanged and the destination file (MDB)
does not get created.

Running the exact same exe on the XP computer works correctly. I can see both the source and destination files and
can see that the destination file size has decreased from the source version.

I checked the notebook computer for the presence of the msjro.dll.  It is located at path :
c:\program files (x86)\common files\system\ado\msjro.dll

Any assistance with this problem is greatly appreciated.

Or, if there is another procedure for compacting an Access 2000 MDB on the Windows 7 64 bit platform that would work also on other platforms (XP, Vista)
that would be fine.  This is one of the last few problems that I need to solve in order to release the software to
the public.  We need for it to run on Windows 7 64 bit systems.  

This is a fairly sophisticated program that has many features and after extensive testing on the 64 bit system this is one of the last few issues we have run in to.

I have included a code snippet

Thanks for any assistance here.


Private Sub MnuFCompact_Click()

   On Error GoTo mnuCompactErr
   Dim TransOK As Boolean
   Dim Response As Integer
   Dim lresponse As Long
   lresponse = my_taskDialog(frmAction, App.Title, "Compact & Repair Data Base ?", "", xtpTaskButtonYes + xtpTaskButtonNo, 3)
   If lresponse = xtpTaskButtonNo Then Exit Sub
   lresponse = my_taskDialog(frmAction, App.Title, "All Work stations must be completely out of the ARS system before proceeding!!", "", xtpTaskButtonOk, 3)
   If Len(Dir$(gPathData$ & "\ARSDB.LDB")) > 0 Then
       lresponse = my_taskDialog(frmAction, App.Title, "Database is in use.  Unable to complete the operation.  Please try this operation later.", "", xtpTaskButtonOk, 3)
       Exit Sub
   End If
   TransOK = MarkDB
   Screen.MousePointer = vbHourglass
   Dim JRO As JRO.JetEngine
   Set JRO = New JRO.JetEngine

   Dim DB_sour As String
   Dim DB_dest As String
   Dim sSource As String
   Dim sDestination As String
   sSource = ModGlobal.gQuoteDatabaseName
   sDestination = ModGlobal.gPathData & "\compactMDB"
   Dim fso As New FileSystemObject

   If fso.FileExists(sDestination) = True Then
       Kill sDestination
   End If
   DB_sour = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
      & sSource
   DB_dest = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
      & sDestination & " ;Jet OLEDB:Engine Type=5"

   JRO.CompactDatabase DB_sour, DB_dest

GoTo Finish
   If fso.FileExists(sDestination) = True Then

      Kill sSource

      FileCopy sDestination, sSource

   End If
   Set JRO = Nothing
   Screen.MousePointer = vbDefault
   lresponse = my_taskDialog(frmAction, App.Title, "Compact complete.", "", xtpTaskButtonOk, 3)
   Dim CompactFile As String
   CompactFile = gPathData$ & "\" & "compact.txt"
   Open CompactFile For Output As #1
   Print #1, Date, Time
   Close #1
   Dim TypeOp As String
   TypeOp = "C"
   TransOK = UnMarkDB(TypeOp)

   Exit Sub
   MsgBox Err.Description & " in CompactMDB in Backup procedure"
End Sub

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014

Does it have to be done inside your application?  If not, you can use the JetComp utility from Microsoft.  It supports 97-2003 MDB formats.
Top Expert 2011
<<Or, if there is another procedure for compacting an Access 2000 MDB on the Windows 7 64 bit platform that would work also on other platforms (XP, Vista)
that would be fine.  This is one of the last few problems that I need to solve in order to release the software to
the public.>>

I use the Jet Compact 4 (EXE) utility from my Access front ends and VB apps. It can be called passing it a command line.

Jet Compact Utility Available in Download Center


    * Microsoft Office XP
    * Microsoft Office 2000
    * Microsoft Office 97
    * Microsoft Access 2002
    * Microsoft Access 2000
    * Microsoft Access 97
    * Microsoft Visual Basic 6.0
    * Microsoft Visual Basic 5.0
    * A Microsoft Office XP Developer run-time application that includes the run-time version of Microsoft Access 2002
    * A Microsoft Office Developer 2000 run-time application that includes the run-time version of Microsoft Access 2000
    * A Microsoft Office Developer Edition 97 run-time application that includes the run-time version of Microsoft Access 97
    * A Microsoft Visual Basic 6.0 run-time application that includes Microsoft Jet database engine 3.5 or 4.0
    * A Microsoft Visual Basic 5.0 run-time application that includes Microsoft Jet database engine 3.5
Top Expert 2011

Here is the comnd line options:

Jetcomp 4.0
Usage: JetComp
-src:{database name}
-dest:{database name}
-y{system database location}
-u{user name} (default=admin)
-p{user password}
-w{database password}
-x Encrypt Destination Database
-t Use Database Locale when copying data for text columns
-v4 Destination db is 4.x format (default)
-v3 Destination db is 3.x format
-L0 Db locale - None Use current language (default)
-L1 Db locale - English, German, French, and Portuguese
-L2 Db locale - Italian, and Modern Spanish
-L3 Db locale - Arabic
-L4 Db locale - Simplified Chinese
-L5 Db locale - Traditional Chinese
-L6 Db locale - Russian
-L7 Db locale - Czech
-L8 Db locale - Dutch
-L9 Db locale - Greek
-L10 Db locale - Hebrew
-L11 Db locale - Hungarian
-L12 Db locale - Icelandic
-L13 Db locale - Japanese
-L14 Db locale - Korean
-L15 Db locale - Norwegian and Danish
-L16 Db locale - Polish
-L17 Db locale - Slovenian
-L18 Db locale - Traditional Spanish
-L19 Db locale - Swedish and Finnish
-L20 Db locale - Thai
-L21 Db locale - Turkish
If no options are passed, GUI front end will start.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!




I think this might work.

I am not familiar with using the command line from within a VB6 program.

Would I constrict a bat format file and just shell to it from within the VB6 program.

I am just a novice here.

A sample code snippet would really help.

Most Valuable Expert 2012
Top Expert 2014
You can generally do this with a Shell command:

Shell "full path to jetcomp.exe" -src: "full path to your source database" -dest: "Full path to the destination database"


However, the issue may be UAC on the Windows 7 machine. Where is the .mdb file located on the machine? If you're trying to wite to one of the protected areas, you'll find that you cannot do this with UAC enabled.



Thanks,  using shell will be my next step.

I am assuming that jetcomp can recognize that the MDB is Access2000.  

I plan to do some studying on Windows 7.

I also believe the problem has something to do with UAC.

The files are in c:\program files (x86)\ars\data

Top Expert 2014

you should probably check with the JetComp help information to see what the default version is.  You can set the version with the -v parameter.
Most Valuable Expert 2012
Top Expert 2014

<The files are in c:\program files (x86)\ars\data>

This most likely your issue. On Windows 7 machines with UAC in place, Program Files is a protected directory. Your data should be in one of the "Data" locations on Win7, like ProgramDAta, Users\<SomeUser> or something of that nature. Note that a folder directly built on the root (i.e. "C:\MyData") can also be used, but that may change in the future.

As a side note: Program Files should NEVER have been used to house data, and MS has made a good move IMO in restricting that directory. I too was caught by this change, and had to rework nearly all of my apps to respond to this change. In the end, I had a more robust application, since my users could pick/choose where to house their data, and my apps now work on any supported environment.
Top Expert 2011

<<The files are in c:\program files (x86)\ars\data >>

That location is an is an issue.  I would never put data in the program files folders.

I totally agree with LSMConsulting's side note.


Changing the Install location for the Access 2000 MDB  away from \PROGRAM FILES did the trick.

The compact procedure using the the JRO.CompactDatabase method works now on the Windows 7 Professional platform.

I also looked at the JetComp utility documentation which indicated that it was possibly more powerful in repairing damaged Access MDB's so I also tested using Shell from within the VB6 program, using a Windows API code snippet I found on the Internet to pause execution until the compact either completed successfully or returned a flag if it failed.

The lesson I learned is that Windows 7 requires more fine tuning to accomodate "Legacy" 32 bit  apps like VB6 to run correctly on 7 64 bit..

Another problem was that not including all required dlls for the 3rd party controls imbedded in our application and running on Windows 7 just stops with the message "Program is stopping" without giving any indication of what the problem is.

Also, I have read up on the UAC feature of Windows 7.

I plan to look into what is involved in the option on Windows 7 running in XP mode and the "virtual machine" procedure just as insurance for our app. when it goes out the door.  

Anyways, thanks Guys for your help.  Doubt if I could ever have solved the problems without outside assistance.

250 points each to LSMConsulting & TheHiTechCoach.



Expert Exchange came through here.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial