ACCESS FEED: [MS_AccessPros] compact and repair own back end

Senin, 28 Januari 2013

[MS_AccessPros] compact and repair own back end

Pros, I'm trying to do a compact and repair a db's back end and have everything set so that hopefully it is not being opened by something else and then this is the module that is supposed to actually do the deed.

Public Sub CompactNRepairBE(strForm As String)
Dim lngFullSize As Long
Dim strsql As String
Dim db As DAO.Database
Dim strBE As String
Dim strUser As String
strUser = GetLoginName()
st0: ' determine back end path
strBE = "\\ws0415\tudb$\ECR_be-TEST.accdb"
st1: ' get previous file size
' MsgBox ("...performing necessary maintenance... This will take a few seconds.")
lngFullSize = CDec(FileLen(strBE)) / 1024
strsql = "INSERT INTO DBMaint VALUES (#" & Now() & "#, 'Pre-compact: " & strUser & "', " & lngFullSize & ");"
CurrentDb.Execute (strsql)
st2: ' close the current form
DoCmd.Close acForm, strForm

st3: ' open the back end
Set db = DBEngine(0).OpenDatabase(strBE)
'--->>>

db.CompactDatabase

st4: ' close the back end (compact and repair upon close)
db.Close
Set db = Nothing
lngFullSize = CDec(FileLen(strBE)) / 1024
strsql = "INSERT INTO DBMaint VALUES (#" & Now() & "#, 'Post-compact: " & strUser & "', " & lngFullSize & ");"
CurrentDb.Execute (strsql)
st5: ' reopen the current form
CurrentDb.Execute "INSERT INTO LogStats (UserLoggedIn, TimeLoggedIn, WhichDatabase) " & _
"VALUES('" & strUser & "-compacted" & "', #" & Now() & "#, 'ECR DB')"
DoCmd.OpenForm strForm
End Sub

Unfortunately, the db.compactdatabase doesn't work. It says that the method or the data member is not found and won't even compile.

What am I doing wrong?

Respectfully,
Liz Ravenwood
Programmer/Analyst
Super First Class Products
B/E Aerospace
O: 1.520.239.4808
www.beaerospace.comhttp://www.beaerospace.com>

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

[Non-text portions of this message have been removed]