Using parameters in a SQL query with a subquery

Question

I have a rather complicated SQL query with a nested subquery. When I try to use parameters in Microsoft Query, I can use parameters in queries that cannot be represented graphically. So I need one more option. I think you can put your SQL query in a cell as a string, then run Macro. Any ideas how I could do this?

thank

-Jesse

Answer 1

Here's what I do to get around the limitations of Microsoft Query in Excel 2007:

• A create a dummy query ( SELECT NULL AS Testfor example) in Microsoft Query and paste it into the worksheet.
• , MS Query, "- > ".
• " ", "".
• " " , , "?" , "".
• "", " ".
• "- > ", .

- GUI, MS Query, , .

, . , MS Query , ( ), VIEW SQL .

Answer 2

,? , .

, ,

Dim Con As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim server, Database As String
Dim Data as Worksheet

Set data = ThisWorkBook.Worksheets("data")

'rename field here and elsewhere to your variable eg SD or StartDate
Dim field as string

server = "servername"
Database = "database"

'set connection string

If Con.State <> 1 Then
 Con.ConnectionString = "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & Database & ";Integrated Security=SSPI;"


'this is just setting the connection time out to infinite
setcono:
Con.ConnectionTimeout = 0
Con.CommandTimeout = 0


'this is making sure it set the connection time out to infinite
If Con.ConnectionTimeout > 0 Then GoTo setcono
If Con.CommandTimeout > 0 Then GoTo setcono

Con.Open

Set oRS = New ADODB.Recordset
oRS.ActiveConnection = Con

field = Range("A2").value

oRS.Source = "YOUR SQL QUERY "
oRS.Source = oRS.Source & " WHERE field = '"  & field & "'"

oRS.Open
data.Range("A2").CopyFromRecordset oRS
End If
oRS.Close
Con.Close
If Not oRS Is Nothing Then Set oRS = Nothing
If Not Con Is Nothing Then Set oCon = Nothing

, Microsoft , , ,

Answer 3

CREATE PROCEDURE [dbo].[yourprocedure] @DATEFROM DATETIME, @DATETO DATETIME
AS


SELECT Query 
where date >= @datefrom
and date <= @dateto 

, "". " ":

EXEC yourprocedure @DATEFROM = ?, @DATETO = ?

and direct? to the cells you want