By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,967 Members | 1,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,967 IT Pros & Developers. It's quick & easy.

Compact/Repair

P: n/a
Here is my situation:I have several Access database projects that all
keep their back-end data in a directory on a server. I would like to
have a small Access application that could open each .mdb file, then
compact and repair it, with no user intervention other than launching
the application. Thank you for any help.

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Sh**********@KidNet.com wrote:
Here is my situation:I have several Access database projects that all
keep their back-end data in a directory on a server. I would like to
have a small Access application that could open each .mdb file, then
compact and repair it, with no user intervention other than launching
the application. Thank you for any help.


When compacting in code you can't compact a file onto itself so you also have to
include some renaming statements.

dbEngine.CompactDatabase "PathToFirstDatabaseSource"
"PathToFirstDatabaseDestination"

If Dir("PathToFirstDatabaseDestination") <> "" Then
Kill "PathToFirstDatabaseSource"
Name "PathToFirstDatabaseDestination" As "PathToFirstDatabaseSource"
End If

(repeat for as many files as you need)

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #2

P: n/a

<Sh**********@KidNet.com> wrote in message
news:43695fa3.3348843@news-server...
Here is my situation:I have several Access database projects that all keep their back-end data in a directory on a server. I would like to
have a small Access application that could open each .mdb file, then
compact and repair it, with no user intervention other than launching the application. Thank you for any help.


Sheldon Mopes,

There is the Jet Compact Utility,
http://support.microsoft.com/kb/295334.

JETCOMP.exe.

This can be run from .bat, vbScript, and Windows Shell Script files.
Sincerely,

Chris O.
Nov 13 '05 #3

P: n/a
It used to be unnecessary to "open" an mdb file in order to compact it.
My guess is that this is still the case. This is old code that compacts
the mdb file of linked tables.

Does it still work? I don't know.
Are you welcome to modify or use any part of it to meet your needs?
Sure.
Will you have to fix up line breaks inserted by the various news
servers/clients through which this will pass. Prabably!
Will you post triumphantlly, "Doesn't work!". Depends on you.

Public Sub CompactAttachedTableMDBS()
'will fail if no reference to DAO
On Error GoTo CompactAttachedTableMDBSErr
Dim r As DAO.Recordset
Dim s As String
If Forms.Count Or Reports.Count Then
MsgBox "Please, close all forms and reports, and retry.",
vbExclamation, "FFDBA"
Else
s = "SELECT Distinct CStr(DataBase) AS db" _
& " FROM MSysObjects WHERE Type=" & AttachedTable
With DBEngine(0)(0)
.TableDefs.Refresh
Set r = .OpenRecordset(s)
With r
Do While Not .EOF
If DoesFileExist1997(!db) Then
If CanBeOpenedExclusively(!db) Then
Shell SysCmd(acSysCmdAccessDir) &
"MsAccess.Exe " & """" & !db & """" & " /compact"
If Notify Then
MsgBox "Successfully Compacted" _
& vbCrLf _
& !db & "." _
, vbInformation, "FFDBA"
End If
Else
MsgBox "Can't compact" _
& vbCrLf _
& !db & "." _
& vbCrLf _
& "Database seems to be opened by another
user.", vbExclamation, "FFDBA"
End If
Else
MsgBox "Can't compact" _
& vbCrLf _
& !db & "." _
& vbCrLf _
& "Database seems to have been moved or
deleted.", vbExclamation, "FFDBA"
End If
.MoveNext
Loop
.Close
End With
End With
End If
CompactAttachedTableMDBSExit:
Set r = Nothing
Exit Sub
CompactAttachedTableMDBSErr:
With Err
MsgBox .Description, vbCritical, "Error: " & .Number
End With
Resume CompactAttachedTableMDBSExit
End Sub

Private Function CanBeOpenedExclusively(ByVal FullPath As String) As
Boolean
Dim d As Database
Dim p As PrivDBEngine
Set p = New PrivDBEngine
On Error Resume Next
Set d = p(0).OpenDatabase(FullPath, True)
CanBeOpenedExclusively = Not (d Is Nothing)
p(0).Close
Set d = Nothing
Set p = Nothing
End Function

Public Function DoesFileExist1997(ByVal FilePath As String) As Boolean
On Error GoTo DoesFileExist1997Err
GetAttr FilePath
DoesFileExist1997 = True
DoesFileExist1997Exit:
Exit Function
DoesFileExist1997Err:
With Err
If .Number = FileNotFoundErrNumber Then
DoesFileExist1997 = False
Else
MsgBox .Description, vbCritical, "Error Number: " & .Number
End If
End With
Resume DoesFileExist1997Exit
End Function

Nov 13 '05 #4

P: n/a
Sh**********@KidNet.com wrote:
Here is my situation:I have several Access database projects that all
keep their back-end data in a directory on a server. I would like to
have a small Access application that could open each .mdb file, then
compact and repair it, with no user intervention other than launching
the application. Thank you for any help.


Why use an Access application to do this?

The following (untested) batch file should compact/repair all .mdb files
found at or below the current folder. Put it in a scheduled task to run
at midnight or whatever.

=== begin NT CMD code
set access=[fully qualified path to your Access.exe]
for /f "tokens=1 delims=" %%a in ('dir *.mdb /b /s') do ("%access% %%a
/compact")
=== end code
--
Smartin
Nov 13 '05 #5

P: n/a
Which version of Access are you running?
I created an Access application that I can MDB Maintenance that will
compact and repair any mdb file that you have added to it's list.
It has a search capability to help you locate and then select some or
all of the files found. You can then run the Compact and Repair on all
of the files, or just one.
I'm including a link to my Database Maintenance program for you to
download for free if you wish. It is an MDE file (which is an Access
Database file that cannot be modified). It allows you to search for mdb
files and select the ones you wish to add to your list of files to
compact and repair. You then have the option to run the C&R on one or
all of the files with the click of a button. The link is
http://www.palssons.biz/DatabaseMaint.htm. I hope you find it helpful.

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.