Convert SQl to VBA so that it executes in VBA

[RB Smissaert]

Don't know what database you are running it on, but it looks all you have to
do is
remove the double quotes and and then just run the SQL.
So for example with ADO you will get something like this:

Set rs = New ADODB.Recordset
rs.Open source:=strSQL, _
ActiveConnection:=ADOConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText

Where strSQL a string variable is, holding your big query.


RBS

 

[RB Smissaert]

Make longer lines or break it up in a number of string variables and
concatenate them.

RBS

 

[todd.huttenstine]

Hey

I need to make the following-working SQL query run from a VBA module
and then export all the reults to an Excel file/tab. I dont know if
this involves allot of conversion or simply setting this SQL equal to
a string and then running it... Any input what-so-ever is
appreciated.

Select T1."SLS_OUTLET_ID", T2."SLS_OUTLET_NM",
T3."SLS_DIST_CHNL_TYPE_DESC", T1."CUST_ID", T4."ACCESS_AMT",
T1."PPLAN_CD", T4."PPLAN_DESC", T1."ACCT_NUM", T1."MTN", T1."MKT_CD",
T5."MKT_NAME", T6."BUS_NM", T1."NM_FIRST", T1."NM_LAST",
T1."STATE_CD", T1."SLS_PRSN_ID", T7."SLS_PRSN_NM", T8."ACTIVITY_CD",
T1."LINE_ACT_DT", T1."LINE_TERM_DT", T1."EQP_PROD_ID", T1."PROD_NM",
T1."CNTRCT_TERM_DT", T1."PREPAID_IND", T1."DEACT_CHANGE_REAS_CD",
T9."CHANGE_REAS_DESC", T10."RLTD_ACCT_ID", T11."AREA_CD",
T11."REGION_CD", T12."PPLAN_SHARE_DESC"
From SDW_PRD_ALLVM."REGION_V" T11,
(((((((((("SDW_PRD_ALLVM"."CUST_ACCT_LINE_V" T1 LEFT OUTER JOIN
"SDW_PRD_ALLVM"."CUST_ACCT_V" T6
On T1."SOR_ID" = T6."SOR_ID"
And T1."CUST_ID" = T6."CUST_ID"
And T1."ACCT_NUM" = T6."ACCT_NUM") LEFT OUTER JOIN
"SDW_PRD_ALLVM"."DLY_LINE_ACTIVITY_V" T8
On T1."CUST_ID" = T8."CUST_ID"
And T1."CUST_LINE_SEQ_ID" = T8."CUST_LINE_SEQ_ID"
And T1."SOR_ID" = T8."SOR_ID") LEFT OUTER JOIN
"SDW_PRD_ALLVM"."OUTLET_V" T2
On T1."SOR_ID" = T2."SOR_ID"
And T1."SLS_OUTLET_ID" = T2."SLS_OUTLET_ID") LEFT OUTER JOIN
"SDW_PRD_ALLVM"."SALES_DIST_CHANNEL_TYPE_V" T3
On T2."SOR_ID" = T3."SOR_ID"
And T2."SLS_DIST_CHNL_TYPE_CD" = T3."SLS_DIST_CHNL_TYPE_CD") LEFT
OUTER JOIN "SDW_PRD_ALLVM"."PRICE_PLAN_V" T4
On T1."PPLAN_CD" = T4."PPLAN_CD"
And T1."SOR_ID" = T4."SOR_ID"
And T1."PPLAN_MKT_CD" = T4."PPLAN_MKT_CD") LEFT OUTER JOIN
"SDW_PRD_ALLVM"."MARKET_V" T5
On T1."SOR_ID" = T5."SOR_ID"
And T1."MKT_CD" = T5."MKT_CD") LEFT OUTER JOIN
"SDW_PRD_ALLVM"."SALES_PERSON_V" T7
On T1."SLS_PRSN_ID" = T7."SLS_PRSN_ID"
And T1."SOR_ID" = T7."SOR_ID") LEFT OUTER JOIN
"SDW_PRD_ALLVM"."RELATED_ACCT_V" T10
On T6."RLTD_ACCT_ID" = T10."RLTD_ACCT_ID"
And T6."SOR_ID" = T10."SOR_ID") LEFT OUTER JOIN
"SDW_PRD_ALLVM"."PRICE_PLAN_SHARE_V" T12
On T4."PPLAN_SHARE_CD" = T12."PPLAN_SHARE_CD") LEFT OUTER JOIN
"SDW_PRD_ALLVM"."CHANGE_REASON_V" T9
On T1."SOR_ID" = T9."SOR_ID"
And T1."DEACT_CHANGE_REAS_CD" = T9."CHANGE_REAS_CD") LEFT OUTER JOIN
"SDW_PRD_ALLVM"."REGION_HIST_V" T13
On T5."AREA_CD" = T13."AREA_CD"
And T5."REGION_CD" = T13."REGION_CD"
Where T13."REGION_CD" = T11."REGION_CD"
And T13."AREA_CD" = T11."AREA_CD"
And T1."LINE_ACT_DT" Between {d '2007-08-01'}
And {d '2007-08-31'}
And T10."RLTD_ACCT_ID" In ( sel group_id from
SDW_PRD_QMTBLS.CARSTNSLSOPS_NAT_GID_tN)
And T8."ACTIVITY_CD" In ('ac')

 

[Dom_Ciccone]

Hi todd,

Use a querytable object, setting the connection string property and SQL
statement property. Add the QT to the worksheet's QueryTables collection,
specifying the connection string, destination range and SQL string.

Then use the QT.Refresh method to run the query and drag the data back.
Here's an example:

Sub CreateQT()

Dim sConn As String
Dim sSql As String
Dim oQt As QueryTable

sConn = "ODBC;DSN=MS Access 97 Database;"
sConn = sConn & "DBQ=C:\Program Files\Microsoft Office\"
sConn = sConn & "Office\Samples\Northwind.mdb;"

sSql = "SELECT Customers.CustomerID, Customers.CompanyName "
sSql = sSql & "FROM `C:\Program Files\Microsoft
Office\Office\Samples\Northwind`"
sSql = sSql & ".Customers Customers "
sSql = sSql & "WHERE (Customers.City='Berlin') "

Set oQt = ActiveSheet.QueryTables.Add( _
Connection:=sConn, _
Destination:=Range("a1"), _
Sql:=sSql)

oQt.Refresh

End Sub

 

[todd.huttenstine]

Dom_Ciccone,
It asks me to select Data Source. What do I use?



RB Smissaert,
Its telling me too many line continuations. How do I fix this?