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?

 

[BuckWoody]

Use an ODBC DSN instead, where you have more control over the connection info. Then code this way:


' Declare the QueryTable object</PRE>
Dim qt As QueryTable</PRE>
' Set up the SQL Statement</PRE>
sqlstring = "select au_fname, au_lname from authors"</PRE>
</PRE>
' Set up the connection string, reference an ODBC connection</PRE>
' There are several ways to do this</PRE>
' Leave the name and password blank for NT authentication</PRE>
connstring = _</PRE>
"ODBC;DSN=pubs;UID=;PWD=;Database=pubs"</PRE>
</PRE>
' Now implement the connection, run the query, and add</PRE>
' the results to the spreadsheet starting at row A1</PRE>
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)</PRE>
.Refresh</PRE>
End With</PRE>

 

[RoryA]

What messages are you getting from the code? You don't appear to be deleting the existing qt, just adding new ones.

 

[DavidStein]

If I delete the QT nothing happens to the connection. It remains in the Workbook Connections screen.

Is there any way that I can delete all connections that begin with "Connection" via VBA to clean them up after my code as run?

 

[RoryA]

How are you deleting the QT?

 

[DavidStein]

Use an ODBC DSN instead, where you have more control over the connection info. Then code this way:

I've tried that, and just tried it again. It doesn't remove the connections. They just build up.

Incidentally, it doesn't matter if I close and re-open the file, the old connections remain.

It has to do with how I'm adding a QT and a connection with it, but not being able to delete the connection afterward.

 

[DavidStein]

How are you deleting the QT?

Right before the End Sub I placed:

QT.Delete

Am I doing it wrong?

 

[ChrisM]

I'm confused about your whole approach. I view query tables and ADO recordsets as two exclusive items.

I could:

1) Create a query table in Excel (not VBA) using the built in tools and have it refresh whenever I want
or
2) I could create an ADO recordset, then loop through it and write the output to a range of cells.

You seem to be doing both at once and I'm not sure why.

I think you would be better served to create a query table using the built-in Excel tools, then when you need to change the SQL, use VBA to change the connection string property of the query table and then refresh it.

 

[DavidStein]

I'm confused about your whole approach. I view query tables and ADO recordsets as two exclusive items.

I could:

1) Create a query table in Excel (not VBA) using the built in tools and have it refresh whenever I want
or
2) I could create an ADO recordset, then loop through it and write the output to a range of cells.

You seem to be doing both at once and I'm not sure why.

I think you would be better served to create a query table using the built-in Excel tools, then when you need to change the SQL, use VBA to change the connection string property of the query table and then refresh it.

Thanks ChrisM. Could you perhaps point me toward a link with a good example of this?

 

[ChrisM]

well......!!!! Now I'm feeling silly. My answer was based on my experience from Excel 2000/2003. I opened up a new file in 2007 and was going to type up an answer for you, then quickly found out that the way 2007+ handles data connections is really confusing to me! On top of that, I looked in one of my work files, and I have like 50 empty data connections in there, so I actually have the same end-problem as you!

So, sorry, not ready to give you an answer yet, need to figure this out myself first!

ps: I still maintain you don't need both a recordset and a query table though, but can't prove it in 2007 at the moment.