473,322 Members | 1,409 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Automate Compact and Repair

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
1 4923
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: KEVIN97810 | last post by:
Hello to all, Assume my mdb name is Cust.MDB. I want to compact & repair the same Cust.MDB when the user exit the program. Is there a way you can do it in code behind the EXIT button. I am...
3
by: Paolo | last post by:
Hi, I am trying to compact and repair my database, however every time I try it comes up a message saying: Table: "TempMSysAccessObject already exists", whenever I try to look for this table I...
4
by: Wayne | last post by:
Does "Compact On Close" do a "Compact and Repair" or just a compact. Is a compact necessary (or at least a good idea) on a regular basis, say weekly, for a database that has several hundred records...
4
by: Andy Davis | last post by:
Is it possible to create a button with code to carry out the a weekly compact and repair of database and backup? My users know how to do this from the menu but would really like some kind process...
2
by: Ron | last post by:
Hi All, Using WinXP pro/Access 2000. I have a database that's been used for about 5 months. Transferred lots of data from a dos based program, then the users have been using it for that 5...
9
by: Ron | last post by:
New discovery. If I take a perfectly good database, and "compact/repair" on it with Access 2000 (seems to be at multiple sites--I've tried it with my system here, at another office on an...
0
by: | last post by:
In an Access 2000 database in Access 2003 (Dutch) I have the following code: Option Compare Database Option Explicit Public Sub CompactDB() CommandBars("Menu Bar"). _ Controls("Tools"). _...
3
by: Bizmark | last post by:
Access2003, Windows XP Pro OEM user OS, Windows Sever 2000 server OS. Not-so-recently I inherited 3 Access DB's from another company merging with our own. Our own system works off of SQL...
4
by: zufie | last post by:
When I Repair/Compact my Access databse. I get icons/copies of a database named db1.mdb, db2.mdb, db3.mdb, etc... How else may I repair this database? Thanks!, Zuf
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.