Compacting a linked database

  • Thread starter Thread starter Richardson
  • Start date Start date
R

Richardson

My Access database is split into a front end .mde and a .mdb backend. I
would like a function triggered with a command button in the front end that
compacts the backend database and then copies the backend to a new location
for backup.
Does anyone have a sample of code to handle these tasks when they are done
on an external database, and not the active database?

Thanks in advance,
Lori
 
Hi,

I do something similar in my application ...

You need to delete all linked tables associated with your
backend database from the .mde frontend, which will close
the backend database ... assuming your the only one using
it. You can walk through the currentDb.TableDefs
collection, looking at the Attribute field.

Something like the following will remove all linked tables
from the current (frontend) database.

Sub DeleteAllLinkedTables()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim i As Integer

Set db = CurrentDb
i = 0
Do
If i >= db.TableDefs.Count Then Exit Do
Set tdf = db.TableDefs(i)
If tdf.Attributes And dbAttachedTable Then
db.TableDefs.Delete tdf.Name
i = i - 1
End If
i = i + 1
Loop
End Sub


You can then use the CompactDatabase to compress, and
FileCopy to copy of the backend database.

When complete, you need to recreate the links to the
backend database tables. You can use the
Docmd.TransferDatabase method. to create the link.

HTH
Jim Ridenour
 
Back
Top