Compact BE from FE

[Santara]

I found a post from 10/22/2004 that gave the code (shown at end) for this
posted by Doug Steele.

I am new to using VBA code, so my question is "Where" do I put this code.
What do I do to get this to work? I am using the built in switchboard and
would like to have it run from there. If I use the switchboard, do I create
an unbound form with a command button to activate the code? If so, does that
make it an Event Procedure?

OR.... is there a way to just use this code to compact and repair the front
end on the close of the database. There is a single user for the database,
so we don't have the problem of someone else using the table when she closes
out.

Thanks for the help!

Santara

--------------------------------------------------
Here is the Code copied from previous post:

Function CompactDatabase(DatabaseName As String) As Boolean
' Renames the existing backend database from .MDB to .BAK
' Compacts the backup copy to the "proper" database
'
' Returns True if successful, False otherwise

On Error GoTo Err_CompactDatabase

Dim booStatus As Boolean
Dim strBackupFile As String

booStatus = True

' Make sure that DatabaseName exists
If Len(Dir$(DatabaseName)) > 0 Then

' Figure out what the backup file should be named
If StrComp(Right$(DatabaseName, 4), ".mdb", vbTextCompare) = 0 Then
strBackupFile = Left$(DatabaseName, Len(DatabaseName) - 4) &
".bak"

' Determine whether the backup file already exists,
' and delete it if it does.
If Len(Dir$(strBackupFile)) > 0 Then
Kill strBackupFile
End If

Name DatabaseName As strBackupFile

' Do the actual compact
DBEngine.CompactDatabase strBackupFile, DatabaseName
End If

End If

End_CompactDatabase:
CompactDatabase = booStatus
Exit Function

Err_CompactDatabase:
booStatus = False
MsgBox Err.Description & " (" & Err.Number & ")", _
vbOkOnly + vbCritical
Resume End_CompactDatabase

End Function

 

[Ken Sheridan]

Paste the function into any standard module. You can call it from your
switchboard by creating a new switchboard item using the switchboard manager.
For the item's 'command' select Run Code and then enter the name of the
function, CompactDatabase. However, the function as it stands expects the
path to the back end database to be passed to it as its argument.
Unfortunately you can't do this when calling a function from the switchboard
as far as I'm aware. You can get round this by amending the function so the
path is a constant in the function:

Function CompactDatabase() As Boolean
' Renames the existing backend database from .MDB to .BAK
' Compacts the backup copy to the "proper" database
'
' Returns True if successful, False otherwise

On Error GoTo Err_CompactDatabase

Const conBackEndFile = "F:\SomeFolder\SomeFile.mdb"
Dim booStatus As Boolean
Dim strBackupFile As String

booStatus = True

' Make sure that Back End File exists
If Len(Dir$(conBackEndFile)) = 0 Then
MsgBox "File " & conBackEndFile & " not found.", vbExclamation,
"Error"
boo Status = False
Else
' Figure out what the backup file should be named
If StrComp(Right$(conBackEndFile, 4), ".mdb", vbTextCompare) = 0 Then
strBackupFile = Left$(conBackEndFile, Len(conBackEndFile) - 4) &
".bak"

' Determine whether the backup file already exists,
' and delete it if it does.
If Len(Dir$(strBackupFile)) > 0 Then
Kill strBackupFile
End If

Name conBackEndFile As strBackupFile

' Do the actual compact
DBEngine.CompactDatabase strBackupFile, conBackEndFile
End If

End If

End_CompactDatabase:
CompactDatabase = booStatus
Exit Function

Err_CompactDatabase:
booStatus = False
MsgBox Err.Description & " (" & Err.Number & ")", _
vbOkOnly + vbCritical
Resume End_CompactDatabase

End Function

Using a constant for the path to the back end is not ideal as its location
might be changed, so I've added a little code in the above to deal with this.
There are ways you can locate the back end programmatically, but it might
confuse you at this stage if we go down that road. One thing you will have
to make sure of, however, is that the Switchboard Items table is in the front
end, not the back end as this table will be accessed by code in the
switchboard form's class module and consequently would prevent the back end
being compacted if the table were located within it.

 

[Santara]

Ken,

Ok, first, I put Switchboard Items Table into the Front End.

Second, I created the standard module and called it CompactDatabase.

I imported the new code as you show it, entered the path to the Back End
file, and I ran Debug ->Compile from the menu.

Third, I created a new switchboard item called Compact Database Tables with
a Command of Run Code and the Function Name of CompactDatabase.

Finally, I went out to test it from the switchboard, and ran into a problem.
I got a critical message stating "There was an error executing the command."

And here....I'm lost. I don't know enough about VBA to figure out where the
problem is, or what to do next.

What do I do now?

Thanks for your help!

Santara

 

[Santara]

Ken,

Opps.... It's even worse...The whole switchboard doesn't work anymore.

It must have to do with moving the Table from the BackEnd to the FrontEnd.

I looked at the table and the form, but can't figure out what went wrong.
The switchboard opens, but won't respond. I tried reentering the items on
the Switchboard Manager, but that didn't work either.

What should I do... By the way...I have a backup that I could use to start
over. The way I took the Switchboard table from the FE to the BE was by drag
and drop a copy to overwrite the linked table, then deleted the table from
the BE.

Thanks for the help!

Santara

 

[Santara]

Ken,

Okay I fixed the majority of the switchboard problem. It appears the form
was corrupted. So I started over and this time used File->Get External Data
to get the table.

However, I still have the same problem with the CompactDatabase item.

Thanks,

Santara

 

[Ken Sheridan]

Don't call the module CompactDatabase; call it anything you like, but not the
same name as the function or confusion will be caused. I always prefix
module names with 'bas'.

Checking the code I did find one or two small mistakes, which I've corrected
in the following. Watch out that between here and it getting into your
module specious carriage returns/line feeds don't creep in (I found a couple
of these). If when you paste it into the module you click the Compile button
on the VBA toolbar these should be identified and you can delete the unwanted
CR/LFs:

Function CompactDatabase() As Boolean
' Renames the existing backend database from .MDB to .BAK
' Compacts the backup copy to the "proper" database
'
' Returns True if successful, False otherwise

On Error GoTo Err_CompactDatabase

Const conBackEndFile = "F:\SomeFolder\SomeFile.mdb"
Dim booStatus As Boolean
Dim strBackupFile As String

booStatus = True

' Make sure that Back End File exists
If Len(Dir$(conBackEndFile)) = 0 Then
MsgBox "File " & conBackEndFile & " not found.", vbExclamation,
"Error"
booStatus = False
Else
' Figure out what the backup file should be named
If StrComp(Right$(conBackEndFile, 4), ".mdb", vbTextCompare) = 0 Then
strBackupFile = Left$(conBackEndFile, Len(conBackEndFile) - 4) &
".bak"

' Determine whether the backup file already exists,
' and delete it if it does.
If Len(Dir$(strBackupFile)) > 0 Then
Kill strBackupFile
End If

Name conBackEndFile As strBackupFile

' Do the actual compact
DBEngine.CompactDatabase strBackupFile, conBackEndFile
End If

End If

End_CompactDatabase:
CompactDatabase = booStatus
Exit Function

Err_CompactDatabase:
booStatus = False
MsgBox Err.Description & " (" & Err.Number & ")", _
vbOKOnly + vbCritical
Resume End_CompactDatabase

End Function