Compact MS Access .accdb with Microsoft.ACE.OLEDB.12.0

[Rick Lipkin]

To All

I have upgraded all my Ms Access .Mdb 2003 tables to MS Access 2007 .Accdb. For the most part the transition was to load the Ace Client and a simple connection string change .. however, it appears you can not use the JRO object with the ACE client ..

Code: Select all  Expand view

cSource1  := cDEFA+"\Service.accdb"
cSource2  := cDEFA+"\ServiceNew.accdb"
cPROVIDER := "Microsoft.ACE.OLEDB.12.0"

cCn1 := 'Provider='+cPROVIDER+';Data Source='+cSOURCE1+';Jet OLEDB:Database Password='+xPASSWORD
cCn2 := 'Provider='+cPROVIDER+';Data Source='+cSOURCE2+';Jet OLEDB:Database Password='+xPASSWORD

msginfo( cCn1)
msginfo( cCn2 )

Try
  oJro:CompactDatabase(cCn1,cCn2 )
Catch
   cLine := "Compacting Database to "+chr(10)
   cLine += cSource2+"  Failed .. "+chr(10)
   oLine:ReFresh()
   SysReFresh()
   SysWait(5)
   oDlg:End()
   Return(.f.)
End Try
 

I found this VB code on a forum that the programmer said works with the Ace engine

Code: Select all  Expand view

Set reference to "Microsoft Office 12.0 Access database Engine".
Now you can use the following code to compress your ACCDB File.

Public Sub TestCompact()
    Dim acc As New DAO.DBEngine
   
    acc.CompactDatabase "C:\Source.accdb", "C:\Destination.accdb", , , "pwd=..."
   
End Sub

The access database 2007 can be compacted by the above said method, It is just that you have to add ";" before the pwd.
 

Does any one have any insight how to code this with Ado ?

Thanks
Rick Lipkin

[Rick Lipkin]

To All

Anyone know how to translate this to FWH ?

Thanks
Rick Lipkin

Code: Select all  Expand view

Set reference to "Microsoft Office 12.0 Access database Engine".
Now you can use the following code to compress your ACCDB File.

Public Sub TestCompact()
    Dim acc As New DAO.DBEngine
   
    acc.CompactDatabase "C:\Source.accdb", "C:\Destination.accdb", , , "pwd=..."
   
End Sub

The access database 2007 can be compacted by the above said method, It is just that you have to add ";" before the pwd.
 
 

[cnavarro]

Dear Rick
Try with this

Code: Select all  Expand view

               oCn := FW_OpenAdoConnection( ;
                     AllTrim( cFolderDatab ) + AllTrim( cFileTarget ) )
              /*
                      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ;
                       AllTrim( cFolderDatab ) + AllTrim( cFileTarget ) + ;
                       ";User ID=" + AllTrim( cUserName ) + ;
                       ";Password=" + AllTrim( cPassWord ) + ";" )
              */