jueves, 8 de septiembre de 2011

VBA Compact Access Database

There are some functions for compact an Access Database.

This one, requires to add a reference to Microsoft Jet y biblioteca Replication Objects X.X

Public Function CompactDB()

Dim Cjro As JRO.JetEngine
Set Cjro = New JRO.JetEngine
Dim cnn1 As String, cnn2 As String

' Cadena de conexión de origen
cnn1 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data source=" & CurrentDb.Name & ";" & _
"Jet OLEDB:Engine Type = 5;"

' Cadena de conexión de destino
cnn2 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data source=" & Left(CurrentDb.Name, Len(CurrentDb.Name) - 4) & "_compactada.mdb" & ";" & _
"Jet OLEDB:Engine Type = 5"

Cjro.CompactDatabase cnn1, cnn2

End Function

Another one that uses the commandbar control.

Function Compacta1()
On Error Resume Next
CommandBars. _
FindControl(Id:=2071). _
End Function

This ones makes the same than the previous, but taking the reference with Menu's text. (Take care, it differs with Access language)

'Spanish Access

Function Compacta2()
CommandBars("Menu bar"). _
Controls("Herramientas"). _
Controls("Utilidades de la base de datos"). _
Controls("Compactar y reparar base de datos..."). _
End Function

I hope it helps you.

No hay comentarios:

Publicar un comentario