Excel 2010 Query Tables

[DavidStein]

I'm trying to query a SQL 2000 Database. This is the basics of what I have which has gone through several iterations.

Sub GetReceivingLastYear(EndDate As Date)
Dim QT As Excel.QueryTable
Dim CN As ADODB.Connection
Dim Period As Integer
Dim connString As String
Dim SQL As String
Dim Clipboard As New DataObject
Dim Tmp As QueryTable

Worksheets("LYReceived").Range("A5:K8").ClearContents

Set Tmp = Worksheets("LYReceived").QueryTables(1)

If Tmp Is Nothing Then
MsgBox ("Need to Create QT")

Else
MsgBox ("already exists")
Set Y = Nothing
End If


Set CN = New ADODB.Connection
Set Rs = New ADODB.Recordset
connString = "connection string obscurred"

CN.Open connString

SQL = "Select * from some table"


Rs.Open SQL, CN, adOpenForwardOnly

Set QT = Worksheets("LYReceived").QueryTables.Add(Rs, Destination:=Worksheets("LYReceived").Range("A5"))
QT.Name = "qtLYR"
QT.Refresh

Rs.Close
Set Rs = Nothing

CN.Close
Set CN = Nothing

End Sub

Unfortunately, whenever I run this, and the dozens of variations of it, I continue to get Connection, connection1, connection2.... pilling up in my connections for my spreadsheet.

I've done everything I can to remove them in code, but nothing seems to work.

Can anyone assist me with this?

 

[DavidStein]

This seems to work:

Sub GetReceivingLastYearODBC(EndDate As Date)
Dim sqlString As String
Dim connString As String
Dim CN As ADODB.Connection
Dim Rs As ADODB.Recordset
Set CN = New ADODB.Connection
Set Rs = New ADODB.Recordset
Worksheets("Test").Range("A5:K8").ClearContents
sqlString = _
"Select * from SomeTable"
connString = "DRIVER={SQL SERVER};DSN='';SERVER=DatabaseName;UID=;PWD=;DATABASE=M2MDATA01;Connection Timeout=120"
CN.Open connString
With Rs
Rs.ActiveConnection = CN
Rs.Open sqlString
Worksheets("Test").Range("A5").CopyFromRecordset Rs
End With
Rs.Close
Set Rs = Nothing
CN.Close
Set CN = Nothing
End Sub

 

[RoryA]

Do you want a linked querytable at the end, or just the data?