Solved

compact database

Posted on 2002-03-21
5
1,130 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 58
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

630 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