Access Actions from Excel

[neta]

Hi,
I am using the Excel VBA to create Access tables, but after a while the Access database becaome very big.

How can I Using the "Compact and repair" utility in the Access from the Excel VBA. I would appreciate your help in finding the exact coding fo that.

Thanks, neta.

[keepITcool]

Neta,

You cant do it using ADO, but you can with DAO.

Sub CompactDB()
Dim vFile, vTemp
Dim oDBE As Object

vFile = Application.GetOpenFilename("Database,*.mdb")
If vFile = False Then Exit Sub
vTemp = Left$(vFile, Len(vFile) - 3) & ".tmp"

If Len(Dir(vTemp)) Then Kill vTemp
Set oDBE = CreateObject("DAO.DBEngine.36")
oDBE.CompactDatabase vFile, vTemp
If Err = 0 Then
Kill vFile
Name vTemp As vFile
MsgBox "Compacted " & vFile, vbInformation
End If
Set oDBE = Nothing
End Sub

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


neta wrote :

>
> Hi,
> I am using the Excel VBA to create Access tables, but after a while
> the Access database becaome very big.
>
> How can I Using the "Compact and repair" utility in the Access from
> the Excel VBA. I would appreciate your help in finding the exact
> coding fo that.
>
> Thanks, neta.