Execute MS Access "MAKE TABLE" Query from Script?

[#1]

Hiys Guys,

I need to load data from an MS Access Query which uses in-built VBA Module functions (which are not recogniced via ODBC: http://support.microsoft.com/kb/166113).

Therefore, is it possible to first execute a "MAKE TABLE" Query from QV Script (which will run 'inside' Access) and then ODBC to the resulting Table?

I am guessing not - but thought I'd ask before rewriting all my in-built functions using "IIF" statements in the QBE grid! (*grind*)

If anyone has any other suggestions then please let me know!

Cheers,

Steve.

[#2]

Just in case anyone else has this problem - ODBC doesn't like ANY functions (there may be a way to run a select few - but nowhere near as complex as I require, eg: IIF and NZ are not recognised).

Therefore I am looking into importing the raw data using my Script - and then performing the data transformation using user-defined functions via a Macro (not ideal... but no one else seems to have any other suggestions!)  🙂

If I get this working I will post my solution here for future reference.

Steve.

[#3 · Accepted solution]

The solution I have come-up with is as above (performing the data transformation using user-defined functions via a Macro) - however another solution (and possibly a better solution) is to use variables within the Script instead of Macro functions (see this thread: http://community.qlik.com/thread/58791?tstart=0).

I'd be interested to know if either of these two threads actually help another lost-soul at some point... please let me know!  🙂

Steve.

[felix18807]

Hi Steve,

I'm having issue which relate to this post. Long story short the IT team will only let me access certain data via qvd files (paranoia about letting mere data analysts having access to the live SQL tables). I then have to export the data into MSAccess to do various functions on and then use the MSAccess tables to create qvd files for other users to use.

I hope you have managed to resolve your issues.

Felix

[#5]

Hiya felix18807,

Yes, I "worked-around" the issue by rewriting my MS Access functions in a VBS macro... not ideal, but it worked.  However, it may have been more reusable (from a front-end point-of-view) if I had been able to rewrite them using Variables... although being a Software Engineer by trade I find it more traceable to use extensive Comments in my code (which is not really available within a variable - only on the first line I have found!).  Crazy.

Were you after some further advice? - or have you found a satisfactory solution?... I hope this post has helped!  🙂

Cheers,

Steve.