Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

compact database

Posted on 2002-03-21
5
Medium Priority
?
1,149 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 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 300 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

824 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