?
Solved

Compact database Access97

Posted on 1998-10-01
9
Medium Priority
?
359 Views
Last Modified: 2006-11-17
Is it possible to do a "Compact databse" whith
the VBA code?
So my project comapct itself ex. every month?

0
Comment
Question by:babban
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 3

Accepted Solution

by:
dapperry earned 30 total points
ID: 1964109
Just use this statement:

DBEngine.CompactDatabase "Source.mdb", "Dest.mdb"

Let me know if you have any ?s

:) D Perry
0
 
LVL 3

Expert Comment

by:dapperry
ID: 1964110
Let me know what methodology you would want for scheduling the compact.  I might use a scheduler and have it done sometime in the middle of the night.  But there are certainly other ways of going about it.

:) D Perry
0
 

Expert Comment

by:load_o_crap
ID: 1964111
u should know that u can't compact the database that u'r currently using.

Unless of course u use the sendkeys command to call up the toolbar Database Utilites and compact database. Then there's that piece of coding missing to rename the compacted database  back to the orginal...
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:babban
ID: 1964112
Hm?
dapperry says it could go and load_o_crap says it DON'T??????

0
 
LVL 3

Expert Comment

by:dapperry
ID: 1964113
No, I actually didn't say you could do that with :

DBEngine.CompactDatabase "Source.mdb", "Dest.mdb"

That is only from an outside database.  As far as renaming, I figured you probably knew that much, but for yucks..

DBEngine.CompactDatabase "Source.mdb", "Dest.mdb"
Kill "Source.mdb"
Name "Dest.mdb" As "Source.mdb"

But if you need to do it from within the database, by all means use SendKeys as load_o_crap suggested.

:) D Perry
0
 
LVL 1

Expert Comment

by:mogray
ID: 1964114
Below is a kb artical to use the method dapperry is talking about.

Article ID: Q158937


**********************************************************************************************
The following example uses a Visual Basic procedure to compact one or more
databases, and then to close Microsoft Access when it is finished. You
cannot compact the database that is running the procedure, nor can you

compact any database that you cannot open exclusively. You must have read
and write permissions for the folder where the database you are compacting
resides, and you need enough disk space in that folder to store the
original and the compacted copies of the database.

 1. Create a new blank database called Compact.mdb.

 2. Create the following new table in Design view:

       Table: DBNames
       ----------------------------------------------------
       Field Name: DBID

          Data Type: AutoNumber (or Counter in version 2.0)
       Field Name: DBFolder
          Data Type: Text
          Field Size: 255
       Field Name: DBName
          Data Type: Text
          Field Size 255

       Table Properties: DBNames
       -------------------------
       PrimaryKey: DBID

 3. Save the table as DBNames and close it.

 4. Create a new blank form and set the following properties:

       Caption: Compact Databases
       Default View: Single Form

       Scrollbars: Neither
       RecordSelectors: No
       NavigationButtons: No
       OnTimer: [Event Procedure]
       TimerInterval: 60000

 5. Click the Build button next to the OnTimer property of the form and
    type the following procedure:

    In Microsoft Access 7.0 and 97
    ------------------------------

      Private Sub Form_Timer()
      '==================================================================
      'The Timer event runs this code every minute. It compares your

      'system time with the StartTime variable. When they match, it
      'begins compacting all databases in the DBNames table.
      '==================================================================
      Dim StartTime As String
      ' Set this variable for the time you want compacting to begin.
      StartTime = "12:00 AM"
      ' If StartTime is now, open the DBNames table and start compacting
      If Format(Now(), "medium time") = Format(StartTime, _
              "medium time") Then

         Dim RS As Recordset, DB As DATABASE
         Dim NewDBName As String, DBName As String
         Set DB = CurrentDb()
         Set RS = DB.OpenRecordset("DBNames")
         On Error Resume Next
         RS.MoveFirst
         Do Until RS.EOF
            DBName = RS("DBFolder") & "\" & RS("DBName")
            ' Create a new name for the compacted database.
            ' This example uses the old name plus the current date.
            NewDbName = Left(DbName, Len(DbName) - 4)

            NewDbName = NewDbName & " " & Format(Date, "MMDDYY") & ".mdb"
            DBEngine.CompactDatabase DBName, NewDBName
            RS.MoveNext
         Loop
      ' Close the form, and then close Microsoft Access
      DoCmd.Close acForm, "CompactDB", acSaveYes
      DoCmd.Quit acSaveYes
      End If
      End Sub

    In Microsoft Access 2.0
    -----------------------

    NOTE: In the following sample code, an underscore (_) at the end of a
    line is used as a line-continuation character. Remove the underscore

    from the end of the line when re-creating this code in Access Basic.

      Private Sub Form_Timer()
      '==================================================================
      'The Timer event runs this code every minute. It compares your
      'system time with the StartTime variable. When they match, it
      'begins compacting all databases in the DBNames table.
      '==================================================================
      Dim StartTime As String

      ' Set this variable for the time you want compacting to begin.
      StartTime = "12:00 AM"
      ' If StartTime is now, open the DBNames table and start compacting.
      If Format(Now(), "medium time") = Format(StartTime, _
              "medium time") Then
         Dim RS As Recordset, DB As DATABASE
         Dim NewDBName As String, DBName As String
         Set DB = CurrentDb()
         Set RS = DB.OpenRecordset("DBNames")
         On Error Resume Next

         RS.MoveFirst
         Do Until RS.EOF
            DBName = RS("DBFolder") & "\" & RS("DBName")
            ' Create a new name for the compacted database.
            ' This example uses the DBID plus the current date, which
            ' falls within DOS 8.3 file name limits for DBID = 1 to 99.
            NewDBName = RS("DBFolder") & "\" & RS("DBID") & Format(Date, _
                "MMDDYY") & ".mdb"
            DBEngine.CompactDatabase DBName, NewDBName

            RS.MoveNext
         Loop
      ' Close the form, and then close Microsoft Access.
      DoCmd Close a_Form, "CompactDB"
      DoCmd Quit a_Save
      End If
      End Sub

 6. Save the form as CompactDB and close it.

 7. Create a new macro with the following action:

      Action
      --------
      OpenForm

      Action Arguments
      --------------------
      Form Name: CompactDB
      View: Form
      Data Mode: Read Only
      Window Mode: Normal

 8. Save the macro as AutoExec and close it.

 9. Open the DBNames table and add a record for each database you want to
    compact. Type the full path to the database in the DBFolder field, and
    the name of the database itself in the DBName field. For example:

      DBID   DBFolder                       DBName
      ---------------------------------------------------
         1   C:\MSOffice\Access\Samples     Northwind.mdb
         2   \\Servername\Access\Sampapps   Nwind.mdb

10. Close the database, and then reopen it any time before compacting is
    scheduled to start. The AutoExec macro will automatically open the
    CompactDB form. Leave Microsoft Access running with this form open.
    At the specified time, compacting begins and when the last database is
    done, Microsoft Access closes.



Copyright (c) Microsoft Corporation. All rights reserved.

*****************************************************************************************************

Wayne

0
 

Author Comment

by:babban
ID: 1964115
OK,thanks.
0
 

Author Comment

by:babban
ID: 1964116
Adjusted points to 8
0
 

Author Comment

by:babban
ID: 1964117
Adjusted points to 10
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question