Focal Point
have wf execute ms access query

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6811032372

February 24, 2008, 06:09 PM
alandool
have wf execute ms access query
Is it somehow possible to have WebFOCUS run/execute a MS Access query from a WebFOCUS query?

Alan
February 25, 2008, 02:12 AM
FrankDutch
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?




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

February 25, 2008, 04:08 AM
Tony A
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



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
February 25, 2008, 04:23 AM
nubi
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...


Developer Studio 7.64
Win XP
Output: mostly HTML, also Excel and PDF

"Never attribute to malice that which can be adequately explained by stupidity." - Heinlein's Razor
February 25, 2008, 05:02 AM
Tony A
Ah yes, the old table update processes, the ones that are always forgotten when replying to a post Roll Eyes 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 Wink

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
February 25, 2008, 06:28 AM
alandool
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
February 25, 2008, 06:47 AM
FrankDutch
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?




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

February 25, 2008, 07:01 AM
nubi
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.


Developer Studio 7.64
Win XP
Output: mostly HTML, also Excel and PDF

"Never attribute to malice that which can be adequately explained by stupidity." - Heinlein's Razor
February 25, 2008, 07:20 AM
FrankDutch
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.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

February 25, 2008, 09:32 AM
Jason K.
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?


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
February 25, 2008, 09:32 AM
Tony A
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 FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
February 27, 2008, 04:31 PM
Moogle
-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.


-WebFOCUS 8.2.01 on Windows
February 27, 2008, 05:40 PM
FrankDutch
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.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7