Access 2007 Data Base Compact via VBA

  • Thread starter Thread starter Giovanni Roi
  • Start date Start date
G

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
 
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;"
 
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
 
I suspect that that version may not be compatible with the new ACCDB file
format.
 
Thank's Steel, You Know other methods, line of code or utility that I can use
to compact the ACCDB file?

Regards

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
 
Assuming your password is abcd, try:

Shell """C:\Programmi\Microsoft Office\Office12\msaccess.exe"" " & _
"""C:\test\test_be.accdb"" /compact /pwd abcd"
 
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
 
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?
 
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
 
Sorry, I don't have Access 2007 installed on this machine, so I can't test
what options are available.
 
Back
Top