Run an MS Access Macro - How?

Databases
1

I am trying to convert some of my VB6 apps over to RBasic. These are small “automation” VB apps that open a MS Access database, run a macro in it and then close it.

Here is the code of one of them…

Sub Main()
'To open ms access and run a macro
'Used on the server to replace the batch file named CSCWeekly.bat
'
'Created Oct 20, 2010 - Rick Yerex
' Winserver 2008 & Task Scheduler seem to have problems running batch files
'
Dim strDB As String
Dim strMac As String

'strDB = "C:\\ipcc\\eomreports\\eomreporting.mdb"
'strMac = "mTEST"
strDB = "E:\\ReportFiles\\Report_KBIS2012.mdb"
strMac = "mPrintCSCWeeklyAuto"

RunAccessMacro strDB, strMac, , True

End Sub


Private Sub RunAccessMacro(ByVal strDB As String, ByVal strMacro As String, _
                        Optional lngRunX As Long = 1, _
                        Optional CloseOnComplete As Boolean = True)

On Error GoTo Hell

'Create a Access object
Dim objAccess As Access.Application
Set objAccess = New Access.Application

'Open the database and run the macro
With objAccess
    .OpenCurrentDatabase strDB
    .DoCmd.RunMacro strMacro, lngRunX

    'Close the Database
    If CloseOnComplete Then .CloseCurrentDatabase
End With

Set objAccess = Nothing

Exit_For:
    On Error GoTo 0
    Exit Sub

Hell:
    MsgBox "sum ting wong"
    GoTo Exit_For

End Sub

I can’t seem to figgure out how to accomplish this in RBasic. Can anyone assist pls?

2

I don’t know if you can do this but you may be able to work directly with the database in XOJO using ODBC and do the work in RBasic. I certainly use Xojo to retrieve information and insert it into an access table which I do with a couple of systems.

The other option is to perhaps launch msaccess with the macro supplied as a command line parameter.

msaccess.exe /ro “c:\MyFolder\MyDatabase.accdb” /x Macroname

3

[quote=99361:@Paul Budd]I don’t know if you can do this but you may be able to work directly with the database in XOJO using ODBC and do the work in RBasic. I certainly use Xojo to retrieve information and insert it into an access table which I do with a couple of systems.

The other option is to perhaps launch msaccess with the macro supplied as a command line parameter.

msaccess.exe /ro “c:\MyFolder\MyDatabase.accdb” /x Macroname[/quote]

Paul, I know how to do this from within a batch file, what I don’t know is how to launch msaccess.exe from code in RealBasic.

4

FolderItem.Launch?

Launch

5

shell.execute("c:\\windows\\programs\\msaccess.exe /ro "c:\\MyFolder\\MyDatabase.accdb" /x Macroname")

6

Michel beat me to it. More info here btw:

http://office.microsoft.com/en-us/access-help/command-line-switches-for-access-HA010166605.aspx

7

Thanks guys!!

8

After some testing, the actual statement that works is …

Dim s as New Shell

s.Execute(“start msaccess.exe E:\ReportFiles\Report_KBIS2012.mdb /x mactestshell”)