Use VBA to return ODBC query to Excel

Hello,

I apologize if this question has been answered in other posts, but I looked through several other similar posts and either do not know how to implement the solutions to my case, or they didn't work for me. I am trying to use VBA code to run an ODBC query from our ERP system and return the results to Excel. I am not confident that I can effectively make the connection to ODBC from Excel either at this point, so I probably need my hand held more than most. Here is exactly what I am trying to automate:

I open Excel and run a query called "Customer Sales History.dqy". This is an ODBC query that pulls data from a table called "sales_history_report_view". There are 3 user inputs required for the query - "customer_id", and then a beginning and ending "invoice_date". The data source is called P21_US and the server it connects to is called GT-SQL02. GT-SQL02 uses the SQL Server driver (Version 6.01.7601.17514). I just want to return this to Excel within VBA so that I can continue modifying the results within the VBA coding.

Here's what I have been using to connect to the source. I think it works as it doesn't error out, but I haven't successfully returned any data yet so I can't be sure.

Sub Connect_DSN()
Dim adoConn As ADODB.Connection
Dim sConnect As String

sConnect = "P21_US"

Set adoConn = New ADODB.Connection

With adoConn
.Open sConnect
.DefaultDatabase = "P21_US"
Debug.Print .ConnectionString
Debug.Print .DefaultDatabase
' RetreiveSales <-- Procedure I am trying to run later to return the data
.Close
End With

Set adoConn = Nothing

End Sub

Any help on this is greatly appreciated! I am still a noob when it comes to VBA syntax which is why I am so high-maintenance here.

Thanks,
Steve

Thanks Tony. So here's what I came up with:

Sub Connect_DSN()
Dim adoConn As ADODB.Connection
Dim sConnect As String

sConnect = "Driver={SQL Server};Server=GT-SQL02;Database=P21_US;Trusted_Connection=Yes;"

Set adoConn = New ADODB.Connection

With adoConn
.Open sConnect
.DefaultDatabase = "P21_US"
Debug.Print .ConnectionString
Debug.Print .DefaultDatabase
With ActiveSheet.QueryTables.Add(Connection:=sConnect, Destination:=Range("A1"))
.ListObject.DisplayName = "Table_Customer_Sales_History"
.Refresh BackgroundQuery:=False
End With
.Close
End With

Set adoConn = Nothing

End Sub

I am still getting an application or object-defined runtime error. I did add the Microsoft ActiveX Data Objects 2.8 Library already, so that's why the connection is even being made. How precisely do I execute this to retrieve the query I'm after?

Thanks!