compact/repair/backup with VBA

[Toxalot]

I want to compact/repair/backup the backend database from the frontend
database using VBA code. I'm using Access 2003 and the application
will be used with the runtime.

I see several options for RunCommand, but they aren't explained.

Can anyone give me some sample code?

Should the compact/repair/backup be done in a certain order?

Jennifer

 

[Guest]

Hi

You could do this from an OnClick - something like

strDBtoBackUp = "Path to DB"
DBEngine.CompactDatabase strDBtoBackUp

eg (air code - not checked - no time at the moment but this is about right ??)

Private Sub ButtonName_Click()
Dim strDBtoBackUp As String
strDBtoBackUp = "C:\Documents and Settings\My Documents\DBName.mdb"
DBEngine.CompactDatabase strDBtoBackUp
End Sub


change the path to what it is.

 

[Toxalot]

This DAO, correct? So far I've been using ADO for everything. Is there
a way to do it with ADO?

Jennifer

 

[biganthony via AccessMonster.com]

Hi,

Various code I have seen on the web:

To compact: A2003

'run command to compact database.
CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction


To backup: A2003

CommandBars("Menu Bar").Controls("Tools"). _
Controls("Database utilities").Controls("Back up database..."). _
accDoDefaultAction

Function to compact:

Public Function AutoCompactApplication()

Dim s
Dim strProjectPath As String, strProjectName As String

strProjectPath = Application.CurrentProject.Path
strProjectName = Application.CurrentProject.Name
filespec = strProjectPath & "\" & strProjectName

s = CLng(FileLen(filespec) / 1000000) 'convert size

If s > 15 Then 'edit the 15 (Mb's) to the max size you want
to allow your app to grow.

Application.SetOption ("Auto Compact"), 1 'compact app

Else

Application.SetOption ("Auto Compact"), 0 'no don't compact app

End If

End Function

Regards
Anthony

 

[Toxalot]

I've seen this code as well, but since that just opens the item from
the menu bar it will be compacting/backing up the frontend not the
backend.

Jennifer

 

[Guest]

I just did a quick google - and came accross this from Doug Steel

http://www.dbforums.com/archive/index.php/t-779964.html

Like I said I think you're better just using an OnClick (works for me and my
stuff on most of my DBs that are not stored on the main server)

I would use the doa DBEngine.CompactDatabase - but then I may bit a little
lazy.


--
Wayne
Manchester, England.

This DAO, correct? So far I've been using ADO for everything. Is there
a way to do it with ADO?

Jennifer

 

[Ron Hinds]

This DAO, correct?

Yes, it is.

So far I've been using ADO for everything. Is there
a way to do it with ADO?

There is no way to do it from ADO.

One problem with the other poster's code is that in order to Compact and
Repair the database (BTW, in Access 2000 and on those two functions are
combined in the CompactDatabase method) the .MDB you are trying to compact
must be closed, with no other users in it. If you are doing it from your FE
this means that all forms that reference the BE must be closed, and no other
users can be in the BE, either. In other words, the form you are calling
CompactDatabase from must not be bound to the BE in any way. If you have
ever checked out various performance-related issues in these NG's, you
probably are using an always open bound form to increase performance to your
BE. Those would need to be closed, also. There is a DLL you can incorporate
in your application (available from Microsoft - search for JetUtils) that
has functions that will tell you if all usres are out of the database, and
if not, which users are still in. Here is a sample code snippet (not
complete) for using MSLDBUSR.DLL:

Declare Function LDBUser_GetUsers Lib "MSLDBUSR.DLL" (lpszUserBuffer() As
String, ByVal lpszFilename As String, ByVal nOptions As Long) As Integer
Declare Function LDBUser_GetError Lib "MSLDBUSR.DLL" (ByVal nErrorNo As
Long) As String

Public Const OptAllLDBUsers = &H1
Public Const OptLDBLoggedUsers = &H2
Public Const OptLDBCorruptUsers = &H4
Public Const OptLDBUserCount = &H8
Public Const OptLDBUserAuthor = &HB0B

Check_Again:
ReDim strUsers(1)
lRet = LDBUser_GetUsers(strUsers, strDBFile, OptLDBLoggedUsers)
If lRet < 0 Then
If lRet <> -2 And lRet <> -14 Then
strError = LDBUser_GetError(lRet)
MsgBox strError, vbExclamation
LogError Me.Name, lRet, strError, "MSLDBUSR.DLL"
Exit Function
End If
ElseIf lRet > 0 Then
'Users are still connected
strMsg = lRet & " users are still connected!" & vbCrLf & _
"Please make sure these users are logged off:" & vbCrLf
For L = LBound(strUsers) To UBound(strUsers)
strMsg = strMsg & strUsers(L) & vbCrLf
Next
strMsg = strMsg & "Click Retry when ready or Cancel to abort."
iResp = MsgBox(strMsg, vbRetryCancel Or vbInformation)
If iResp = vbRetry Then
GoTo Check_Again
Else
Exit Function
End If
End If

Jennifer