Run Access Macro from VB Script

[abesimpson]

For a variety of reasons I would like to start Access and various macros from a Visual Basic Script (VBS), or similar.

I have found the following code on the internet, but obviously I can't get it to work

set objaccess = createobject("Access.application")
objaccess.opencurrentdatabase ("E:\AO_VERIFICATION\AO.mdb")
objaccess.run "AO" ' >> Macro name
objaccess.CloseCurrentDatabase
set objaccess=nothing

Error:...can't find procedure AO... The macro name is AO, and the access path is correct.

Any suggestions as to how to fix it?

Thanks as always

a

 

[abesimpson]

That is what I do currently. But the databases (plural) are very large and take considerable time to complete the process. My preference would be to be able to schedule db maintenance during the evening.

 

[rconverse]

This is directly from Help in Access. If you search for compactrepair, you should be able to find it.

Function RepairDatabase(strSource As String, _
        strDestination As String) As Boolean
        ' Input values: the paths and file names of
        ' the source and destination files.

    ' Trap for errors.
    On Error GoTo error_handler

    ' Compact and repair the database. Use the return value of
    ' the CompactRepair method to determine if the file was
    ' successfully compacted.
    RepairDatabase = _
        Application.CompactRepair( _
        LogFile:=True, _
        SourceFile:=strSource, _
        DestinationFile:=strDestination)

    ' Reset the error trap and exit the function.
    On Error GoTo 0
    Exit Function

' Return False if an error occurs.
error_handler:
    RepairDatabase = False

End Function

HTH
Roger

 

[gaj104]

Personally i dont like the compact and repair approach. I use this script to compact (assuming you have closed the database):

Dim tmpext, theFile

tmpext = ".tmp" 
theFile = "C:\Your DB.mdb"

Set WshShell = wscript.CreateObject("WScript.Shell")
Set fso = CreateObject("Scripting.FileSystemObject")

CompactDB (theFile)


wscript.echo "Compacted " & dbPath

Set WshShell = Nothing
Set fso = Nothing



'Compact an access Database
Function CompactDB(dbPath)

wscript.echo "Compacting " & dbPath

Set fso1 = CreateObject("Scripting.FileSystemObject")
Set jro = CreateObject("Jro.JetEngine")
jro.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";Jet OLEDB:Database Password=", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & tmpext & ";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Password="
fso1.DeleteFile (dbPath)
fso1.MoveFile dbPath & tmpext, dbPath
Set jro = Nothing
Set fso1 = Nothing
End Function

Hope it helps