VB coding to Compress Microsoft Access Databases 2

[PJname]

Can someone direct me to where I can find VB coding to be included in a Microsoft Access Database to run a compress on other MS Access databases which are located in different directories and naming conventions?

What I would like the VB coding to do - is to include this action in my Autoexec file as the last step after it completes my previous actions to select MS Access
database(s) located in other directories, perform a compact on these database(s), assign the compacted database TO a different filename, deleting the old MS database compacted from and renaming the new compacted database name with the old MS Access name.

Thanks in Advance.

 

[Ed2020]

Sub CompactDB()
On Error GoTo CompactDB_Err
Const conFilePath = <put your directory path here>

DBEngine.CompactDatabase conFilePath & "<put your filename here>", conFilePath & "<put temporary filename here>"

If Dir(conFilePath & "<filename without extension>.bak") <> "" Then
Kill conFilePath & "<filename without extension>.bak"
End If

Name conFilePath& "<put your filename here>" As conFilePath &"<filename without extension>.bak"
Name conFilePath & "<put temporary filename here>" As conFilePath & "<put your filename here>"

Exit_CompactDB:
Exit Sub

CompactDB_Err:
MsgBox Err.Description
Resume Exit_CompactDB

End Sub

Please do not feed the trolls.....

 

[PJname]

Hello Ed2020

trying to use the VB coding you sent me but receiving compile errors. I am not a VB code person expert so this is what I plugged in:


General information:
directory location: J:\oschd\access\pam

Database name & compact from: testcompCODE.mdb
Compact to: testcompCODE1.mdb

this is where I tried to plug in the information within the coding but confusion started.....


Sub CompactDB()
On Error GoTo CompactDB_Err
Const conFilePath = "j:\OSCHD\ACCESS\pam"

DBEngine.CompactDatabase conFilePath & "testcompactCODE.mdb", conFilePath & "testcompactCODE1.mdb"

If Dir(conFilePath & "testcompactCODE.bak") <> "" Then
Kill conFilePath & "testcompactCODE.mdb"
End If

Name conFilePath& "testcompactCODE1.mdb" As conFilePath &"testcompactCODE.mdb"
Name conFilePath & "<put temporary filename here>" As conFilePath & "<put your filename here>"

Exit_CompactDB:
Exit Sub

CompactDB_Err:
MsgBox Err.Description
Resume Exit_CompactDB

End Sub

 

[B827]

Try
Const conFilePath = "j:\OSCHD\ACCESS\pam\"


Sandy

 

[PHV]

And I'd replace this:
Name conFilePath& "testcompactCODE1.mdb" As conFilePath &"testcompactCODE.mdb"
Name conFilePath & "<put temporary filename here>" As conFilePath & "<put your filename here>"
with this:
Name conFilePath & "testcompactCODE.mdb" As conFilePath & "testcompactCODE.bak"
Name conFilePath & "testcompactCODE1.mdb" As conFilePath & "testcompactCODE.mdb"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PJname]

Thanks to all for your assistance in providing VB code for compacting databases.

I have the following code and it seems to be working for compacting MS Access Databases.....I will set this up in my autoexec file to run as the last step.


' This is used to compact the [Daily_Scheduling_Tables.mdb] Access Database after tables have been updated after the morning run.

Function CompactDB1()
On Error GoTo CompactDB1_Err
Const conFilePath = "J:\shared\SCHED\"

DBEngine.CompactDatabase conFilePath & "Daily_Scheduling_Tables.mdb", conFilePath & "Daily_Scheduling_Tables1.mdb"

' If Dir(conFilePath & "testcompactCODE1.bak") <> " " Then
' Kill conFilePath & "testcompactCODE1.bak"
' End If

Name conFilePath & "Daily_Scheduling_Tables1.mdb" As conFilePath & "Daily_Scheduling_Tables1.bak"

If Dir(conFilePath & "Daily_Scheduling_Tables.mdb") <> " " Then
Kill conFilePath & "Daily_Scheduling_Tables.mdb"
End If

Name conFilePath & "Daily_Scheduling_Tables1.bak" As conFilePath & "Daily_Scheduling_Tables.mdb"

Exit_CompactDB1:
Exit Function

CompactDB1_Err:
MsgBox Err.Description
Resume Exit_CompactDB1

End Function