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). _
accDoDefaultAction
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..."). _
accDoDefaultAction
End Function


I hope it helps you.

No hay comentarios:

Publicar un comentario