asked on

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

game-master

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

https://www.experts-exchange.com/questions/20255768/Using-Access-repair-and-compact-ability-in-Delphi.html

morrisbo

ASKER

game-master

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

Morris

game-master

Oh yes...

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

i will going to check it again

game-master

game-master

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

game-master

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

ASKER CERTIFIED SOLUTION

morrisbo

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

game-master

Morris

im glad u solved your problem...

game-master