Solved

compact database

Posted on 2002-03-21
5
1,122 Views
Last Modified: 2008-03-04
hi;

I want to compact my database right before the user close the database using VBA. Compacted database has the same name as old database in the same folder. 'DoCmd.RunCommand acCmdCompactDatabase' is not option, because of user interaction and can not give the same names. In Access menu there is 'Tools->Securities->Compact Database' option, but the application starts again after the compact. This is not my expectation.
0
Comment
Question by:tubst
[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
5 Comments
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6885391
Hi tubst,

This is a copy&paste from your profile:
Knowledgebase Stats:    
Questions  
Questions Asked 50
Last 10 Grades Given A B C B B B A A B C  
Question Grading Record 39 Answers Graded / 40 Answers Received


Please resolve your forgotten qestions.

If you need any assistance with that, please post a 0 (zero) points question at the community support topic area, adding the link to the Qestion you need help with, and a short description of the problem, and a moderator will help you in no time.

Thanx!

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6885417
Regarding your question:

I suggest thet you create a new table to hold just one parameter (True/False).

The usual value of the field will be True. On the Application load event (or the OnLoad event of your switchboard), add a routine like the following pseodo-code:

If TheFieldsValueInTable = True then
   Activate Application
Else
   TheFieldsValueInTable = True
   Quit Application
End If

Before you start the Compact (on exit) you should do:
TheFieldsValueInTable = False
CompactMyDB

Clear?

Nosterdamus
0
 
LVL 57
ID: 6885431
You cannot compact the database that is currently open.  Instead, you must call another MDB with the name of the database to be compacted in some way (ie. an INI file) and have it do the actual compaction.  There is code available to do this from several sources, which I can list if you want to go this route.

Also, in A2000 and up, there is now an option to compact on close.  After the last person exits, the database is acutomatically compacted.

Jim.
0
 
LVL 1

Accepted Solution

by:
genehead earned 100 total points
ID: 6885874
If I understand what you want to do, here is how I do it.

(Cut from working code)


Dim sPath, sFile, sPW as String
sPath = "C:\MyPath\"
sFile = "MyFile.MDB"
sPW = "PassWord"


compactDB(sPath & sFile, sPath & "Temp_" & sFile)

Public Function compactDB(ByVal SOUR_path As String, _
                          ByVal DEST_path As String) As Boolean
                         

compactDB = True  ' Assume success

'  We pass the fully qualified file names including path

'  This requires a VB reference to:
'      Microsoft Jet and Replication Objects Library
On Error GoTo Err_compact
Dim JRO As New JRO.JetEngine

' Source and Destination connection path
Dim DB_sour As String, DB_dest As String

'  Check for keypress
DoEvents

'  Connection string
DB_sour = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& SOUR_path & " ;Jet OLEDB:Database Password=" & sPW
DB_dest = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& DEST_path & " ;Jet OLEDB:Database Password=" & sPW _
& " ;Jet OLEDB:Engine Type=5"

'  Main compact method
JRO.CompactDatabase DB_sour, DB_dest

'  We created a temp file.
'  Delete the original and rename the temp.
Call RenameFile(SOUR_path, DEST_path)

Exit Function

Err_compact:
    Err.Clear
    compactDB = False
    HasCompactErrors = True
   
    'write error to pilot log
errorreporting ("Problem . . .Compact Error ")

End Function

Sub RenameFile(DeleteThisOne, RenameThisOne)
    Dim fs, f, s, notfound
    notfound = False
   
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(DeleteThisOne)
    f.Delete
    Name RenameThisOne As DeleteThisOne
   
End Sub
0
 

Author Comment

by:tubst
ID: 6888142
thanks, genehead!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

751 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