Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Convert SQl to VBA so that it executes in VBA
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default Convert SQl to VBA so that it executes in VBA
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

wrote in message
ups.com...
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')


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default Convert SQl to VBA so that it executes in VBA
Make longer lines or break it up in a number of string variables and
concatenate them.

RBS

wrote in message
oups.com...
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?


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 17
Default Convert SQl to VBA so that it executes in VBA
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')

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 52
Default Convert SQl to VBA so that it executes in VBA
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




--
Kevin Ciccone


" wrote:

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')


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 17
Default Convert SQl to VBA so that it executes in VBA
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?

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Hide actions while macro executes Ben Rum Excel Programming 2 December 1st 06 09:19 PM
For Loop only executes with F8 Sisilla[_2_] Excel Programming 2 October 10th 06 08:57 PM
SAveAs executes combobox code Alan Lyall Excel Programming 3 June 9th 04 12:52 AM
excel executes only a part of a macro mgr[_2_] Excel Programming 2 January 15th 04 07:18 AM
Count # of Times Sub Executes Mike[_32_] Excel Programming 1 July 15th 03 03:27 PM


All times are GMT +1. The time now is 07:32 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement