Compacting and Repairing and Creating Backup using VBA? 1

[VBUser77]

How can I compact and repair my database and make a back up copy using VBA code?

Thanks in advance.

Jay

 

[SiJP]

The code you need is DBEngine.CompactDatabase to compact the db, then to create a backup, use the FileSystemObject to copy your db.



------------------------
Hit any User to continue

 

[VBUser77]

When I use

DBEngine.CompactDatabase "Currentlocation", "copy location"

it runs fine for the first time but when i try to run it for the second time, it gives me an error that says the database already exists.

How can i fix it. I know I am getting the error because it's trying to copy the database second time with the same name that it used to copy for the first time. But I like to run the compact command without me deleting any same name databasefiles before running this command.

Thanks a lot in advance.

Jay

 

[SiJP]

Check for the "copy location" file prior to running the compact, and use vba to rename this file to somthing like oldbackup_database.mdb

Then when the code is finished compacting you can delete it. or not. up to you!



------------------------
Hit any User to continue

 

[VBUser77]

Do you the syntax for renaming the access file in VBA. Thanks a lot for your help

Jay

 

[SiJP]

Sure. Use the FSO, as per your other thread.

fso.RenameFile





------------------------
Hit any User to continue

 

[mordja]

VBUser,

I have a lot of databases in a folder at work. I compact them weekly. Here is a function I wrote to compact all databases in a given folder


Public Function CompactDB(ByVal dirPath As String)
'Compacts all databases in given dir

Dim S As Variant
Dim je As New JRO.JetEngine
Dim fso As FileSystemObject
Dim fsoFolder As Folder
Dim fsoFile, tempFile As File
Dim tempName As String

'Make a new File System object.
Set fso = New FileSystemObject
' Get the FSO Folder (directory) object.
If Len(Dir(dirPath)) Then
Set fsoFolder = fso.GetFolder(dirPath)

For Each fsoFile In fsoFolder.Files
If Right(fsoFile.name, 4) = ".mdb" Then
je.CompactDatabase _
SourceConnection:="Data Source=" & dirPath & "\" & fsoFile.name, _
DestConnection:="Data Source=" & dirPath & fsoFile.name & "temp.mdb; "
tempName = fsoFile.name
fsoFile.Delete
Name (dirPath & tempName & "temp.mdb") As (dirPath & tempName)
End If
Next fsoFile
Else
MsgBox "Invalid Path"
End If


You can use Name as I have to rename files.

Mordja