Run an access macro

Toad Data Point & Toad Intelligence Central
1

Hi,
Please help me to identify what I am doing wrong. I want to run a macro created in MS Access so in TDA ver 2.7, I created an automation task with the activity, Run Program; picture showing settings and error is attached.
Thanks. regards. MCh

RunAccessMacro.ppt (108 KB)

2

Hi Monica,

Seems like in this case you cannot use file association to launch an application and open an associated file in it. We are not smart enough here and I'll create an enhancement change request (CR) to address the problem. But you still are able to accomplish your task in a slightly different way:

  1. Submit path and name of Access executable in the Program property.
  2. Submit all the command line parameters including database file name in Arguments property.

Check attached RunMacro.jpg for details. I have Access 2007 installed on my Windows 7 machine. Paths and names may differ from what you have on your system.

Hope this helps,

Igor.
RunMacro.jpeg

3

Thanks a lot for the work around. It works beautifully.

4

Hi All,
I have similar problem with running Excel macro through “Run Program”.

Program: path to excel
Arguments: “C:\test.xls” x/Macro1

Excel is opening but macro is not running, any idea how to deal with it? What I’m doing wrong.

Br,
polorzelpolczajka

5

The commandline arg of “/x” cannot be used on an excel file only with an Access file. To fire a macro you need to use the export wizard. It will display a page that gives you a choice to fire the macro before or after exporting.

See screenshot in this post.
http://tda.inside.quest.com/thread.jspa?threadID=32519

Debbie

6

Hi,
I have used this instruction:
http://www.toadworld.com/Blogs/tabid/67/EntryId/628/Automating-Complex-Excel-Reports-new-in-version-2-7.aspx
But it showed up that I can’t open in Excel file that has been generated.

I have the following situation:

  • I need daily data refresh in excel (tabel in excel with external data connection)
  • I have connected this table through Microsoft Query (ODBC) to Oracle database
    This is how it works now:
  • I’m running script each day (it creates table with needed data from other tables)
  • opening excel file and refreshing table in excel and pivots etc

After Execute command I have used “Run program” in Toad and simple vbs script.

Program: E:\WINDOWS\system32\csscript.exe
Arguments:“E:…path to script.vbs”

SIMPLE SCRIPT BELOW:
Set objExcel = CreateObject(“Excel.Application”)
SetobjWorkbook=objExcel.Workbooks.Open(“H:\path to excel file template with macro .xls”)
objExcel.visible = true
objExcel.Run “macro name”
objExcel.Quit

Mayby it will help somebody.

Br,
polorzelpolczajka

7

I don’t know how to help you on that one. You are putting together different technologies. The first one is a linked query. With linking a query to a data base in an Excel file you would simple comfigure it to update when the user opens the file.

I have never tried running MSScript on command line.

Debbie

8

Hello,

I need help asap.Just purchased access and trying to run a MS Access query from automatation.what is the sql command to run a ms access query?

Thanks

Glenn

9

Hi Glenn,

What do you mean by RUN a query? I am assuming you‘d like to get a result set from the query.

What are you going to do with the result? Save in a file? Check the value?

Could you please be more specific?

Regards

Aleksey

10

Connect to access like any other database. Build a query in the query builder or SQL editor. In the automation designer choose the ‘Select to file’ activity and copy in your SQL. Select a file to export the data to. Access isn’t any different than any other database. You can get more info on how to use automation in the videos listed below.

dev.toadforsqlserver.com/…/TDPVideo.html