Compacting

[rcaesar]

I have a question on compacting.
Originally, I had one db. But when I would run some macros, I would have to compact again in midprocess. The macros run series of queries that bulid tables, and append to existing tables.

So, I broke it up into a frontend and backend. The backend still need to get compacted in midprocess, and the size is very nearly the same as the original db.

Is there a better way to approach this, or is there a better way to compact? Using Access 97 for this project.

Appreciate any help,

Thanks

 

[VBslammer]

You can compact the backend by retrieving the path from a known linked table's Connect property. A table that is linked from another Access database will have a Connect string that looks like this:
[tt]
;DATABASE=C:\Documents and Settings\UserName\My Documents\Databases\MyDatabase.mdb[/tt]

so if you strip out the first 10 characters you have the path. Here's an example function that will compact the backend. It's oversimplified but should work on a small system with only a couple of users. The Jet Programmer's Guide provides a more detailed solution that also checks to see whether the backend can be opened exclusively, which would be a good idea on larger production systems.

Public Function CompactBackEnd(ByVal strKnownLinkedTable As String) As Boolean
On Error GoTo ErrHandler
  Dim strBackend As String
  Dim strTemp As String
  Dim strBack As String
  
  strBackend = CurrentDb().TableDefs(strKnownLinkedTable).Connect
  If Len(strBackend) = 0 Then GoTo ExitHere
  
  strBackend = mid(strBackend, Len(";DATABASE=") + 1)
  If dir(strBackend) = "" Then GoTo ExitHere
  
  strTemp = Replace(strBackend, ".", "TMP.")
  strBack = Replace(strBackend, ".mdb", ".bak")

  DBEngine.CompactDatabase strBackend, strTemp
  
  If dir(strBack) <> "" Then
    Kill strBack
  End If
  
  Name strBackend As strBack
  Name strTemp As strBackend
  
  CompactBackEnd = True
ExitHere:
  Exit Function
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[rcaesar]

Thanks VBSlammer,

This is another great post. I will test it out this evening, and modify it to do what I have in mind.