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.


--
neta
------------------------------------------------------------------------
neta's Profile: http://www.excelforum.com/member.php...fo&userid=7624
View this thread: http://www.excelforum.com/showthread...hreadid=495435

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.