Verify Data Connection

chuckchuckit · Nov 19, 2010

I am trying to determine if a "Data Connection" has been deleted by looking to see if the connection name still exists. I know the connection name, but do not know the code line that would tell me if it exists.

Here is the code I am using so far:

Code:

Dim TheConnectionName As String
For Each objWBConnect In ThisWorkbook.Connections
   TheConnectionName = objWBConnect.Name
Next objWBConnect

Above code loops and gets names of all data connections that exist. Later I will delete one of those connections by its name using code:

Code:

ActiveWorkbook.Connections(TheConnectionName).Delete

If it errors and does not delete it (such as due to the connection still being used in a query) I am choosing to use code:

Code:

On Error Resume next

And then test to see if that connection no longer exists (which means it did get deleted). If it still exists I'll continue to try to delete it until I see that it no longer exists.

But I can not find this needed next line of code that would tell me if that connection is still there or not.

Does anyone know what line of code would tell me such?

Thanks.

Chuck

chuckchuckit · Nov 19, 2010

I know I could loop through a second time following code

Code:

For Each objWBConnect In ThisWorkbook.Connections
   TheConnectionName = objWBConnect.Name
Next objWBConnect

and if none of the connections match, then it must be deleted. But it would be more efficient to just see if the connection still exists.

But don't know the code to do so?

Thanks.

Chuck

xenou · Nov 19, 2010

Maybe:

Code:

On Error Resume Next
ActiveWorkbook.Connections(TheConnectionName).Delete
If Err Then
    '//Connection was not deleted
    On Error GoTo 0
Else
    '//Connection was deleted
    On Error GoTo 0
End If

That's untested. Perhaps there's an error if you try to delete a connection that doesn't exist! In which case, check if it exists after deleting it - this is not a great hardship to do even with your loop. The only solution for avoiding this would be to investigate why/how a connection would be in use when you are running your code, and closing those connections first.

ξ

xenou · Nov 20, 2010

This would be an example of a function that would serve your purpose (maybe):

Code:

Function ConnExists(ByRef arg As String) As Boolean
Dim c As Object

    On Error Resume Next
    Set c = ThisWorkbook.Connections(arg)
    If Err Then
        ConnExists = False
    Else
        ConnExists = True
    End If

End Function

Implemented easily enough:

Code:

Sub Foo()
Dim x as Boolean
    x = ConnExists("MyConnection")
End Sub

Nothing very sophisticated, I'm afraid. You could do the same in line without using a function, if you like.

An interesting footnote: there is a connections object but not a connection object, in intellisense (XL2007). I was surprised by that.

chuckchuckit · Nov 20, 2010

Thanks for the code back. Yes I was concerned to be trying to delete a connection that does not exist and get caught in an endless loop trying to delete it. Your code checking for a connection seems the much better way and I will try that sometime this weekend when I get a chance. Here is the reason for my needing such code:

Actual my code used to work well when I would query and then set following parameter in the query to False:

Code:

.Refresh BackgroundQuery:=False

As this would let a query finish before any more code would run. Multiple queries one after another and then after each query data is retrieved (which may take a second or two while it accesses the Web to do so) the next line of code would delete that no longer needed connection that is still there (so we do not end up with potentially 100’s of connections no longer used).
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
But another program I associate my Excel programs with is sharing DDE information back and forth with My Excel programs, and their latest version of software now hangs when the parameter above is set to False. Their new version now does not like my code that says my Excel code should wait, as theirs seems to conflict with this type of “wait” parameter. Both their program and my Excel program locks up too and all programs need to be rebooted.
<o

></o

>
So now when set above parameter to True, there is no more lockup problems, but of course the code is not waiting for the query to finish anymore before it tries to delete the connection. If the query is not finished yet and code tries to delete it, it gives a run time error trying to delete it while still in the midst of the query.
<o

></o

>
So that is why I am thinking I will now just continue to try to delete it until the query finishes and it should delete OK then. Of course it may try to delete it 100 or more times before query finishes, so I am wondering if so many attempts might cause some other problems perhaps. Not sure of any other way to do it other than a 1 second delay between deletion attempts, but that would slow down my program way too much in certain areas.
<o

></o

>
I will try your “x = ConnExists (“MyConnection”) code. And let you know the results.
<o

></o

>
And I’ll use the “On Error Goto 0” to turn off the error checking.
<o

></o

>
Thanks much.
<o

></o

>
Chuck

chuckchuckit · Nov 20, 2010

Xenou - Your code that looks to see if the connection exists works very well when stepping through it using F8.

But when I run it real time with the query connection still downloading data (query usually is for a few seconds max) and trying to delete its connection at the same time, is causing the code to hang and not be able to delete the connection. Or maybe it is causing the query to hang. I even tried running a timer delay that lets code still run while it waits and that does not help either. Unfortunately the “Err” 1<SUP>st</SUP> example had similar problems.

In any case it appears there will be problems trying to delete a connection while it is being still used.

It looks like the only solution will be to look for some sort of way to “determine that the query has finished.” Before I attempt to delete its connection.

OR

There seems to be be quite a number of QueryTable Properties and perhaps there is one there that will automatically delete its connection when it is done, or some sort of return that says when it is done?

Thanks. - Chuck

chuckchuckit · Nov 20, 2010

Looks like there is an "AfterRefresh" event that triggers True after a query is completed or cancelled?

What code would produce a Boolean for this?

chuckchuckit · Nov 20, 2010

Looks like the .refreshing property might be the way. Something along the lines of:

Code:

With Worksheets(1).QueryTables(1)
    If .Refreshing Then
        'Do not delete yet as still an active query in progress
    Else
        ActiveWorkbook.Connections(TheConnectionName).Delete 'removes connection
    End If
End With

xenou · Nov 20, 2010

I confess I don't use query tables often - I'm an ADO kind of guy. I'll have to ponder. How is it that you are using so many queries so heavily that you are creating, running, and deleting them all at the same time! Is this a trading platform you are building?

xenou · Nov 20, 2010

So that is why I am thinking I will now just continue to try to delete it until the query finishes and it should delete OK then. Of course it may try to delete it 100 or more times before query finishes, so I am wondering if so many attempts might cause some other problems perhaps. Not sure of any other way to do it other than a 1 second delay between deletion attempts, but that would slow down my program way too much in certain areas.

I will try your “x = ConnExists (“MyConnection”) code. And let you know the results.

And I’ll use the “On Error Goto 0” to turn off the error checking.

Sounds good to me ... the code to check if the connection exists will only take an instant in computer time. You can set an OnTime procedure to run until the connection is deleted - these aren't hard to do and I think there's good examples around. This would be preferred to a loop, for instance - Excel would be free while waiting for the next OnTime trigger.

Maybe you can pass the names of connections to be deleted to some kind of container (array/collection/etc.). Set an OnTime procedure to run every 1 minute or 5 minutes or what have you - delete all connections that are in the container, if possible ... sort of a homemade garbage collector.

Verify Data Connection

chuckchuckit

Well-known Member

Excel Facts

chuckchuckit

Well-known Member

xenou

MrExcel MVP

xenou

MrExcel MVP

chuckchuckit

Well-known Member

chuckchuckit

Well-known Member

chuckchuckit

Well-known Member

chuckchuckit

Well-known Member

xenou

MrExcel MVP

xenou

MrExcel MVP

Similar threads

Forum statistics

Share this page

Verify Data Connection

Well-known Member

Excel Facts

Well-known Member

MrExcel MVP

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock