VBA ADODB Connection -SQL QUERY

Question

I am Running in VBA But same is not working. Getting error no Value Given for one or more Required Parameter.

My Query :

esql = "Select MAPPING_MONTH.DT,SUM(MAIL_ACTIVE_Base_Tracker_GA_AM_AO.COUNT) as GA_COUNT From MAPPING_MONTH A LEFT OUTER JOIN MAIL_ACTIVE_Base_Tracker_GA_AM_AO B on A.DT=B.DATE where ((MAIL_ACTIVE_Base_Tracker_GA_AM_AO.CIRCLE)= " & """" & FName & "GROUP BY MAIL_ACTIVE_Base_Tracker_GA_AM_AO.CIRCLE"")"

RS.Open (esql), CN, adOpenStatic, adLockReadOnly

I notice that you are aliasing the tables A and B but only using the alias in the join condition, not in the select, where or group by clauses. This may be causing the problem. — UberDoodles
– UberDoodles, Commented May 10, 2016 at 12:17
The error is probably caused by the line: """" & FName & "GROUP BY MAIL_ACTIVE_Base_Tracker_GA_AM_AO.CIRCLE"")"... you need a space between FName and GROUP BY. — C. White
– C. White, Commented May 10, 2016 at 14:27

Ralph · Accepted Answer · 2016-05-10 13:05:29Z

While there is some code missing which could be helpful in debugging the code here are some improvements which (potentially) already resolve your problem. If that's not the case then you might want to consider updating your post and include more of your code (where you declare RS and CN and setup the ADODB.Connection).

Having said that here are the suggested changes:

esql = "      select  MAPPING_MONTH.DT, "
esql = esql & "        sum(MAIL_ACTIVE_Base_Tracker_GA_AM_AO.COUNT) as GA_COUNT "
esql = esql & "from    MAPPING_MONTH A "
esql = esql & "left outer join MAIL_ACTIVE_Base_Tracker_GA_AM_AO B "
esql = esql & "on      A.DT = B.DATE "
esql = esql & "where   MAIL_ACTIVE_Base_Tracker_GA_AM_AO.CIRCLE = N'" & FName & "' " 
sql = esql & "group by MAIL_ACTIVE_Base_Tracker_GA_AM_AO.CIRCLE "

RS.Open esql, CN, adOpenStatic, adLockReadOnly

Changes:

Remove the brackets around esql in the RS.Open line.
Remove the brackets inside the constructed T-SQL statement
Exchange the " inside your T-SQL for ' to include the file name as text.

Collectives™ on Stack Overflow

VBA ADODB Connection -SQL QUERY

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related