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

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

Postby Rick Lipkin » Tue Jun 02, 2020 7:08 pm

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
User avatar
Rick Lipkin
 
Posts: 2663
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

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

Postby Rick Lipkin » Thu Jun 04, 2020 12:06 pm

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.
 
 
User avatar
Rick Lipkin
 
Posts: 2663
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

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

Postby cnavarro » Thu Jun 04, 2020 8:45 pm

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 ) + ";" )
              */



 
Cristobal Navarro
Hay dos tipos de personas: las que te hacen perder el tiempo y las que te hacen perder la noción del tiempo
El secreto de la felicidad no está en hacer lo que te gusta, sino en que te guste lo que haces
User avatar
cnavarro
 
Posts: 6541
Joined: Wed Feb 15, 2012 8:25 pm
Location: España


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: No registered users and 58 guests