Thread: 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.

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

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=t1 adm"
.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)&""&c hr(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

Originally Posted by pr4t3ek

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.

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!