Solved

compact database

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

705 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now