compact access2000 mdb using vb6 on windows 7 platform 64 bit

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.

morrisbo

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
Finish:
   TypeOp = "C"
   TransOK = UnMarkDB(TypeOp)

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

Select allOpen in new window