Automate Compact and Repair

[Mark]

Hi all,
I have a Access 2002 FE/BE scenario where the BE is stored on a network
drive. Due to the amount of importing/deleting, the database bloats quite a
lot. UP until now, I have been booting the users out of the database and
manually compacting it. What I am trying to do is automate the process so I
don't have to worry about it. My VB skills are few and far between so this
is proving somewhat of a challenge. Because I am trying to compact the
backend from the frontend, no users can be logged in. I think I have solved
this problem by getting the FE to close if the current user isn't the last
person that logged in.

With only one person logged in, all the forms are closed which I thought
would close a connection to the backend allowing the BE to be compacted.
When I run the code, all work well up until the compact where I get an error
stating that I have the database opened exclusively. Am I going about this
the wrong way or is there a way to make sure the connection is closed before
the compact?

Here is the code I have come up with.

Private Sub compactandreope n_click()
Dim rstUser As New ADODB.Recordset 'Last User
Dim rstForm As New ADODB.Recordset 'Form list
Dim UserStr As String
rstUser.Open "qry_LastLogin" , CurrentProject. Connection, adOpenStatic,
adLockReadOnly
rstForm.Open "qry_FrmLis t", CurrentProject. Connection, adOpenStatic,
adLockReadOnly
UserStr = rstUser!userid
If Forms!frm_user_ login!txt_user_ id = UserStr Then 'current user matches
last user logged in
Do Until rstForm.EOF 'Close All forms

If SysCmd(acSysCmd GetObjectState, acForm, "" & rstForm!name) <> 0 Then
DoCmd.Close acForm, "" & rstForm!name
Debug.Print rstForm!name
End If
rstForm.MoveNex t
Loop
rstForm.Close
Set rstForm = Nothing

'With all forms closed, compact backend
DBEngine.Compac tDatabase "K:\Systems\New BE.mde",
"K:\Systems\bac kup\NewBE.mde" 'This is where the code fails

DoCmd.OpenForm "frm_User_login ", acNormal, , , acFormEdit
Else
MsgBox "The database just closed"
'DoCmd.Quit 'Close the database
End If
End Sub

TIA Mark

[Mark]

Hi All,
After a little playing around, I have managed a workaround. I found that the
connection does close once the forms are closed but not quick enough. I have
created an unbound form which is opened up once all other forms are closed.
On the timer event of this form which is set to 1 second, I have placed the
compact command. This gives enough time to close the connection and the
process completes as required.

If anyone has got any other suggestions on how to pause the code until the
connection is closed, it would be greatly appreciated.

Regards,

Mark
"Mark" <ma**********@n tlworld.com> wrote in message
news:4f******** ******@newsfe4-gui.ntli.net...
Hi all,
I have a Access 2002 FE/BE scenario where the BE is stored on a network
drive. Due to the amount of importing/deleting, the database bloats quite a
lot. UP until now, I have been booting the users out of the database and
manually compacting it. What I am trying to do is automate the process so I
don't have to worry about it. My VB skills are few and far between so this
is proving somewhat of a challenge. Because I am trying to compact the
backend from the frontend, no users can be logged in. I think I have solved
this problem by getting the FE to close if the current user isn't the last
person that logged in.

With only one person logged in, all the forms are closed which I thought
would close a connection to the backend allowing the BE to be compacted.
When I run the code, all work well up until the compact where I get an error
stating that I have the database opened exclusively. Am I going about this
the wrong way or is there a way to make sure the connection is closed before
the compact?

Here is the code I have come up with.

Private Sub compactandreope n_click()
Dim rstUser As New ADODB.Recordset 'Last User
Dim rstForm As New ADODB.Recordset 'Form list
Dim UserStr As String
rstUser.Open "qry_LastLogin" , CurrentProject. Connection, adOpenStatic,
adLockReadOnly
rstForm.Open "qry_FrmLis t", CurrentProject. Connection, adOpenStatic,
adLockReadOnly
UserStr = rstUser!userid
If Forms!frm_user_ login!txt_user_ id = UserStr Then 'current user matches
last user logged in
Do Until rstForm.EOF 'Close All forms

If SysCmd(acSysCmd GetObjectState, acForm, "" & rstForm!name) <> 0 Then
DoCmd.Close acForm, "" & rstForm!name
Debug.Print rstForm!name
End If
rstForm.MoveNex t
Loop
rstForm.Close
Set rstForm = Nothing

'With all forms closed, compact backend
DBEngine.Compac tDatabase "K:\Systems\New BE.mde",
"K:\Systems\bac kup\NewBE.mde" 'This is where the code fails

DoCmd.OpenForm "frm_User_login ", acNormal, , , acFormEdit
Else
MsgBox "The database just closed"
'DoCmd.Quit 'Close the database
End If
End Sub

TIA Mark