Access 2007 Data Base Compact via VBA

[Giovanni Roi]

Enviroment: Access 2007

I try this code to compact a Access 2007 Data base Back-end:
----------------------------------------------------------------------
Function UTICompatta_DBADO()
Dim CONN As JRO.JetEngine
Dim CONN_Sorg As String
Dim CONN_Dest As String
'Screen.MousePointer = vbHourglass
On Error GoTo ConnectionError
Set CONN = New JRO.JetEngine

If Dir("C:\Test\New_test_be.Accdb") <> "" Then
Kill "C:\Test\New_test_be.Accdb"
End If

CONN_Sorg = "Provider=Microsoft.Jet.OLEDB.5.0;Data
Source=C:\Test\Test_be.Accdb;Database Password=test;"
CONN_Dest = "Provider=Microsoft.Jet.OLEDB.5.0;Data
Source=C:\Test\New_test_be.Accdb;Jet OLEDB:Engine Type=5;"

CONN.CompactDatabase CONN_Sorg, CONN_Dest
Set CONN = Nothing
If Dir("C:\Test\Test_be.Accdb") <> "" And
Dir("C:\Test\New_test_be.Accdb") <> "" Then
Kill "C:\Test\Test_be.Accdb"
FileCopy "C:\Test\New_test_be.Accdb", "C:\Test\Test_be.Accdb"
End If

On Error GoTo 0
'Screen.MousePointer = vbDefault
ExitError:
Exit Function
ConnectionError:
Screen.MousePointer = vbDefault
MsgBox " VB #" & Err.Number & " " & Err.Description & " " & Err.Source
Resume ExitError
End Function
------------
and I have this error message :
" VB # -2147221164 Interfaccia non registrata.Microsoft OLE DB Service
Component"

Can You help me to solve this problem?
Thank's

Giovanni Roi

 

[Douglas J. Steele]

Your connection strings are invalid.

Try:

CONN_Sorg = "Provider=Microsoft.Jet.OLEDB.12.0;Data
Source=C:\Test\Test_be.Accdb;Database Password=test;"
CONN_Dest = "Provider=Microsoft.Jet.OLEDB.12.0;Data
Source=C:\Test\New_test_be.Accdb;Database Password=test;"

 

[Giovanni Roi]

Dear Douglas,
Thank's for your support. I try to use Yur connection string but I have the
same error messagge. There are probably some Reference Library that I have to
add in my project? I have add only the "Microsoft Jet and Replication Objects
2,6 Library"

Can you help me.

Regards

Giovanni Roi

 

[Douglas J. Steele]

I suspect that that version may not be compatible with the new ACCDB file
format.

 

[Giovanni Roi]

Thank's Steel, You Know other methods, line of code or utility that I can use
to compact the ACCDB file?

Regards

Giovanni Roi

 

[Giovanni Roi]

Dear Steele, i try this code that working well:

Shell """C:\Programmi\Microsoft Office\Office12\msaccess.exe"" " & _
"""C:\test\test_be.accdb"" /compact"

the only question is that not include the password and the prompt ask me the
password. Can You help me to add the password to the above code?
Thank's

Giovanni Roi

 

[Douglas J. Steele]

Assuming your password is abcd, try:

Shell """C:\Programmi\Microsoft Office\Office12\msaccess.exe"" " & _
"""C:\test\test_be.accdb"" /compact /pwd abcd"

 

[Giovanni Roi]

Dear Steele,
I try this code and in this case the system ask me : user name and
password. I think that I need to pass other parameter.
Have You other idea?

Regards

Giovanni Roi

 

[Douglas J. Steele]

User name? That implies Access User-Level Security, which isn't supported in
the accdb file format.

Are you certain that your database is, in fact, an accdb, and that you
didn't just rename the extension from mdb?

 

[Giovanni Roi]

Dear Steele,
my data base is accdb format. I see also in Microsoft documentation that the
"pwd" parameter in the line of command is available only in the old "mdb"
format and that in Access 2007 isn't available this parameter in command
line.
Can You give me other possibility?
Thank's

Giovanni Roi

 

[Douglas J. Steele]

Sorry, I don't have Access 2007 installed on this machine, so I can't test
what options are available.