Microsoft Access Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMicrosoft Access Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old July 11th, 2005, 03:25 PM
summer brew summer brew is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2005
Posts: 12 summer brew User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 51 m 8 sec
Reputation Power: 0
Compact Database via VBA

This is the current code that I am trying to run and I am unable to because I already have that file open. Does anyone have any insight or suggestions?

Thank you ahead of time for your help!

Public Sub Compact_MDB()
Dim dbPath As String, OldDbName As String, NewDbName As String
Dim Response As Integer
dbPath = Application.CurrentProject.Path

OldDbName = "ReorderMonitoring" & ".mdb"
NewDbName = "ReorderMonitoringNew" & ".mdb"
DBEngine.CompactDatabase dbPath & "\" & OldDbName, dbPath & "\" & NewDbName

Response = MsgBox("Do you want to delete previous mdb version?", vbYesNo, "Continue")
If Response = vbYes Then
Kill dbPath & "\" & OldDbName
Name dbPath & "\" & NewDbName As dbPath & "\" & OldDbName
Else
Dim OldDbBackup As String
OldDbBackup = Mid(OldDbName, 1, Len(OldDbName) - 4) & "_" & Format(Date, "mmddyy") & ".mdb"
Name dbPath & "\" & OldDbName As dbPath & "\" & OldDbBackup
Name dbPath & "\" & NewDbName As dbPath & "\" & OldDbName
End If
End Sub

Reply With Quote
  #2  
Old July 11th, 2005, 05:30 PM
lwells lwells is offline
Contributing User
Dev Articles Novice (500 - 999 posts)
 
Join Date: Sep 2004
Posts: 632 lwells User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 21 h 59 m 38 sec
Reputation Power: 10
You can't delete or rename a database while it is open. However you can modify your code as shown below to give you three different choices

1) Make a backup copy
2) Make a new compacted copy of the database
3) Compact the current database

Public Sub Compact_MDB()
Dim dbPath As String, OldDbName As String, NewDbName As String, DbBackup As String
Dim Response As Integer, fs As Object
dbPath = Application.CurrentProject.Path
OldDbName = "ReorderMonitoring" & ".mdb"
NewDbName = "ReorderMonitoringNew" & ".mdb"
DbBackup = Mid(OldDbName, 1, Len(OldDbName) - 4) & "_" & Format(Date, "mmddyy") & ".mdb"
Response = MsgBox("Do you want to make a Back-Up copy of this Database Named " & vbCrLf & "'" & DbBackup & "'", vbYesNo, "Continue")
If Response = vbYes Then
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile dbPath & "\" & OldDbName, dbPath & "\" & DbBackup
Set fs = Nothing
Else
If MsgBox("Do You want to Compact This Database And Re-Name As " & vbCrLf & "'" & NewDbName & "'", vbYesNo, "Continue") = vbYes Then
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile dbPath & "\" & OldDbName, dbPath & "\" & DbBackup
DBEngine.CompactDatabase dbPath & "\" & DbBackup, dbPath & "\" & NewDbName
Kill dbPath & "\" & DbBackup
Set fs = Nothing
Else
If MsgBox("Do you want to just compact this Database Only?", vbYesNo, "Continue") = vbYes Then
CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction
Else
DoCmd.CancelEvent
End If
End If
End If
End Sub

Watch for line wrapping when copying and pasting the code and add the continue mark as needed.

lwells

Reply With Quote
  #3  
Old July 12th, 2005, 10:45 AM
summer brew summer brew is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2005
Posts: 12 summer brew User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 51 m 8 sec
Reputation Power: 0
Thanks, that worked like a charm.

I just have one more question:

Is it possible to auotmatically open back up to the form after Compact by placing such an action in the code?

Reply With Quote
  #4  
Old July 12th, 2005, 02:45 PM
lwells lwells is offline
Contributing User
Dev Articles Novice (500 - 999 posts)
 
Join Date: Sep 2004
Posts: 632 lwells User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 21 h 59 m 38 sec
Reputation Power: 10
Just go to Tools/StartUp and in the dropdown box Display Form/Page select the form that has your command button.

lwells

Reply With Quote
  #5  
Old April 19th, 2010, 04:20 AM
bijayani bijayani is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2009
Posts: 8 bijayani User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 34 m 58 sec
Reputation Power: 0
Compact and repair access database using VB.NET

1. Add a reference to "Microsoft Jet and Replication Objects 2.6 Library"
2. Check if the database is in use , display a warning.
3. Create a temporary folder and copy the DB
4. Create an object of JRO JetEngine and call the compact method , to compact the tempdb and replace at the original location. After compacting delete the temp folder along with its contents.

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMicrosoft Access Development > Compact Database via VBA


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap