Compact Database
in [General]
|
From: PeterM on 23 May 2010 22:42 I've read and seen tons of theories about how to compact an active database via VBA code for AC2003. The following code works for me. Public Function QuitAccess() Select Case MsgBox("Do you really want to close the MDS system?", vbYesNo Or vbQuestion Or vbDefaultButton2, "Please Confirm!") Case vbYes AutoCompactCurrentProject Application.Quit Case vbNo End Select End Function Public Function AutoCompactCurrentProject() Dim fs, f, s, filespec Dim strProjectPath As String, strProjectName As String strProjectPath = Application.CurrentProject.Path strProjectName = Application.CurrentProject.Name filespec = strProjectPath & "\" & strProjectName Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.getfile(filespec) s = CLng(f.Size / 1000000) 'convert size of app from bytes to Mb's If s > 80 Then 'edit the 80 (Mb's) to the max size you want to allow your app to grow. Application.SetOption ("Auto Compact"), 1 'compact app Dim strMsg As String strMsg = "The Medical Diary System needs to be compacted." & vbCrLf & vbCrLf _ & "This process will begin after you click on the OK button. " _ & "Please be patient while this process completes." & vbCrLf & vbCrLf _ & "It may take several minutes to run." MsgBox strMsg, vbInformation, "System Maintenance - Compact Database" Else Application.SetOption ("Auto Compact"), 0 'no don't compact app End If End Function It works great. If the size of the database is greater than 80mb, it turns the Compact on Close option on, if not, it turns the Compact on Close option off. As I said, it works great. The problem is that the Application.Quit command is executed, Access shuts down and is gone from the system tray. However, it is compacting the database in the background. If you try to restart the database while it's compacting, it ignores the call until the compact is complete. You can click on a shortcut to open the database as many times as you want, but it will not run the database until the compact finishes and there is no way to determine when it's done. I'm not obligated to use this solution. If someone has a better idea, that would be great. Has anyone ever run into this problem? I'd appreciate any ideas that you might have... thanks
From: Arvin Meyer [MVP] on 23 May 2010 23:06 Try this one: http://www.mvps.org/access/general/gen0041.htm -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access Co-author: "Access 2010 Solutions", published by Wiley "PeterM" <PeterM(a)discussions.microsoft.com> wrote in message news:A1EBE708-F6C3-42E6-A047-5AE5D5B9A339(a)microsoft.com... > I've read and seen tons of theories about how to compact an active > database > via VBA code for AC2003. The following code works for me. > > Public Function QuitAccess() > Select Case MsgBox("Do you really want to close the MDS system?", > vbYesNo Or vbQuestion Or vbDefaultButton2, "Please Confirm!") > Case vbYes > AutoCompactCurrentProject > Application.Quit > Case vbNo > End Select > End Function > > > Public Function AutoCompactCurrentProject() > Dim fs, f, s, filespec > Dim strProjectPath As String, strProjectName As String > strProjectPath = Application.CurrentProject.Path > strProjectName = Application.CurrentProject.Name > filespec = strProjectPath & "\" & strProjectName > Set fs = CreateObject("Scripting.FileSystemObject") > Set f = fs.getfile(filespec) > s = CLng(f.Size / 1000000) 'convert size of app from bytes to Mb's > If s > 80 Then 'edit the 80 (Mb's) to the max size you > want > to allow your app to grow. > Application.SetOption ("Auto Compact"), 1 'compact app > Dim strMsg As String > strMsg = "The Medical Diary System needs to be compacted." & vbCrLf > & vbCrLf _ > & "This process will begin after you click on the OK button. " > _ > & "Please be patient while this process completes." & vbCrLf & > vbCrLf _ > & "It may take several minutes to run." > MsgBox strMsg, vbInformation, "System Maintenance - Compact > Database" > Else > Application.SetOption ("Auto Compact"), 0 'no don't compact app > End If > End Function > > It works great. If the size of the database is greater than 80mb, it > turns > the Compact on Close option on, if not, it turns the Compact on Close > option > off. As I said, it works great. > > The problem is that the Application.Quit command is executed, Access shuts > down and is gone from the system tray. However, it is compacting the > database in the background. If you try to restart the database while it's > compacting, it ignores the call until the compact is complete. You can > click > on a shortcut to open the database as many times as you want, but it will > not > run the database until the compact finishes and there is no way to > determine > when it's done. > > I'm not obligated to use this solution. If someone has a better idea, > that > would be great. Has anyone ever run into this problem? I'd appreciate > any > ideas that you might have... thanks > >
From: PeterM on 24 May 2010 00:25 Arvin... Didn't work. I get a message that it's invalid to try to compact an active database using a macro or vba code. For my database, I do not have the file menu displayed. Does it need to be visible before running the code? Below is the code I run.. Public Function QuitAccess() Select Case MsgBox("Do you really want to close the MDS system?", vbYesNo Or vbQuestion Or vbDefaultButton2, "Please Confirm!") Case vbYes CommandBars("Menu Bar"). _ Controls("Tools"). _ Controls("Database utilities"). _ Controls("Compact and repair database..."). _ accDoDefaultAction Case vbNo End Select Application.Quit End Function "Arvin Meyer [MVP]" wrote: > Try this one: > > http://www.mvps.org/access/general/gen0041.htm > -- > Arvin Meyer, MCP, MVP > http://www.datastrat.com > http://www.accessmvp.com > http://www.mvps.org/access > Co-author: "Access 2010 Solutions", published by Wiley > > > "PeterM" <PeterM(a)discussions.microsoft.com> wrote in message > news:A1EBE708-F6C3-42E6-A047-5AE5D5B9A339(a)microsoft.com... > > I've read and seen tons of theories about how to compact an active > > database > > via VBA code for AC2003. The following code works for me. > > > > Public Function QuitAccess() > > Select Case MsgBox("Do you really want to close the MDS system?", > > vbYesNo Or vbQuestion Or vbDefaultButton2, "Please Confirm!") > > Case vbYes > > AutoCompactCurrentProject > > Application.Quit > > Case vbNo > > End Select > > End Function > > > > > > Public Function AutoCompactCurrentProject() > > Dim fs, f, s, filespec > > Dim strProjectPath As String, strProjectName As String > > strProjectPath = Application.CurrentProject.Path > > strProjectName = Application.CurrentProject.Name > > filespec = strProjectPath & "\" & strProjectName > > Set fs = CreateObject("Scripting.FileSystemObject") > > Set f = fs.getfile(filespec) > > s = CLng(f.Size / 1000000) 'convert size of app from bytes to Mb's > > If s > 80 Then 'edit the 80 (Mb's) to the max size you > > want > > to allow your app to grow. > > Application.SetOption ("Auto Compact"), 1 'compact app > > Dim strMsg As String > > strMsg = "The Medical Diary System needs to be compacted." & vbCrLf > > & vbCrLf _ > > & "This process will begin after you click on the OK button. " > > _ > > & "Please be patient while this process completes." & vbCrLf & > > vbCrLf _ > > & "It may take several minutes to run." > > MsgBox strMsg, vbInformation, "System Maintenance - Compact > > Database" > > Else > > Application.SetOption ("Auto Compact"), 0 'no don't compact app > > End If > > End Function > > > > It works great. If the size of the database is greater than 80mb, it > > turns > > the Compact on Close option on, if not, it turns the Compact on Close > > option > > off. As I said, it works great. > > > > The problem is that the Application.Quit command is executed, Access shuts > > down and is gone from the system tray. However, it is compacting the > > database in the background. If you try to restart the database while it's > > compacting, it ignores the call until the compact is complete. You can > > click > > on a shortcut to open the database as many times as you want, but it will > > not > > run the database until the compact finishes and there is no way to > > determine > > when it's done. > > > > I'm not obligated to use this solution. If someone has a better idea, > > that > > would be great. Has anyone ever run into this problem? I'd appreciate > > any > > ideas that you might have... thanks > > > > > > > . >
From: Arvin Meyer [MVP] on 26 May 2010 23:22 Add some error handling. Then when you debug by stepping through the code. Show us where it errors. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access Co-author: "Access 2010 Solutions", published by Wiley "PeterM" <PeterM(a)discussions.microsoft.com> wrote in message news:67A2EF69-E9FA-4D8D-B750-87B672CE9DB3(a)microsoft.com... > Arvin... > > Didn't work. I get a message that it's invalid to try to compact an > active > database using a macro or vba code. For my database, I do not have the > file > menu displayed. Does it need to be visible before running the code? > > Below is the code I run.. > > Public Function QuitAccess() > Select Case MsgBox("Do you really want to close the MDS system?", > vbYesNo Or vbQuestion Or vbDefaultButton2, "Please Confirm!") > Case vbYes > CommandBars("Menu Bar"). _ > Controls("Tools"). _ > Controls("Database utilities"). _ > Controls("Compact and repair database..."). _ > accDoDefaultAction > Case vbNo > End Select > Application.Quit > End Function > > > "Arvin Meyer [MVP]" wrote: > >> Try this one: >> >> http://www.mvps.org/access/general/gen0041.htm >> -- >> Arvin Meyer, MCP, MVP >> http://www.datastrat.com >> http://www.accessmvp.com >> http://www.mvps.org/access >> Co-author: "Access 2010 Solutions", published by Wiley >> >> >> "PeterM" <PeterM(a)discussions.microsoft.com> wrote in message >> news:A1EBE708-F6C3-42E6-A047-5AE5D5B9A339(a)microsoft.com... >> > I've read and seen tons of theories about how to compact an active >> > database >> > via VBA code for AC2003. The following code works for me. >> > >> > Public Function QuitAccess() >> > Select Case MsgBox("Do you really want to close the MDS system?", >> > vbYesNo Or vbQuestion Or vbDefaultButton2, "Please Confirm!") >> > Case vbYes >> > AutoCompactCurrentProject >> > Application.Quit >> > Case vbNo >> > End Select >> > End Function >> > >> > >> > Public Function AutoCompactCurrentProject() >> > Dim fs, f, s, filespec >> > Dim strProjectPath As String, strProjectName As String >> > strProjectPath = Application.CurrentProject.Path >> > strProjectName = Application.CurrentProject.Name >> > filespec = strProjectPath & "\" & strProjectName >> > Set fs = CreateObject("Scripting.FileSystemObject") >> > Set f = fs.getfile(filespec) >> > s = CLng(f.Size / 1000000) 'convert size of app from bytes to Mb's >> > If s > 80 Then 'edit the 80 (Mb's) to the max size you >> > want >> > to allow your app to grow. >> > Application.SetOption ("Auto Compact"), 1 'compact app >> > Dim strMsg As String >> > strMsg = "The Medical Diary System needs to be compacted." & >> > vbCrLf >> > & vbCrLf _ >> > & "This process will begin after you click on the OK button. >> > " >> > _ >> > & "Please be patient while this process completes." & vbCrLf >> > & >> > vbCrLf _ >> > & "It may take several minutes to run." >> > MsgBox strMsg, vbInformation, "System Maintenance - Compact >> > Database" >> > Else >> > Application.SetOption ("Auto Compact"), 0 'no don't compact >> > app >> > End If >> > End Function >> > >> > It works great. If the size of the database is greater than 80mb, it >> > turns >> > the Compact on Close option on, if not, it turns the Compact on Close >> > option >> > off. As I said, it works great. >> > >> > The problem is that the Application.Quit command is executed, Access >> > shuts >> > down and is gone from the system tray. However, it is compacting the >> > database in the background. If you try to restart the database while >> > it's >> > compacting, it ignores the call until the compact is complete. You can >> > click >> > on a shortcut to open the database as many times as you want, but it >> > will >> > not >> > run the database until the compact finishes and there is no way to >> > determine >> > when it's done. >> > >> > I'm not obligated to use this solution. If someone has a better idea, >> > that >> > would be great. Has anyone ever run into this problem? I'd appreciate >> > any >> > ideas that you might have... thanks >> > >> > >> >> >> . >>
|
Pages: 1 Prev: Building search/filter criteria Next: Question about recordsource |