Hi Sharma,
How do you define failure for the first query? 0 records returned? an
error from the database server/engine?
What do you want to do with the result of the query? Put it on a (new?)
sheet in your workbook?
*writing* the queries using VBA is very simple. The following assembles the
first query statement in a string variable:
Sub WriteSQLquery ()
Dim SQL As String
SQL = _
"SELECT " & _
" distinct AMRCostCtr " & _
"FROM " & _
" [SRT1].[RequestToolDev].[dbo].[vReqsocs] a, " & _
" [SRT1].[RequestToolDev].[dbo].[ReqMain] b " & _
"WHERE " & _
" a.ReqID = b.ReqID and MainRequestNumber = '365700';"
End Sub
Here's a robust-looking, general-purpose procedure called SQLLoad to open a
recordset for your query and copy the data to a worksheet: Beyond Excel -
Say Goodbye to QueryTables
<http://itknowledgeexchange.techtarget.com/beyond-excel/say-goodbye-to-query
tables/> . The associated blog actually contains a lot of good information
and code for working with databases from Excel (some of it advanced).
You will need a connection string to identify your data source and (if
needed) credentials. Connectionstrings.com
<http://www.connectionstrings.com/> has a large index of connection string
examples. You should avoid Microsoft Jet connection strings and in cases
where there are alternatives, ODBC connection strings, which have been
replaced by newer drivers. (ODBC is still fine and commonly used with many
non-Microsoft data stores).
See if the above info gets you started. I can provide more help if needed.
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of amrahs k
Sent: Friday, March 09, 2012 7:59 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ SQL - Query
Hi Folks,
Please guide me to write the below sql queries using vba macros.
SELECT
distinct AMRCostCtr
FROM
[SRT1].[RequestToolDev].[dbo].[vReqsocs]
a,[SRT1].[RequestToolDev].[dbo].[ReqMain] b
where
a.ReqID = b.ReqID and MainRequestNumber = '365700'
If above query fails need to run the next query as given below.
SELECT
StationBranch
FROM
[SRT1].RequestToolDev.dbo.ReqAMROOS
WHERE
ReqID in (select reqid from [SRT1].RequestToolDev.dbo.ReqMain
where
mainrequestnumber = '365700')
Thanks,
Sharma
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
