Trying to compact access 2000 mdb in vb6 using ADO

I am attempting to add code to existing VB6 program to Compact (and Repair) an Access 2000 MDB using tthe Microsoft jet and Replication Objects library 2.6

I am getting an error message at the statement below jro.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;" stating that the argument iCompactDatabase s not optional.  It is probably in the syntax that I just cook booked code from an internet site and only changed the data base names.  I just can't see where the problem is and was hoping that someone more familiar with this task could help.

Morris

-----------------------------------------------------------------------------------------------------------------------
Private Sub mnuCompactMDB_Click()
   Dim strSource As String
   Dim strDestination As String
   
   Dim Response As Integer
   Response = MsgBox("Compact ARSSL.mdb ?", vbYesNo)
   If Response = vbNo Then Exit Sub
   MsgBox "NOTE - all other users must be completely out of the program for this operation"
   If Len(Dir$(App.Path & "\data\arssl.ldb")) > 0 Then
       MsgBox ("Database is in use")
       Exit Sub
     Else
       strSource = App.Path & "\data\arssl_data.mdb"
       strDestination = App.Path & "\data\arssl_data_new.mdb"
       Dim jro As jro.JetEngine
       Set jro = New jro.JetEngine
       jro.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;" _
        & "Data Source= & strSource" _
        & "Provider=Microsoft.Jet.OLEDB.4.0;" _
        & "Data Source= & strDestination" _
        & ";Jet OLEDB:Engine Type=5"
        
        Kill strSource
        Name strDestination As strSource
        Set jro = Nothing
     
   End If
End Sub

Open in new window

morrisboAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
morrisboConnect With a Mentor Author Commented:
game-master

I wanted to stay with ADO

Finally made it work.  I will post the code snipet for anyone else needing to perform a similar task.

Thanks for your help.  Really appreciate it.

Morris
Private Sub mnuCompactMDB_Click()
   
   If Len(Dir$(App.Path & "\data\arssl.ldb")) > 0 Then
       MsgBox ("Database is in use")
       Exit Sub
   End If
   
   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 = App.Path & "\data\ars_data.mdb"
   sDestination = App.Path & "\data\ars_data_temp.mdb"
    
   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
   
   Kill sSource
   Name sDestination As sSource
   Set JRO = Nothing
 
End Sub

Open in new window

0
 
game-masterCommented:

try this one...
very easy to understand and works fine..


http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_20255768.htm
0
 
morrisboAuthor Commented:
game-master

When I click on the link I get the message that the web site cannot be found.

Morris
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
game-masterCommented:

Oh yes...

i just opened this earlier..
im sorry...

i will going to check it again

game-master
0
 
game-masterCommented:


try this one..


     Public Sub CompactAccessDataBase(dbPath As String, NewPath As String)
         Dim acc As Object
         Set acc = CreateObject("Access.Application")
         acc.CompactRepair dbPath, NewPath
         Set acc = Nothing
     End Sub
     
     Sub test()
         CompactAccessDataBase "C:\YourDatabase.mdb", "C:\YourDatabase_NEW.mdb"
     End Sub
0
 
game-masterCommented:


this one also...

Add as reference to DAO 3.51 or later to your project and
use DBEngine.Compact database here's an example.


Sub CompactDatabaseX()

   Dim dbsNorthwind As Database

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")

   ' Show the properties of the original database.
   With dbsNorthwind
      Debug.Print .Name & ", version " & .Version
      Debug.Print "  CollatingOrder = " & .CollatingOrder
      .Close
   End With

   ' Make sure there isn't already a file with the
   ' name of the compacted database.
   If Dir("NwindKorean.mdb") <> "" Then _
      Kill "NwindKorean.mdb"

   ' This statement creates a compact version of the
   ' Northwind database that uses a Korean language
   ' collating order.
   DBEngine.CompactDatabase "Northwind.mdb", _
      "NwindKorean.mdb", dbLangKorean

   Set dbsNorthwind = OpenDatabase("NwindKorean.mdb")

   ' Show the properties of the compacted database.
   With dbsNorthwind
      Debug.Print .Name & ", version " & .Version
      Debug.Print "  CollatingOrder = " & .CollatingOrder
      .Close
   End With

End Sub


i hope this will give idea...


game-master
0
 
game-masterCommented:


Morris


im glad u solved your problem...

game-master
0
All Courses

From novice to tech pro — start learning today.