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

Selasa, 29 Januari 2013

RE: [MS_AccessPros] compact and repair own back end

Liz-

CompactDatabase is a method of the DBEngine object, not the Database object.

DBEngine.CompactDatabase Method (DAO)

Copies and compacts a closed database, and gives you the option of changing
its version, collating order, and encryption. (Microsoft Access workspaces
only). .

Syntax

expression.CompactDatabase(SrcName, DstName, DstLocale, Options, password)

expression An expression that returns a DBEngine object.

Parameters

Name Required/Optional Data Type Description

SrcName Required String Identifies an existing, closed
database. It can be a full path and file name, such as "C:\db1.mdb". If the
file name has an extension, you must specify it. If your network supports
it, you can also specify a network path, such as
"\\server1\share1\dir1\db1.mdb"

DstName Required String the file name (and path) of the
compacted database that you're creating. You can also specify a network
path. You can't use this argument to specify the same database file as
SrcName.

DstLocale Optional Variant A string expression that specifies a
collating order for creating DstName, as specified in Remarks. If you omit
this argument, the locale of DstName is the same as SrcName. You can also
create a password for DstName by concatenating the password string (starting
with ";pwd=") with a constant in the DstLocale argument, like this:
dbLangSpanish & ";pwd=NewPassword". If you want to use the same DstLocale
as SrcName (the default value), but specify a new password, simply enter a
password string for DstLocale: ";pwd=NewPassword"

Options Optional Variant A constant or combination of
constants that indicates one or more options, as specified in Remarks. You
can combine options by summing the corresponding constants.

password Optional Variant A string expression containing a
password, if the database is password protected. The string ";pwd=" must
precede the actual password. If you include a password setting in DstLocale,
this setting is ignored.

So, to do a compact, you create a new compacted copy with a different name,
then if that goes well, you delete the old one (Kill works) and rename the
new (Name statement). You cannot have the database open when you do this.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Liz Ravenwood
Sent: Tuesday, January 29, 2013 12:04 AM
To: MS_Access_Professionals@yahoogroups.com'
Subject: [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]

------------------------------------

Yahoo! Groups Links