repair and compact access 2000 DB

I am using the attached code to compact and repair my Access 2000 DB.  sConnSource and sConnDestination seem to be connection strings.  Where should I place or do I even need the "Jet OLEDB:Engine Type = 5" section?

connection string:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & My.Application.Info.DirectoryPath & "\Data2.mdb;Persist Security Info=True;Jet OLEDB:Database Password=xxxxx
Private Sub CompactDatabase(ByVal DBPATH As String)
        Dim jro As New JRO.JetEngine
        Dim sConnSource As String
        Dim sConnDestination As String
        Dim DBPATHONLY As String = DBPATH.Substring(0, DBPATH.LastIndexOf("\") + 1) 'My.Application.Info.DirectoryPath & "\Data2.mdb"
 
        'compact db to temp.mdb
        sConnSource = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & DBPATH & ";Jet OLEDB:Engine Type = 5"
 
        sConnDestination = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & DBPATHONLY & "temp.mdb;Jet OLEDB:Engine Type = 5"
 
         jro.CompactDatabase(sConnSource, sConnDestination)
         System.IO.File.Delete(DBPATH) 'delete db(System.IO.FIle)
         System.IO.File.Copy(DBPATHONLY & "temp.mdb", DBPATHONLY & "CheckRec.mdb")
         System.IO.File.Delete(DBPATHONLY & "temp.mdb")
 
 
    End Sub

Open in new window

NevSoFlyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PaulHewsConnect With a Mentor Commented:
I tested here against a sample with the following code, and it works as it's supposed to.  (Make sure to change your password in the code):
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim DBPath As String = "C:\temp\db1.mdb"
        Dim jro As New JRO.JetEngine
        Dim sConnSource As String
        Dim sConnDestination As String
        Dim DBPATHONLY As String = DBPATH.Substring(0, DBPATH.LastIndexOf("\") + 1) 'My.Application.Info.DirectoryPath & "\Data2.mdb"
 
        'compact db to temp.mdb
        sConnSource = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & DBPath & ";Jet OLEDB:Database Password=xxxxx"
 
        sConnDestination = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & DBPATHONLY & "temp.mdb;Jet OLEDB:Engine Type = 5;Jet OLEDB:Database Password=xxxxx"
 
        jro.CompactDatabase(sConnSource, sConnDestination)
        System.IO.File.Delete(DBPATH) 'delete db(System.IO.FIle)
        System.IO.File.Copy(DBPATHONLY & "temp.mdb", DBPATHONLY & "CheckRec.mdb")
        System.IO.File.Delete(DBPATHONLY & "temp.mdb")
    End Sub

Open in new window

0
 
PaulHewsCommented:
At the end of the destination only.

sConnSource = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & DBPATH
 
        sConnDestination = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & DBPATHONLY & "temp.mdb;Jet OLEDB:Engine Type = 5"
0
 
NevSoFlyAuthor Commented:
what about the password?
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
PaulHewsCommented:
Sorry, didn't realize there was a password:

sConnSource = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & DBPATH & ";Jet OLEDB:Database Password=xxxxx"
 
        sConnDestination = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & DBPATHONLY & "temp.mdb;Jet OLEDB:Engine Type = 5;Jet OLEDB:Database Password=xxxxx"
0
 
NevSoFlyAuthor Commented:
OK, now I get a COMException "Could not find installable ISAM.
0
 
NevSoFlyAuthor Commented:
I thank you for your help.  It seems that I have to reinstall MDAC 2.8 unfortunately for me I can get it to run on my system.  
0
All Courses

From novice to tech pro — start learning today.