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

Automate Compact and Repair

P: n/a
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 compactandreopen_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_FrmList", 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(acSysCmdGetObjectState, acForm, "" & rstForm!name) <> 0 Then
DoCmd.Close acForm, "" & rstForm!name
Debug.Print rstForm!name
End If
rstForm.MoveNext
Loop
rstForm.Close
Set rstForm = Nothing

'With all forms closed, compact backend
DBEngine.CompactDatabase "K:\Systems\NewBE.mde",
"K:\Systems\backup\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
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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**********@ntlworld.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 compactandreopen_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_FrmList", 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(acSysCmdGetObjectState, acForm, "" & rstForm!name) <> 0 Then
DoCmd.Close acForm, "" & rstForm!name
Debug.Print rstForm!name
End If
rstForm.MoveNext
Loop
rstForm.Close
Set rstForm = Nothing

'With all forms closed, compact backend
DBEngine.CompactDatabase "K:\Systems\NewBE.mde",
"K:\Systems\backup\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

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.