+ Reply to Thread
Results 1 to 7 of 7

Refresh a mysql query

  1. #1
    Registered User
    Join Date
    09-06-2009
    Location
    Christchurch, New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    7

    Question Refresh a mysql query

    Hi all,

    I have a mysql query which I have written and then pasted into the SQL window of MSQuery - the query works well and gives me the result that I need.

    What I would like to do is develop the solution further so that the user can input a date and have that flow through to the query via vba (or another method if anyone can suggest one).

    I have done this before with quite small simple queries but this particular one is very lengthy and I seem to have some up against some sort of maximum query length inside the vba code. The message I get is something about too many line continuations.

    Can anyone shed some light on how I might be able to solve this??

    Many thanks.

  2. #2
    Registered User
    Join Date
    09-06-2009
    Location
    Christchurch, New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: writing a macro to refresh a mysql query

    Does anyone have any ideas on this one at all???

  3. #3
    Forum Contributor pr4t3ek's Avatar
    Join Date
    10-13-2008
    Location
    Melbourne, Australia
    MS-Off Ver
    2003 & 2007, 2010
    Posts
    483

    Re: writing a macro to refresh a mysql query

    Hi!


    How are you getting data from MSQuery? Pivot table via external datasource or Data>Import External Data?

    If using Data>Import External Data...
    you can right click your results and go to Data Range Properties>Check refresh data on file open

    Pivot table
    right click pivot>table options>Refresh on open
    --
    Regards
    PD

    ----- Don't Forget -----

    1. Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks

    2. Thank those who have helped you by Clicking the scales above each post.

    3. Please mark your post [SOLVED] if it has been answered satisfactorily.

  4. #4
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264

    Re: writing a macro to refresh a mysql query

    Hi

    Can you post the code you are using - thanks.

    Richard
    Richard Schollar
    Microsoft MVP - Excel

  5. #5
    Registered User
    Join Date
    09-06-2009
    Location
    Christchurch, New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Refresh a mysql query

    Hi,

    I am recording the macro and I get this far before I get the error:


    Sub Macro2()
    '
    ' Macro2 Macro
    '

    '
    Range("C9").Select
    With Selection.ListObject.QueryTable
    .Connection = _
    "ODBC;DATABASE=ncs;DESCRIPTION=whitestone;DSN=whitestone;OPTION=0;;PORT=0;SERVER=192.168.51.3;UID=t1adm"
    .CommandText = Array( _
    "select"&chr(13)&""&chr(10)&" 'Revenue' AS '_ '"&chr(13)&""&chr(10)&" ,gl_func_revenue(1,200909) AS 'Roading & Construction'"&chr(13)&""&chr(10)&" ,gl_func_revenue(7,200909) AS 'Works'"&chr(13)&""&chr(10)&" ,gl_func_revenue(5,200909) AS 'Landscape'"&chr(13)&""&chr(10)&" ,gl_func_revenue(2,200909" _
    , _
    ") AS 'Mackenzie'"&chr(13)&""&chr(10)&" ,gl_func_revenue(6,200909) AS 'Waimate'"&chr(13)&""&chr(10)&" ,gl_func_revenue(8,200909) AS 'Quarries'"&chr(13)&""&chr(10)&" ,gl_func_revenue(9,200909) AS 'Auxilary'"&chr(13)&""&chr(10)&" ,gl_func_revenue(3,200909) AS 'Concul'"&chr(13)&""&chr(10)&"UNION"&chr(13)&""&chr(10)&"se" _
    , _
    "lect"&chr(13)&""&chr(10)&" 'Expense'"&chr(13)&""&chr(10)&" ,gl_func_expense(1,200909)"&chr(13)&""&chr(10)&" ,gl_func_expense(7,200909)"&chr(13)&""&chr(10)&" ,gl_func_expense(5,200909)"&chr(13)&""&chr(10)&" ,gl_func_expense(2,200909)"&chr(13)&""&chr(10)&" ,gl_func_expense(6,200909)"&chr(13)&""&chr(10)&" ,gl_func_expense(8,200909)"&chr(13)&""&chr(10)&" " _
    , _
    " ,gl_func_expense(9,200909)"&chr(13)&""&chr(10)&" ,gl_func_expense(3,200909)"&chr(13)&""&chr(10)&"UNION"&chr(13)&""&chr(10)&"select"&chr(13)&""&chr(10)&" 'Total Jobs'"&chr(13)&""&chr(10)&" ,gl_func_revenue(1,200909) + gl_func_expense(1,200909)"&chr(13)&""&chr(10)&" ,gl_func_revenue(7,200909) + gl_func_expense(7,200" _
    , _
    "909)"&chr(13)&""&chr(10)&" ,gl_func_revenue(5,200909) + gl_func_expense(5,200909)"&chr(13)&""&chr(10)&" ,gl_func_revenue(2,200909) + gl_func_expense(2,200909)"&chr(13)&""&chr(10)&" ,gl_func_revenue(6,200909) + gl_func_expense(6,200909)"&chr(13)&""&chr(10)&" ,gl_func_revenue(8" _
    , _
    ",200909) + gl_func_expense(8,200909)"&chr(13)&""&chr(10)&" ,gl_func_revenue(9,200909) + gl_func_expense(9,200909)"&chr(13)&""&chr(10)&" ,gl_func_revenue(3,200909) + gl_func_expense(3,200909)"&chr(13)&""&chr(10)&"UNION"&chr(13)&""&chr(10)&"select"&chr(13)&""&chr(10)&" 'Direct Overheads'"&chr(13)&""&chr(10)&" ,gl_fun" _
    , _
    "c_dir_overheads(1,200909)"&chr(13)&""&chr(10)&" ,gl_func_dir_overheads(7,200909)"&chr(13)&""&chr(10)&" ,gl_func_dir_overheads(5,200909)"&chr(13)&""&chr(10)&" ,gl_func_dir_overheads(2,200909)"&chr(13)&""&chr(10)&" ,gl_func_dir_overheads(6,200909)"&chr(13)&""&chr(10)&" ,gl_func_dir_overheads(8,20" _
    , _
    "0909)"&chr(13)&""&chr(10)&" ,gl_func_dir_overheads(9,200909)"&chr(13)&""&chr(10)&" ,gl_func_dir_overheads(3,200909)"&chr(13)&""&chr(10)&"UNION"&chr(13)&""&chr(10)&"select"&chr(13)&""&chr(10)&" 'Contribution'"&chr(13)&""&chr(10)&" ,(gl_func_revenue(1,200909) + gl_func_expense(1,200909)) + gl_func_dir_overheads(1,200" _
    , _
    "909)"&chr(13)&""&chr(10)&" ,(gl_func_revenue(7,200909) + gl_func_expense(7,200909)) + gl_func_dir_overheads(7,200909)"&chr(13)&""&chr(10)&" ,(gl_func_revenue(5,200909) + gl_func_expense(5,200909)) + gl_func_dir_overheads(5,200909)"&chr(13)&""&chr(10)&" ,(gl" _
    , _
    "_func_revenue(2,200909) + gl_func_expense(2,200909)) + gl_func_dir_overheads(2,200909)"&chr(13)&""&chr(10)&" ,(gl_func_revenue(6,200909) + gl_func_expense(6,200909)) + gl_func_dir_overheads(6,200909)"&chr(13)&""&chr(10)&" ,(gl_func_revenu" _
    , _
    "e(8,200909) + gl_func_expense(8,200909)) + gl_func_dir_overheads(8,200909)"&chr(13)&""&chr(10)&" ,(gl_func_revenue(9,200909) + gl_func_expense(9,200909)) + gl_func_dir_overheads(9,200909)"&chr(13)&""&chr(10)&" ,(gl_func_revenue(3,200909) " _
    , _
    "+ gl_func_expense(3,200909)) + gl_func_dir_overheads(3,200909)"&chr(13)&""&chr(10)&"UNION"&chr(13)&""&chr(10)&"select"&chr(13)&""&chr(10)&" 'Workshop Costs'"&chr(13)&""&chr(10)&" ,gl_func_workshop_costs(1,200909)"&chr(13)&""&chr(10)&" ,gl_func_workshop_costs(7,200909)"&chr(13)&""&chr(10)&" ,gl_func_workshop_costs(5" _
    End Sub


    Many thanks

  6. #6
    Registered User
    Join Date
    09-06-2009
    Location
    Christchurch, New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: writing a macro to refresh a mysql query

    Quote Originally Posted by pr4t3ek View Post
    Hi!


    How are you getting data from MSQuery? Pivot table via external datasource or Data>Import External Data?

    If using Data>Import External Data...
    you can right click your results and go to Data Range Properties>Check refresh data on file open

    Pivot table
    right click pivot>table options>Refresh on open
    What I am trying to do is change the query based on a yyyymm criteria supplied by the user - the code is currently hard coded for September 2009 (200909) but I need to be able to change this on the fly.

  7. #7
    Forum Contributor
    Join Date
    09-22-2009
    Location
    Indianapolis, Indiana
    MS-Off Ver
    Office 2003 / Office 2007
    Posts
    114

    Re: Refresh a mysql query

    Ouch. Hideous and unmaintainable come to mind. I'd suggest using MS Query or SSMS and redoing what that query is attempting to do, then just using ADO to copy it out to the worksheet. Once you have the correct query, its actually pretty trivial to copy the recordset to a range using ADO or to modify the query dynamically based on dates or other criteria.

    If you find that you have a lot of columns to include and do not want *some* of those columns, I'd just dump the entire thing into a spreadsheet provided its not some absurd amount of data, and delete the data that you do not need. Its much easier to select * from x and delete 2 columns out of 30, than it is to specify 39 different columns.

    Additionally, just as a guess here...

    It looks like the date is part of the column header which is also a bit scary. If that's the case I'd suggest correcting that so that the date and department are two separate columns.

    If you have no choice but to get this working as it was given to you, good luck!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1