Is it somehow possible to have WebFOCUS run/execute a MS Access query from a WebFOCUS query?

Alan

You can execute dos commands, so you can start a msaccess macro too.
Is that what you want? Or do you want to use the access data in an WF report?

Alan,

An MS Query will return a resultset the same as a view in many other RDBMS. Treat it as such and create a synonym.

T

If it's just a result set you want i'd go with Tony A's suggestion.

If it's a build/update/delete table command you can get access to run a macro on start; program the macro to run your query and then shut down and then just call the access database using the DOS commands- can't help you with the WF DOS syntax (haven't used it yet) but it should be in the help file...

Ah yes, the old table update processes, the ones that are always forgotten when replying to a post Must be a Monday of something similar.

Good call Nubi and I promise to try and remember that next time - if the grey cells let me

T

Wow! You folks are great. I post at 6 p.m. (U.S. eastern time) and come back in the next morning and have 4 replies.

I think nubi hit on what I need to do. I need to have Access update a table. From there, I've created a WF synonym for the Access data and I run WF reports off the data in the Access table.

I noticed someone else posted something similar a while back in 2006 using DOS commands, but I don't think she ever got it figured out. She was trying to call an Access macro from WF (posted by cvernon on October 19, 2006 04:18 PM).

I will go looking and see what I can find in the help files. Does anyone have an example in code? (that is, calling an Access macro through WF)

alan

Alan

What i would do is create a Dos command procedure (RUNACC.BAT).
In that procedure you do what you want to do with Access.
In the Webfocus FEX you just call that BAT proces by

-DOS \\properpath\RUNACC.BAT

You might have a little problem with the path and the rights to run it, but that should be possible.

The next problem you might get, is that you need to know if the BAT proces has done what it should do. Is it possible to let Webfocus wait for the whole processing.

Did you ever consider the use of ETL?

No Problem Alan,

ill leave the DOS stuff to the experts but in terms of Access im pretty sure that you can set the macro to run on opening the .mdb and then have it close; it will be under the startup options in the tools(?) menu in access(i can't check as we don't have access at work), and that should be all you need for the table update process.

then use the BAT command to trigger the opening of the .mdb from your code as suggested.

voila!

let us know how it goes.

Here is an example that I used to run an access macro.

c:
cd c:\
cd program files
cd microsoft office
cd office
time/t >h:\period.txt
msaccess.exe c:\mydir\theaccessprog.mdb /x startmacro
time/t >>h:\period.txt

the commands "time" set a timestamp in the file period.txt

I would like to know if you get this working.

why not just use SQL to update your msaccess table?

One command, less points of failure...and no worry that a systems fella won't come through and delete your .bat file because he figured it was used for the install of a program and isn't needed any more. As a note on this topic, does focus support a Dos shell?

quote:

I post at 6 p.m. (U.S. eastern time) and come back in the next morning and have 4 replies.

That will be the European posters that help make this Forum truly International!

T

-In Access, create a macro called "autoexec"
-In the macro:
-set warnings off
-run the query that updates the table
-set warnings on

The autoexec macro will run automatically everytime you open the .mdb, thus triggering the update. All you need to do is write a dos batch file that opens the database and you are laughing.

Moogle

if you create a macro that is called "autoexec" then that proces will run every time you start the access application.

See my former message to prevent that.
Create a macro with an other name than Autoexec.