QueryTables in Excel 2010 with VBA with VBA creating many joins

Question

I follow the code that I found on another site. Here are the basics of my code:

Dim SQL As String Dim connString As String connString = "ODBC;DSN=DB01;UID=;PWD=;Database=MyDatabase" SQL = "Select * from SomeTable" With Worksheets("Received").QueryTables.Add(Connection:=connString, Destination:=Worksheets("Received").Range("A5"), SQL:=SQL) .Refresh End With End Sub 

The problem with this is that every time they click on the button assigned to her, he creates a new connection and never refuses it. I open the table after testing, and there are many connection versions listed in the Connections section. connection Connection1 Connection2

I cannot find a way to close or remove connections. If I add ".delete" after ".Refresh", I get error 1004. This operation cannot be performed because the data is being updated in the background.

Any ideas on how to close or remove the connection?

Answer 1

You may ask yourself why you create a QueryTable each time in your code. There are reasons for this, but usually this is not necessary.

QueryTables are rather development-time objects. That is, you create your QueryTable once (via code or user interface), and you update QueryTable to get updated data.

If you need to change the underlying SQL statement, you have some options. You can configure parameters that request a value or get it from a cell. Another option to modify SQL is to change the code in an existing QueryTable.

 Sheet1.QueryTables(1).CommandText = "Select * FROM ...." Sheet1.QueryTables(1).Refresh 

You can select different columns or even different tables by changing CommandText. If this is a different database, you will need a new connection, but this is quite rare.

I know that does not answer your question directly, but I believe that determining whether you really need to add a QueryTable each time is the first step.

For more information about the parameters, see http://www.dicks-clicks.com/excel/ExternalData6.htm This is for 2003, so there are a few inconsistencies with later versions. The basics are the same; you may need to find out about the ListObject if you are using 2007 or later.

Answer 2

I had the same problem. The previous answer, although a definite step in the right direction is PITA.

However, he allowed me to refine my search, and the winner ...

http://msdn.microsoft.com/en-us/library/bb213491(v=office.12).aspx

i.e. for your existing QueryTable object, simply do the following:

 .MaintainConnection = False 

It works so hard. No more database access lock file after data update.

Answer 3

You must declare the connection as a separate object, after which you can close it after completing the database query.

I don't have a VBA IDE in front of me, so excuse me if there are any inaccuracies, but it should point you in the right direction.

eg.

 Dim SQL As String Dim con As connection Set con = New connection con.ConnectionString = "ODBC;DSN=DB01;UID=;PWD=;Database=MyDatabase" Worksheets("Received").QueryTables.Add(Connection:=con, Destination:=Worksheets("Received").Range("A5"), SQL:=SQL).Refresh con.close set con = nothing 

Answer 4

I found that by default, new connections created in this way are called "Connection". I use this piece of code to remove a connection, but keep a listobject.

 Application.DisplayAlerts = False ActiveWorkbook.Connections("Connection").Delete Application.DisplayAlerts = True 

It can be easily changed to remove the last added connection (or if you track connections by their index).

 Application.DisplayAlerts = False ActiveWorkbook.Connections(ActiveWorkbook.Connections.Count).Delete Application.DisplayAlerts = True 

Answer 5

Instead of adding another query table using the add method, you can simply update the CommandText property of this connection. However, you should be aware that there is an error updating the CommandText property for an ODBC connection. If you temporarily switch to an OLEDB connection, update the CommandText property, and then return to ODBC, it will not create a new connection. Don't ask me why ... it just works for me.

Create a new module and paste the following code:

 Option Explicit Sub UpdateWorkbookConnection(WorkbookConnectionObject As WorkbookConnection, Optional ByVal CommandText As String = "", Optional ByVal ConnectionString As String = "") With WorkbookConnectionObject If .Type = xlConnectionTypeODBC Then If CommandText = "" Then CommandText = .ODBCConnection.CommandText If ConnectionString = "" Then ConnectionString = .ODBCConnection.Connection .ODBCConnection.Connection = Replace(.ODBCConnection.Connection, "ODBC;", "OLEDB;", 1, 1, vbTextCompare) ElseIf .Type = xlConnectionTypeOLEDB Then If CommandText = "" Then CommandText = .OLEDBConnection.CommandText If ConnectionString = "" Then ConnectionString = .OLEDBConnection.Connection Else MsgBox "Invalid connection object sent to UpdateWorkbookConnection function!", vbCritical, "Update Error" Exit Sub End If If StrComp(.OLEDBConnection.CommandText, CommandText, vbTextCompare) <> 0 Then .OLEDBConnection.CommandText = CommandText End If If StrComp(.OLEDBConnection.Connection, ConnectionString, vbTextCompare) <> 0 Then .OLEDBConnection.Connection = ConnectionString End If .Refresh End With End Sub 

This UpdateWorkbookConnection routine UpdateWorkbookConnection works when updating OLEDB or ODBC connections. A join does not have to be associated with a pivot table. It also fixes another problem and allows you to update the connection, even if there are several pivot tables based on the same connection.

To initiate an update, simply call the function with the connection object and the command text parameters as follows:

 UpdateWorkbookConnection ActiveWorkbook.Connections("Connection"), "exec sp_MyAwesomeProcedure" 

You can also update the connection string.

Answer 6

If you want to delete, if immediately after the update you have to update not in the background (using the first parameter → Update False) in order to have the correct sequence of actions

Answer 7

Try setting the QueryTable.MaintainConnection property to False ...

"Set MaintainConnection to True if the connection to the specified data source should be maintained after the update and until the workbook is closed. The default value is True! And it seems that the checkbox is not selected for this flag (Read / write Boolean ) "

Answer 8

Still relevant years later ... fighting the same problem, and this is the most useful flow. My situation is the option above, and I will add my solution when I find it.

I use the Access database for my data source and set the query table on a new sheet. Then I add two more new sheets and try to set up a query table using the same connection for each of them, but in a different Access table. The first query table works fine, and I'm using .QueryTables (1). Remove and set the querytable object to Nothing to disable it.

However, the following sheet cannot create a new query table using the same connection that was not closed. I suspect (and add a solution below) that I need to remove the connection before deleting the query table. The Rasmus code above looks like a likely solution.