INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- Turn Off Ad Banners
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Member Feedback
"...I have answered some questions and have gotten answers for my questions. Anywhere you can do this on one page helps tremendously..."
Geography
Where in the world do Tek-Tips members come from?
|
Microsoft: Access Modules (VBA Coding) FAQ
|
Compacting Databases
|
How do I compact my Access 97 database from code?
Posted: 22 May 02 (Edited 14 Jun 02)
|
The simple answer is "You can't".
You cannot compact the database you are in through code in Access 97. To work round this I created this VBscript.
Copy all the text into a text file and call it CompactDB.vbs or something.
'**Start**
Dim objEngine
Dim objDB
Dim strDb1, strPath
Dim FSO
'Path to database: In this case the same as the script location
strPath=left(wscript.scriptfullname,len(wscript.scriptfullname)-len(wscript.scriptname))
strDb1 = strpath & "Data.mdb"
Set FSO = CreateObject("Scripting.FileSystemObject")
do while FSO.FileExists(Left(strDb1, len(strDb1)-3) & "ldb")=True
'As long as an ldb file exists, someone is in the database, so just do nothing until it goes.
loop
if msgbox("About to perform some basic self maintenance." & chr(10) & "It is vital you do not exit windows until you receive the confirmation message." & chr(10) & "Press Ok to continue or Cancel to stop the process.",1,"Impact XP")=1 then
'let them know we are about to start
Set objEngine = WScript.CreateObject("DAO.DBEngine.35")
call CompactDB(FSO, objEngine, strDB1, "")
msgbox "File maintenance complete"
end if
Function CompactDB (objFSO, objEngine, strDb, pwd)
'compact db
strdbtemp = Left(strDb, len(strDb)-3) & "ldb"
If FSO.FileExists(strdbTemp)=True then 'if ldb file exists, db is still open.
msgbox "You have not exited the file. Please close and try again."
Exit Function
End if
if FSO.FileExists(strDb1) = False then
Exit Function
End If
strdbtemp = Left(strDb, len(strDb)-3) & "tmp"
if pwd="" then
objEngine.CompactDatabase strDb, strdbtemp
else
objEngine.CompactDatabase strDb, strdbtemp, , , ";pwd=" & pwd
end if
If Err <> 0 Then Exit Function
fso.deletefile strdb
fso.copyfile strdbtemp, strdb
fso.deletefile strdbtemp
End Function
'**End**
In your database you call the code using the fHandleFile function found here:
FAQ705-1971
It waits until you are out of the database and then compacts it for you.
Easy when you know how!
Ben |
Back to Microsoft: Access Modules (VBA Coding) FAQ Index
Back to Microsoft: Access Modules (VBA Coding) Forum
My FAQ Archive
 Email This FAQ To A Friend |
|
 |
|
Join Tek-Tips® Today!
Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.
Here's Why Members Love Tek-Tips Forums:
Talk To Other Members- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close
