4 Replies Latest reply: Oct 8, 2012 11:20 AM by Steven Bain RSS

    Execute MS Access "MAKE TABLE" Query from Script?

    Steven Bain

      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.

        • Re: Execute MS Access "MAKE TABLE" Query from Script?
          Steven Bain

          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.

          • Re: Execute MS Access "MAKE TABLE" Query from Script?

            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

              • Re: Execute MS Access "MAKE TABLE" Query from Script?
                Steven Bain

                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.