Problem with Compact BE From FE

[Santara]

Ken Sheridan was kind enough to give me some new code (Shown at end) for the
process of Compacting the Backend from the Frontend. Following his
instructions I did the following:

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 he show it, entered the path to the Back End
file, and I ran Debug ->Compile.

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

Here is the code provided by Ken:

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

 

[Ricky Hicks MVP]

My advice would be not to do this ....
You should "always" compact and repair the backend file from the machine
where the file resides .. and not from a remote machine.
Compacting the file from another machine is a "catastrophe waiting to
happen" ....
If the is any network blip during the process .. the result will more than
likely be a corrupted backend file that possibly may not be recoverable ..

You may want to give some serious thought about doing this before it bites
you ...

R. Hicks

 

[Santara]

Ricky,

The BackEnd and FrontEnd will be on the same machine.

The reason I split the database was to facilicate changes to the objects
since I am not at the office location where the database will reside.

The database is on a stand alone computer; no network involved. However,
the lady who is doing the data entry and will be running reports, is not
Access savy. I'm also trying to protect the Tables, and of course the other
objects as much as I can from inadvertent user error.

Should I still persue this? If so, can you suggest what I do next?

Thanks!

Santara

 

[Ricky Hicks MVP]

I'm sorry .. I assumed that the F drive was a mapped network drive ...

You posted: "I created the standard module and called it CompactDatabase."
.....
The module name can not be the same name as any object within the module ...
So rename your module to something different .. such as "basCompactDB"
(without the quotes) ...

R. Hicks

 

[Santara]

No problem...I'm learning, and that is something I wil remember for the future.

Now, I renamed the standard module, updated the Switchboard, tried it and...
same problem. "There was an error executing the command."

Any other suggestions?

I really appreciate your help!

Santara

 

[Tony Toews]

My advice would be not to do this ....
You should "always" compact and repair the backend file from the machine
where the file resides .. and not from a remote machine.
Compacting the file from another machine is a "catastrophe waiting to
happen" ....
If the is any network blip during the process .. the result will more than
likely be a corrupted backend file that possibly may not be recoverable ..

You may want to give some serious thought about doing this before it bites
you ...

Whereas I have compacted a 300 Mb BE over the network many, many
times. Also there are about no system administrators that would want
to allow you to install Access on a file server.

Sorry I gotta disagree with this recommendation.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm