Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Compacting Database

Discussion in 'Information Technology' started by Justin, Oct 29, 2005.

  1. Justin

    Justin
    Expand Collapse
    Guest

    Question, is there a way that the database automatically compact itself after
    it hits a certain amount of megs (whatever number I set it to be).
    Right Now, I have a module to see if the database is less than 300 megs, if
    it is more, then a message appears and tells the user to compact the database.
    I want to automate it, wonder if it is possible. Thanks
     
  2. Loading...

    Similar Threads Forum Date
    USA NSA has massive database of Americans' phone calls (USA Today 5.11.2006) Breaking News Jun 11, 2013
    Some database errors while navigating SPN forums Announcements Dec 29, 2004

  3. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi, Justin.

    > Question, is there a way that the database automatically compact itself after
    > it hits a certain amount of megs (whatever number I set it to be).


    With VBA in Access 2000 and later versions, yes. First, create a new table,
    tblAdmin, with the following structure:

    ID, AutoNumber, primary key
    LastCompact, Date/Time

    Next, if you don't already have a similar function to determine the file's
    size, create a new standard module and paste the following code into it:

    Public Function getFileSize(sFilePath As String, Optional sSize As String)
    As Long

    On Error GoTo ErrHandler

    Dim nByteSize As Currency
    Dim nFileSize As Currency

    Const KILO As Long = 1024

    nByteSize = FileLen(sFilePath)

    If (UCase$(sSize) = "M") Then
    nFileSize = nByteSize / KILO / KILO
    ElseIf (UCase$(sSize) = "K") Then
    nFileSize = nByteSize / KILO
    Else
    nFileSize = nByteSize
    End If

    getFileSize = nFileSize

    Exit Function

    ErrHandler:

    MsgBox "Error in getFileSize( )." & vbCrLf & vbCrLf & _
    "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
    Err.Clear

    End Function

    Next, if you don't already have a start up form, create a new form and place
    the following code in its OnOpen( ) event:

    Private Sub Form_Open(Cancel As Integer)

    On Error GoTo ErrHandler

    Dim dt As Date
    Const MAX_SIZE As Long = 300

    dt = Nz(DLookup("LastCompact", "tblAdmin"), #1/1/1900#)

    If (dt < Date) Then
    If (getFileSize(CurrentProject.Path & "\" & _
    CurrentProject.Name, "M") > MAX_SIZE) Then
    CurrentDb().Execute "UPDATE tblAdmin " & _
    "SET LastCompact = #" & Date & "#"
    CommandBars("Menu Bar").Controls("Tools"). _
    Controls("Database utilities"). _
    Controls("Compact and Repair database..."). _
    accDoDefaultAction
    End If
    End If

    Exit Sub

    ErrHandler:

    MsgBox "Error in Form_Open( ) in" & vbCrLf & _
    Me.Name & " form." & vbCrLf & vbCrLf & _
    "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
    Err.Clear

    End Sub

    If you've already got your own function to determine the file size, then
    replace the call to getFileSize( ) in the form's OnOpen( ) event with your
    own. If you've already got a start up form, then add the code in this
    Form_Open( ) event to your own form's OnOpen( ) event, ensuring that this
    compaction code occurs before any other executable code.

    If you've already got a start up form, then you're done. If not, then
    select the Tools -> Startup... menu to open the Startup dialog window. Set
    the "Display Form/Page" combo box to the name of your new start up form and
    close the dialog window. Now, you're done.

    Each time the database application is opened, it will check whether the
    automatic compaction has already taken place today. If it hasn't, then it
    checks the file size. If it exceeds the limit, then the file is compacted.
    Therefore, the file will be compacted automatically once a day, not every
    single time the database is opened if it's over the limit.

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips.

    (Please remove ZERO_SPAM from my reply E-mail address so that a message will
    be forwarded to me.)
    - - -
    If my answer has helped you, please sign in and answer yes to the question
    "Did this post answer your question?" at the bottom of the message, which
    adds your question and the answers to the database of answers. Remember that
    questions answered the quickest are often from those who have a history of
    rewarding the contributors who have taken the time to answer questions
    correctly.


    "Justin" wrote:

    > Question, is there a way that the database automatically compact itself after
    > it hits a certain amount of megs (whatever number I set it to be).
    > Right Now, I have a module to see if the database is less than 300 megs, if
    > it is more, then a message appears and tells the user to compact the database.
    > I want to automate it, wonder if it is possible. Thanks
     
  4. Justin

    Justin
    Expand Collapse
    Guest

    I got an error message when using this code.
    I made like an autocompact form (blank form to open up my main form)
    seems i have alot of vb in my main menu that it was conflicting with this
    code.a
    anywaz,

    when the database open, it opens the form that has the code
    but I got this message
    "You can not compact the open database while running a macro or VB CODe.
    Instead of using macro or code, on the tools menu, point to database
    utilites and then click compact/Repair Database"

    Im using office 2003
    please help




    "'69 Camaro" wrote:

    > Hi, Justin.
    >
    > > Question, is there a way that the database automatically compact itself after
    > > it hits a certain amount of megs (whatever number I set it to be).

    >
    > With VBA in Access 2000 and later versions, yes. First, create a new table,
    > tblAdmin, with the following structure:
    >
    > ID, AutoNumber, primary key
    > LastCompact, Date/Time
    >
    > Next, if you don't already have a similar function to determine the file's
    > size, create a new standard module and paste the following code into it:
    >
    > Public Function getFileSize(sFilePath As String, Optional sSize As String)
    > As Long
    >
    > On Error GoTo ErrHandler
    >
    > Dim nByteSize As Currency
    > Dim nFileSize As Currency
    >
    > Const KILO As Long = 1024
    >
    > nByteSize = FileLen(sFilePath)
    >
    > If (UCase$(sSize) = "M") Then
    > nFileSize = nByteSize / KILO / KILO
    > ElseIf (UCase$(sSize) = "K") Then
    > nFileSize = nByteSize / KILO
    > Else
    > nFileSize = nByteSize
    > End If
    >
    > getFileSize = nFileSize
    >
    > Exit Function
    >
    > ErrHandler:
    >
    > MsgBox "Error in getFileSize( )." & vbCrLf & vbCrLf & _
    > "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
    > Err.Clear
    >
    > End Function
    >
    > Next, if you don't already have a start up form, create a new form and place
    > the following code in its OnOpen( ) event:
    >
    > Private Sub Form_Open(Cancel As Integer)
    >
    > On Error GoTo ErrHandler
    >
    > Dim dt As Date
    > Const MAX_SIZE As Long = 300
    >
    > dt = Nz(DLookup("LastCompact", "tblAdmin"), #1/1/1900#)
    >
    > If (dt < Date) Then
    > If (getFileSize(CurrentProject.Path & "\" & _
    > CurrentProject.Name, "M") > MAX_SIZE) Then
    > CurrentDb().Execute "UPDATE tblAdmin " & _
    > "SET LastCompact = #" & Date & "#"
    > CommandBars("Menu Bar").Controls("Tools"). _
    > Controls("Database utilities"). _
    > Controls("Compact and Repair database..."). _
    > accDoDefaultAction
    > End If
    > End If
    >
    > Exit Sub
    >
    > ErrHandler:
    >
    > MsgBox "Error in Form_Open( ) in" & vbCrLf & _
    > Me.Name & " form." & vbCrLf & vbCrLf & _
    > "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
    > Err.Clear
    >
    > End Sub
    >
    > If you've already got your own function to determine the file size, then
    > replace the call to getFileSize( ) in the form's OnOpen( ) event with your
    > own. If you've already got a start up form, then add the code in this
    > Form_Open( ) event to your own form's OnOpen( ) event, ensuring that this
    > compaction code occurs before any other executable code.
    >
    > If you've already got a start up form, then you're done. If not, then
    > select the Tools -> Startup... menu to open the Startup dialog window. Set
    > the "Display Form/Page" combo box to the name of your new start up form and
    > close the dialog window. Now, you're done.
    >
    > Each time the database application is opened, it will check whether the
    > automatic compaction has already taken place today. If it hasn't, then it
    > checks the file size. If it exceeds the limit, then the file is compacted.
    > Therefore, the file will be compacted automatically once a day, not every
    > single time the database is opened if it's over the limit.
    >
    > HTH.
    > Gunny
    >
    > See http://www.QBuilt.com for all your database needs.
    > See http://www.Access.QBuilt.com for Microsoft Access tips.
    >
    > (Please remove ZERO_SPAM from my reply E-mail address so that a message will
    > be forwarded to me.)
    > - - -
    > If my answer has helped you, please sign in and answer yes to the question
    > "Did this post answer your question?" at the bottom of the message, which
    > adds your question and the answers to the database of answers. Remember that
    > questions answered the quickest are often from those who have a history of
    > rewarding the contributors who have taken the time to answer questions
    > correctly.
    >
    >
    > "Justin" wrote:
    >
    > > Question, is there a way that the database automatically compact itself after
    > > it hits a certain amount of megs (whatever number I set it to be).
    > > Right Now, I have a module to see if the database is less than 300 megs, if
    > > it is more, then a message appears and tells the user to compact the database.
    > > I want to automate it, wonder if it is possible. Thanks
     
  5. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi, Justin.

    >I got an error message when using this code.
    > I made like an autocompact form (blank form to open up my main form)
    > seems i have alot of vb in my main menu that it was conflicting with this
    > code.


    Correct. I mentioned you should ensure that the compaction code occurs
    before any other executable code, but this also applies to macros.

    > when the database open, it opens the form that has the code
    > but I got this message
    > "You can not compact the open database while running a macro or VB CODe.
    > Instead of using macro or code, on the tools menu, point to database
    > utilites and then click compact/Repair Database"
    >
    > Im using office 2003


    This code is used in the startup form with Access 2003 SP-1 and Windows XP
    Pro SP-2 on databases in Access 2000 file format, and it works flawlessly.
    It works in other versions of Access too, but this is the configuration we
    use for Access 2003. Here are some things to check:

    1. Are you using both the Form_Open( ) procedure and the getFileSize( )
    function I suggested (not another form event procedure nor your own
    function)? If so, have you changed this code in any way? If so, please
    post the code.

    2. Is this form the start up form? (No other form should be opened before
    this one.)

    3. Do you have any other VBA code or macros that are executed prior to this
    form's OnOpen( ) event, such as an Autoexec macro?

    4. Is this form using the OnTimer( ) event?

    5. Do you have any library databases that are not MDE's? (Problems in
    library databases can interfere with the current database.)

    6. Is all of the code compiled, including any library databases?

    7. Could the application (or one of its library databases) be running in
    break mode? Please see the following Web page for one way to reset this
    without resorting to the /decompile command-line option:

    http://groups.google.com/group/micr...rosoft.public.access.*&hl=en#f0a8402f34ed0dec

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips.


    "Justin" <Justin@discussions.microsoft.com> wrote in message
    news:D4264F69-E53A-43B7-934B-552221B95F73@microsoft.com...
    >I got an error message when using this code.
    > I made like an autocompact form (blank form to open up my main form)
    > seems i have alot of vb in my main menu that it was conflicting with this
    > code.a
    > anywaz,
    >
    > when the database open, it opens the form that has the code
    > but I got this message
    > "You can not compact the open database while running a macro or VB CODe.
    > Instead of using macro or code, on the tools menu, point to database
    > utilites and then click compact/Repair Database"
    >
    > Im using office 2003
    > please help
    >
    >
    >
    >
    > "'69 Camaro" wrote:
    >
    >> Hi, Justin.
    >>
    >> > Question, is there a way that the database automatically compact itself
    >> > after
    >> > it hits a certain amount of megs (whatever number I set it to be).

    >>
    >> With VBA in Access 2000 and later versions, yes. First, create a new
    >> table,
    >> tblAdmin, with the following structure:
    >>
    >> ID, AutoNumber, primary key
    >> LastCompact, Date/Time
    >>
    >> Next, if you don't already have a similar function to determine the
    >> file's
    >> size, create a new standard module and paste the following code into it:
    >>
    >> Public Function getFileSize(sFilePath As String, Optional sSize As
    >> String)
    >> As Long
    >>
    >> On Error GoTo ErrHandler
    >>
    >> Dim nByteSize As Currency
    >> Dim nFileSize As Currency
    >>
    >> Const KILO As Long = 1024
    >>
    >> nByteSize = FileLen(sFilePath)
    >>
    >> If (UCase$(sSize) = "M") Then
    >> nFileSize = nByteSize / KILO / KILO
    >> ElseIf (UCase$(sSize) = "K") Then
    >> nFileSize = nByteSize / KILO
    >> Else
    >> nFileSize = nByteSize
    >> End If
    >>
    >> getFileSize = nFileSize
    >>
    >> Exit Function
    >>
    >> ErrHandler:
    >>
    >> MsgBox "Error in getFileSize( )." & vbCrLf & vbCrLf & _
    >> "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
    >> Err.Clear
    >>
    >> End Function
    >>
    >> Next, if you don't already have a start up form, create a new form and
    >> place
    >> the following code in its OnOpen( ) event:
    >>
    >> Private Sub Form_Open(Cancel As Integer)
    >>
    >> On Error GoTo ErrHandler
    >>
    >> Dim dt As Date
    >> Const MAX_SIZE As Long = 300
    >>
    >> dt = Nz(DLookup("LastCompact", "tblAdmin"), #1/1/1900#)
    >>
    >> If (dt < Date) Then
    >> If (getFileSize(CurrentProject.Path & "\" & _
    >> CurrentProject.Name, "M") > MAX_SIZE) Then
    >> CurrentDb().Execute "UPDATE tblAdmin " & _
    >> "SET LastCompact = #" & Date & "#"
    >> CommandBars("Menu Bar").Controls("Tools"). _
    >> Controls("Database utilities"). _
    >> Controls("Compact and Repair database..."). _
    >> accDoDefaultAction
    >> End If
    >> End If
    >>
    >> Exit Sub
    >>
    >> ErrHandler:
    >>
    >> MsgBox "Error in Form_Open( ) in" & vbCrLf & _
    >> Me.Name & " form." & vbCrLf & vbCrLf & _
    >> "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
    >> Err.Clear
    >>
    >> End Sub
    >>
    >> If you've already got your own function to determine the file size, then
    >> replace the call to getFileSize( ) in the form's OnOpen( ) event with
    >> your
    >> own. If you've already got a start up form, then add the code in this
    >> Form_Open( ) event to your own form's OnOpen( ) event, ensuring that this
    >> compaction code occurs before any other executable code.
    >>
    >> If you've already got a start up form, then you're done. If not, then
    >> select the Tools -> Startup... menu to open the Startup dialog window.
    >> Set
    >> the "Display Form/Page" combo box to the name of your new start up form
    >> and
    >> close the dialog window. Now, you're done.
    >>
    >> Each time the database application is opened, it will check whether the
    >> automatic compaction has already taken place today. If it hasn't, then
    >> it
    >> checks the file size. If it exceeds the limit, then the file is
    >> compacted.
    >> Therefore, the file will be compacted automatically once a day, not every
    >> single time the database is opened if it's over the limit.
    >>
    >> HTH.
    >> Gunny
    >>
    >> See http://www.QBuilt.com for all your database needs.
    >> See http://www.Access.QBuilt.com for Microsoft Access tips.
    >>
    >> (Please remove ZERO_SPAM from my reply E-mail address so that a message
    >> will
    >> be forwarded to me.)
    >> - - -
    >> If my answer has helped you, please sign in and answer yes to the
    >> question
    >> "Did this post answer your question?" at the bottom of the message, which
    >> adds your question and the answers to the database of answers. Remember
    >> that
    >> questions answered the quickest are often from those who have a history
    >> of
    >> rewarding the contributors who have taken the time to answer questions
    >> correctly.
    >>
    >>
    >> "Justin" wrote:
    >>
    >> > Question, is there a way that the database automatically compact itself
    >> > after
    >> > it hits a certain amount of megs (whatever number I set it to be).
    >> > Right Now, I have a module to see if the database is less than 300
    >> > megs, if
    >> > it is more, then a message appears and tells the user to compact the
    >> > database.
    >> > I want to automate it, wonder if it is possible. Thanks
     
  6. Justin

    Justin
    Expand Collapse
    Guest

    I fixed the problem.
    I took the code tou gave me and added one more line, an "else" statement to
    get my main menu. right now it works but i don't know if I did it correctly
    I think this code has to be in its own form that loads up, so what i did, i
    created
    like an autoform to check the database else close this form and open up the
    main menu form.
    here is the code

    Private Sub Form_Open(Cancel As Integer)
    Dim dt As Date

    Const MAX_SIZE As Long = 1

    dt = Nz(DLookup(" LastCompact", "tblAdmin"), #1/1/1900#)
    If (dt < Date) Then
    If (getFileSize(CurrentProject.Path & "\" & _
    CurrentProject.Name, "M") > MAX_SIZE) Then
    CurrentDb().Execute "UPDATE tblAdmin " & _
    "SET LastCompact = #" & Date & "#"
    CommandBars("Menu
    Bar").Controls("Tools").Controls("Database utilities").Controls("Compact and
    repair database..."). _
    accDoDefaultAction
    End If
    Else <------
    this is the only part i added
    DoCmd.OpenForm "Main Menu"
    DoCmd.Close acForm, "AutoCompact"
    End If
    End Sub













    "'69 Camaro" wrote:

    > Hi, Justin.
    >
    > >I got an error message when using this code.
    > > I made like an autocompact form (blank form to open up my main form)
    > > seems i have alot of vb in my main menu that it was conflicting with this
    > > code.

    >
    > Correct. I mentioned you should ensure that the compaction code occurs
    > before any other executable code, but this also applies to macros.
    >
    > > when the database open, it opens the form that has the code
    > > but I got this message
    > > "You can not compact the open database while running a macro or VB CODe.
    > > Instead of using macro or code, on the tools menu, point to database
    > > utilites and then click compact/Repair Database"
    > >
    > > Im using office 2003

    >
    > This code is used in the startup form with Access 2003 SP-1 and Windows XP
    > Pro SP-2 on databases in Access 2000 file format, and it works flawlessly.
    > It works in other versions of Access too, but this is the configuration we
    > use for Access 2003. Here are some things to check:
    >
    > 1. Are you using both the Form_Open( ) procedure and the getFileSize( )
    > function I suggested (not another form event procedure nor your own
    > function)? If so, have you changed this code in any way? If so, please
    > post the code.
    >
    > 2. Is this form the start up form? (No other form should be opened before
    > this one.)
    >
    > 3. Do you have any other VBA code or macros that are executed prior to this
    > form's OnOpen( ) event, such as an Autoexec macro?
    >
    > 4. Is this form using the OnTimer( ) event?
    >
    > 5. Do you have any library databases that are not MDE's? (Problems in
    > library databases can interfere with the current database.)
    >
    > 6. Is all of the code compiled, including any library databases?
    >
    > 7. Could the application (or one of its library databases) be running in
    > break mode? Please see the following Web page for one way to reset this
    > without resorting to the /decompile command-line option:
    >
    > http://groups.google.com/group/micr...rosoft.public.access.*&hl=en#f0a8402f34ed0dec
    >
    > HTH.
    > Gunny
    >
    > See http://www.QBuilt.com for all your database needs.
    > See http://www.Access.QBuilt.com for Microsoft Access tips.
    >
    >
    > "Justin" <Justin@discussions.microsoft.com> wrote in message
    > news:D4264F69-E53A-43B7-934B-552221B95F73@microsoft.com...
    > >I got an error message when using this code.
    > > I made like an autocompact form (blank form to open up my main form)
    > > seems i have alot of vb in my main menu that it was conflicting with this
    > > code.a
    > > anywaz,
    > >
    > > when the database open, it opens the form that has the code
    > > but I got this message
    > > "You can not compact the open database while running a macro or VB CODe.
    > > Instead of using macro or code, on the tools menu, point to database
    > > utilites and then click compact/Repair Database"
    > >
    > > Im using office 2003
    > > please help
    > >
    > >
    > >
    > >
    > > "'69 Camaro" wrote:
    > >
    > >> Hi, Justin.
    > >>
    > >> > Question, is there a way that the database automatically compact itself
    > >> > after
    > >> > it hits a certain amount of megs (whatever number I set it to be).
    > >>
    > >> With VBA in Access 2000 and later versions, yes. First, create a new
    > >> table,
    > >> tblAdmin, with the following structure:
    > >>
    > >> ID, AutoNumber, primary key
    > >> LastCompact, Date/Time
    > >>
    > >> Next, if you don't already have a similar function to determine the
    > >> file's
    > >> size, create a new standard module and paste the following code into it:
    > >>
    > >> Public Function getFileSize(sFilePath As String, Optional sSize As
    > >> String)
    > >> As Long
    > >>
    > >> On Error GoTo ErrHandler
    > >>
    > >> Dim nByteSize As Currency
    > >> Dim nFileSize As Currency
    > >>
    > >> Const KILO As Long = 1024
    > >>
    > >> nByteSize = FileLen(sFilePath)
    > >>
    > >> If (UCase$(sSize) = "M") Then
    > >> nFileSize = nByteSize / KILO / KILO
    > >> ElseIf (UCase$(sSize) = "K") Then
    > >> nFileSize = nByteSize / KILO
    > >> Else
    > >> nFileSize = nByteSize
    > >> End If
    > >>
    > >> getFileSize = nFileSize
    > >>
    > >> Exit Function
    > >>
    > >> ErrHandler:
    > >>
    > >> MsgBox "Error in getFileSize( )." & vbCrLf & vbCrLf & _
    > >> "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
    > >> Err.Clear
    > >>
    > >> End Function
    > >>
    > >> Next, if you don't already have a start up form, create a new form and
    > >> place
    > >> the following code in its OnOpen( ) event:
    > >>
    > >> Private Sub Form_Open(Cancel As Integer)
    > >>
    > >> On Error GoTo ErrHandler
    > >>
    > >> Dim dt As Date
    > >> Const MAX_SIZE As Long = 300
    > >>
    > >> dt = Nz(DLookup("LastCompact", "tblAdmin"), #1/1/1900#)
    > >>
    > >> If (dt < Date) Then
    > >> If (getFileSize(CurrentProject.Path & "\" & _
    > >> CurrentProject.Name, "M") > MAX_SIZE) Then
    > >> CurrentDb().Execute "UPDATE tblAdmin " & _
    > >> "SET LastCompact = #" & Date & "#"
    > >> CommandBars("Menu Bar").Controls("Tools"). _
    > >> Controls("Database utilities"). _
    > >> Controls("Compact and Repair database..."). _
    > >> accDoDefaultAction
    > >> End If
    > >> End If
    > >>
    > >> Exit Sub
    > >>
    > >> ErrHandler:
    > >>
    > >> MsgBox "Error in Form_Open( ) in" & vbCrLf & _
    > >> Me.Name & " form." & vbCrLf & vbCrLf & _
    > >> "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
    > >> Err.Clear
    > >>
    > >> End Sub
    > >>
    > >> If you've already got your own function to determine the file size, then
    > >> replace the call to getFileSize( ) in the form's OnOpen( ) event with
    > >> your
    > >> own. If you've already got a start up form, then add the code in this
    > >> Form_Open( ) event to your own form's OnOpen( ) event, ensuring that this
    > >> compaction code occurs before any other executable code.
    > >>
    > >> If you've already got a start up form, then you're done. If not, then
    > >> select the Tools -> Startup... menu to open the Startup dialog window.
    > >> Set
    > >> the "Display Form/Page" combo box to the name of your new start up form
    > >> and
    > >> close the dialog window. Now, you're done.
    > >>
    > >> Each time the database application is opened, it will check whether the
    > >> automatic compaction has already taken place today. If it hasn't, then
    > >> it
    > >> checks the file size. If it exceeds the limit, then the file is
    > >> compacted.
    > >> Therefore, the file will be compacted automatically once a day, not every
    > >> single time the database is opened if it's over the limit.
    > >>
    > >> HTH.
    > >> Gunny
    > >>
    > >> See http://www.QBuilt.com for all your database needs.
    > >> See http://www.Access.QBuilt.com for Microsoft Access tips.
    > >>
    > >> (Please remove ZERO_SPAM from my reply E-mail address so that a message
    > >> will
    > >> be forwarded to me.)
    > >> - - -
    > >> If my answer has helped you, please sign in and answer yes to the
    > >> question
    > >> "Did this post answer your question?" at the bottom of the message, which
    > >> adds your question and the answers to the database of answers. Remember
    > >> that
    > >> questions answered the quickest are often from those who have a history
    > >> of
    > >> rewarding the contributors who have taken the time to answer questions
    > >> correctly.
    > >>
    > >>
    > >> "Justin" wrote:
    > >>
    > >> > Question, is there a way that the database automatically compact itself
    > >> > after
    > >> > it hits a certain amount of megs (whatever number I set it to be).
    > >> > Right Now, I have a module to see if the database is less than 300
    > >> > megs, if
    > >> > it is more, then a message appears and tells the user to compact the
    > >> > database.
    > >> > I want to automate it, wonder if it is possible. Thanks

    >
    >
    >
     
  7. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi, Justin.

    >I fixed the problem.


    Good job. I'm glad it's working for you now.

    > right now it works but i don't know if I did it correctly


    You did it correctly.

    > I think this code has to be in its own form that loads up


    That's probably the easiest way to prevent this error. If there is other
    code behind the start up form, then you just need to make sure that it
    doesn't interfere with this compaction code.

    > here is the code


    It's substantially the same code we use, but we require a size more than 1
    MB before compaction and we open the application's splash screen instead.

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips.


    "Justin" <Justin@discussions.microsoft.com> wrote in message
    news:0112D12D-479F-4313-BB8C-85139C839C7E@microsoft.com...
    >I fixed the problem.
    > I took the code tou gave me and added one more line, an "else" statement
    > to
    > get my main menu. right now it works but i don't know if I did it
    > correctly
    > I think this code has to be in its own form that loads up, so what i did,
    > i
    > created
    > like an autoform to check the database else close this form and open up
    > the
    > main menu form.
    > here is the code
    >
    > Private Sub Form_Open(Cancel As Integer)
    > Dim dt As Date
    >
    > Const MAX_SIZE As Long = 1
    >
    > dt = Nz(DLookup(" LastCompact", "tblAdmin"), #1/1/1900#)
    > If (dt < Date) Then
    > If (getFileSize(CurrentProject.Path & "\" & _
    > CurrentProject.Name, "M") > MAX_SIZE) Then
    > CurrentDb().Execute "UPDATE tblAdmin " & _
    > "SET LastCompact = #" & Date & "#"
    > CommandBars("Menu
    > Bar").Controls("Tools").Controls("Database utilities").Controls("Compact
    > and
    > repair database..."). _
    > accDoDefaultAction
    > End If
    > Else
    > <------
    > this is the only part i added
    > DoCmd.OpenForm "Main Menu"
    > DoCmd.Close acForm, "AutoCompact"
    > End If
    > End Sub
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    > "'69 Camaro" wrote:
    >
    >> Hi, Justin.
    >>
    >> >I got an error message when using this code.
    >> > I made like an autocompact form (blank form to open up my main form)
    >> > seems i have alot of vb in my main menu that it was conflicting with
    >> > this
    >> > code.

    >>
    >> Correct. I mentioned you should ensure that the compaction code occurs
    >> before any other executable code, but this also applies to macros.
    >>
    >> > when the database open, it opens the form that has the code
    >> > but I got this message
    >> > "You can not compact the open database while running a macro or VB
    >> > CODe.
    >> > Instead of using macro or code, on the tools menu, point to database
    >> > utilites and then click compact/Repair Database"
    >> >
    >> > Im using office 2003

    >>
    >> This code is used in the startup form with Access 2003 SP-1 and Windows
    >> XP
    >> Pro SP-2 on databases in Access 2000 file format, and it works
    >> flawlessly.
    >> It works in other versions of Access too, but this is the configuration
    >> we
    >> use for Access 2003. Here are some things to check:
    >>
    >> 1. Are you using both the Form_Open( ) procedure and the getFileSize( )
    >> function I suggested (not another form event procedure nor your own
    >> function)? If so, have you changed this code in any way? If so, please
    >> post the code.
    >>
    >> 2. Is this form the start up form? (No other form should be opened
    >> before
    >> this one.)
    >>
    >> 3. Do you have any other VBA code or macros that are executed prior to
    >> this
    >> form's OnOpen( ) event, such as an Autoexec macro?
    >>
    >> 4. Is this form using the OnTimer( ) event?
    >>
    >> 5. Do you have any library databases that are not MDE's? (Problems in
    >> library databases can interfere with the current database.)
    >>
    >> 6. Is all of the code compiled, including any library databases?
    >>
    >> 7. Could the application (or one of its library databases) be running in
    >> break mode? Please see the following Web page for one way to reset this
    >> without resorting to the /decompile command-line option:
    >>
    >> http://groups.google.com/group/micr...rosoft.public.access.*&hl=en#f0a8402f34ed0dec
    >>
    >> HTH.
    >> Gunny
    >>
    >> See http://www.QBuilt.com for all your database needs.
    >> See http://www.Access.QBuilt.com for Microsoft Access tips.
    >>
    >>
    >> "Justin" <Justin@discussions.microsoft.com> wrote in message
    >> news:D4264F69-E53A-43B7-934B-552221B95F73@microsoft.com...
    >> >I got an error message when using this code.
    >> > I made like an autocompact form (blank form to open up my main form)
    >> > seems i have alot of vb in my main menu that it was conflicting with
    >> > this
    >> > code.a
    >> > anywaz,
    >> >
    >> > when the database open, it opens the form that has the code
    >> > but I got this message
    >> > "You can not compact the open database while running a macro or VB
    >> > CODe.
    >> > Instead of using macro or code, on the tools menu, point to database
    >> > utilites and then click compact/Repair Database"
    >> >
    >> > Im using office 2003
    >> > please help
    >> >
    >> >
    >> >
    >> >
    >> > "'69 Camaro" wrote:
    >> >
    >> >> Hi, Justin.
    >> >>
    >> >> > Question, is there a way that the database automatically compact
    >> >> > itself
    >> >> > after
    >> >> > it hits a certain amount of megs (whatever number I set it to be).
    >> >>
    >> >> With VBA in Access 2000 and later versions, yes. First, create a new
    >> >> table,
    >> >> tblAdmin, with the following structure:
    >> >>
    >> >> ID, AutoNumber, primary key
    >> >> LastCompact, Date/Time
    >> >>
    >> >> Next, if you don't already have a similar function to determine the
    >> >> file's
    >> >> size, create a new standard module and paste the following code into
    >> >> it:
    >> >>
    >> >> Public Function getFileSize(sFilePath As String, Optional sSize As
    >> >> String)
    >> >> As Long
    >> >>
    >> >> On Error GoTo ErrHandler
    >> >>
    >> >> Dim nByteSize As Currency
    >> >> Dim nFileSize As Currency
    >> >>
    >> >> Const KILO As Long = 1024
    >> >>
    >> >> nByteSize = FileLen(sFilePath)
    >> >>
    >> >> If (UCase$(sSize) = "M") Then
    >> >> nFileSize = nByteSize / KILO / KILO
    >> >> ElseIf (UCase$(sSize) = "K") Then
    >> >> nFileSize = nByteSize / KILO
    >> >> Else
    >> >> nFileSize = nByteSize
    >> >> End If
    >> >>
    >> >> getFileSize = nFileSize
    >> >>
    >> >> Exit Function
    >> >>
    >> >> ErrHandler:
    >> >>
    >> >> MsgBox "Error in getFileSize( )." & vbCrLf & vbCrLf & _
    >> >> "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
    >> >> Err.Clear
    >> >>
    >> >> End Function
    >> >>
    >> >> Next, if you don't already have a start up form, create a new form and
    >> >> place
    >> >> the following code in its OnOpen( ) event:
    >> >>
    >> >> Private Sub Form_Open(Cancel As Integer)
    >> >>
    >> >> On Error GoTo ErrHandler
    >> >>
    >> >> Dim dt As Date
    >> >> Const MAX_SIZE As Long = 300
    >> >>
    >> >> dt = Nz(DLookup("LastCompact", "tblAdmin"), #1/1/1900#)
    >> >>
    >> >> If (dt < Date) Then
    >> >> If (getFileSize(CurrentProject.Path & "\" & _
    >> >> CurrentProject.Name, "M") > MAX_SIZE) Then
    >> >> CurrentDb().Execute "UPDATE tblAdmin " & _
    >> >> "SET LastCompact = #" & Date & "#"
    >> >> CommandBars("Menu Bar").Controls("Tools"). _
    >> >> Controls("Database utilities"). _
    >> >> Controls("Compact and Repair database..."). _
    >> >> accDoDefaultAction
    >> >> End If
    >> >> End If
    >> >>
    >> >> Exit Sub
    >> >>
    >> >> ErrHandler:
    >> >>
    >> >> MsgBox "Error in Form_Open( ) in" & vbCrLf & _
    >> >> Me.Name & " form." & vbCrLf & vbCrLf & _
    >> >> "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
    >> >> Err.Clear
    >> >>
    >> >> End Sub
    >> >>
    >> >> If you've already got your own function to determine the file size,
    >> >> then
    >> >> replace the call to getFileSize( ) in the form's OnOpen( ) event with
    >> >> your
    >> >> own. If you've already got a start up form, then add the code in this
    >> >> Form_Open( ) event to your own form's OnOpen( ) event, ensuring that
    >> >> this
    >> >> compaction code occurs before any other executable code.
    >> >>
    >> >> If you've already got a start up form, then you're done. If not, then
    >> >> select the Tools -> Startup... menu to open the Startup dialog window.
    >> >> Set
    >> >> the "Display Form/Page" combo box to the name of your new start up
    >> >> form
    >> >> and
    >> >> close the dialog window. Now, you're done.
    >> >>
    >> >> Each time the database application is opened, it will check whether
    >> >> the
    >> >> automatic compaction has already taken place today. If it hasn't,
    >> >> then
    >> >> it
    >> >> checks the file size. If it exceeds the limit, then the file is
    >> >> compacted.
    >> >> Therefore, the file will be compacted automatically once a day, not
    >> >> every
    >> >> single time the database is opened if it's over the limit.
    >> >>
    >> >> HTH.
    >> >> Gunny
    >> >>
    >> >> See http://www.QBuilt.com for all your database needs.
    >> >> See http://www.Access.QBuilt.com for Microsoft Access tips.
    >> >>
    >> >> (Please remove ZERO_SPAM from my reply E-mail address so that a
    >> >> message
    >> >> will
    >> >> be forwarded to me.)
    >> >> - - -
    >> >> If my answer has helped you, please sign in and answer yes to the
    >> >> question
    >> >> "Did this post answer your question?" at the bottom of the message,
    >> >> which
    >> >> adds your question and the answers to the database of answers.
    >> >> Remember
    >> >> that
    >> >> questions answered the quickest are often from those who have a
    >> >> history
    >> >> of
    >> >> rewarding the contributors who have taken the time to answer questions
    >> >> correctly.
    >> >>
    >> >>
    >> >> "Justin" wrote:
    >> >>
    >> >> > Question, is there a way that the database automatically compact
    >> >> > itself
    >> >> > after
    >> >> > it hits a certain amount of megs (whatever number I set it to be).
    >> >> > Right Now, I have a module to see if the database is less than 300
    >> >> > megs, if
    >> >> > it is more, then a message appears and tells the user to compact the
    >> >> > database.
    >> >> > I want to automate it, wonder if it is possible. Thanks

    >>
    >>
    >>
     
  8. Justin

    Justin
    Expand Collapse
    Guest

    Thanks for all your help.
    Question, is there any problem of compacting the database daily?

    Also, I have another problem, but not pertaining to compacting the database,
    maybe you know this solution.

    The same database is to export data from a table to excel, and to place it a
    certain way (cell range). also, the sheet has formulas that will have to be
    copied for every data it has.
    Now i have this code from a collegue of mine that does this.
    What it does it looks at the sheet where it has to copy to, take all teh
    data from teh table and do the whole procedure.
    My problem is that I want that sheet it copies to be to like a template and
    to have like a save as dialog window appear, so that the user can give this
    file a name.
    I tried to add some vb code for a save as dialog, but when i run it, the
    window appears, i make a name, but the data still goes to that template
    sheet. From there, i have to go to file-->save as. I am looking for like
    automated way or something. If you can, please take a look at the code and
    see what im doing wrong. THanks

    code:
    Dim WhereTo As String
    Dim ProjectID As String
    Dim rsExporting As DAO.Recordset
    Dim NoOfRecords As Integer
    Dim NoOfWorksheets As Integer
    Dim stDocName As String
    Dim strFilter As String
    Dim strSaveFileName As String

    strFilter = ahtAddFilterItem(strFilter, "Excel File (*.xls)", "*.xls")
    strSaveFileName = ahtCommonFileOpenSave( _
    OpenFile:=False, _
    Filter:=strFilter, _
    Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
    Me.import = strSaveFileName

    WhereTo = [Forms]![form1]![import]

    If WhereTo = "NoFile" Then Exit Sub

    DoCmd.RunMacro "BDCappend"
    Set rsExporting = CurrentDb.OpenRecordset("BDC")

    With rsExporting
    ..MoveLast
    NoOfRecords = rsExporting.RecordCount
    ..MoveFirst
    End With


    '================================================= =====
    'Insert the data from the temptable to the excel sheet
    '================================================= =====

    Dim CellRef As Integer
    Dim NoOfLoops As Integer

    openexcel ("C:\Documents and Settings\Justin\Desktop\New Folder\Logistics
    CDI v01")
    xl.UserControl = False 'Doesnt allow user any control whilst we run our update
    xl.Worksheets.SELECT 'Select the BDC Worksheet

    'This section inserts the correct number of rows into the body of the
    spreadsheet
    NoOfLoops = NoOfRecords - 1
    Do Until NoOfLoops = 0
    xl.Rows("13:13").SELECT
    xl.Selection.Insert Shift:=xlDown
    xl.Rows("12:12").SELECT
    xl.Selection.Copy 'need to copy the forumlas too, so cant just insert new rows
    xl.Rows("12:14").SELECT
    xl.ActiveSheet.Paste
    xl.Application.CutCopyMode = False 'takes the flashing cell thing away
    NoOfLoops = NoOfLoops - 1
    Loop

    'This Loop section inserts the Data
    CellRef = 12 'Starts at 12 because that is the start of the area i want to
    insert into
    NoOfLoops = NoOfRecords
    With rsExporting
    ..MoveFirst
    Do Until NoOfLoops = 0
    xl.Range("A" & CellRef & "").Value = rsExporting![CDI ID]
    xl.Range("B" & CellRef & "").Value = rsExporting![Date]
    xl.Range("J" & CellRef & "").Value = rsExporting![Corp]
    xl.Range("K" & CellRef & "").Value = rsExporting![Account#]
    xl.Range("C" & CellRef & "").Value = rsExporting![Org]
    xl.Range("E" & CellRef & "").Value = rsExporting![Locator]
    xl.Range("D" & CellRef & "").Value = rsExporting![SubInventory]
    xl.Range("H" & CellRef & "").Value = rsExporting![Box Status]
    xl.Range("G" & CellRef & "").Value = rsExporting![Serial Number]
    xl.Range("F" & CellRef & "").Value = rsExporting![Part #]
    xl.Range("I" & CellRef & "").Value = rsExporting![Operator ID]
    CellRef = CellRef + 1
    NoOfLoops = NoOfLoops - 1
    ..MoveNext
    Loop
    End With

    xl.UserControl = True 'Give control back to the user
    rsExporting.Close

    MsgBox "Exporting BDC is completed!", vbOKOnly, "Export Completed"
    DoCmd.Close A_FORM, "form1"
    xl.Visible = True

    LocalExit:
    Set xl = Nothing
    Set rsExporting = Nothing
    Exit Sub

    LocalError:
    MsgBox Err.Number & vbCr & vbCr & Err.Description
    Resume LocalExit

    End Sub
    ====================================================
    module code for openexcel:
    Option Compare Database
    Option Explicit

    Public xl As Object 'This is how you will refer to the object once it is open

    Function openexcel(strLocation)

    Set xl = CreateObject("Excel.Application")

    xl.Visible = False 'Makes the spreasheet visible. False will let you open
    'it behind the scenes

    xl.Workbooks.Add strLocation
    'xl.Workbooks.Add 'Will Create a new workbook
    End Function
     

Share This Page