Verify Data Connection

[chuckchuckit]

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:

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:

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:

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]

Xenou - Yes those are some good thoughts, I’ll be pondering them. I also was thinking of letting all the connections collect and then delete them all later like you say. Since I have many programs running automatically without user interface, I would have to be careful not to delete the connections collection during one of the auto queries. Also sometimes a query can pause quite long due to the query web site may be busy etc, so trying to time a safe deletion of connections might not be a guarantee.

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I am still thinking there has to be a safe way to automatically delete the connection after it finishes its query as part of one of the QueryTable .properties. There is one called “MaintainConnection” that looks like can be Set/Get, but I can’t figure out how to get that property to work (writing code with "MaintainConnection" so can experiment with it).

<o></o>
There is an “AfterRefresh” which can literally launch some code after a refresh(query?) is done. And perhaps that is where the delete should be done, but once again my coding efforts there so far have not found the code to run an experiment to try this. It seems to be set somewhere in one of the VBA pull down windows or something like that.

I suspect a slight possibility that the company whose software is causing the conflict may even be able to modify on their end (slight possibility there). As I am pretty sure the conflict (hanging of their software and my VBA code in Excel) is due to the DDE interface they use that our programs share back and forth. Their DDE works great but they changed something recently in their new version, and now when I tell my Excel program to wait until the query is done before any more lines of code run, it unfortunately causes their software to wait also so they both lockup/crash. Ran great for months with no problems, but not now – O well.

<o></o>
Yes, it is a trading platform. All automated that runs in the background so I don’t need to housekeep it at all during the day. Since I am not a coding expert, took a lot of trial and error to get things to work right. Looks for certain situations and when finds them displays information and quick research etc about the companies. Basically narrows down by a huge amount what I am looking for and then need to of course do research on.

<o></o>
It does not however take the emotions out of any trading… As one then has to still decide what is best no matter what any computer or software might be trying to help with. But it is amazing what computers can do. Especially today with the fast data rates and fast computers. I enjoy coding (the creative part of it), but am sure my code is sometimes not all that efficient or best use of ways to do things. But I figure in what I am doing it just matters that it works, and I leave enough comments (sometimes a lot) so later I can figure out easily what the code is doing if I need to change something. And works hopefully without having to constantly debug it. So I concentrate on the results by really trying to get all the potential bugs out before moving on to the next task. Used to code in C language 15 years ago, quit coding due to Carpal tunnel and now coding again past 6 months or so when needed.

<o></o>
It seems you are educated in coding. Is it your profession?

 

[ellyka112]

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^st 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

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?

 

[xenou]

My profession is in finance (generally accounting with a bit of financial analysis mixed in). I use VBA extensively both in Excel and Access, most of it for aid in my own work, and a little for creating solutions that my coworkers will be using (always much more work when other users gets involved!). I'm not hired for my programming skills but I think they do help me get hired (if that makes sense). There is so much software, DB and data crunching needed for accounting/finance - its a real plus to have these skills in my field. My job now involves plenty of querying on our production database for data analysis - job costs, labor hours, sales and margins, and so on. I've had a bit of coursework in programming - it's probably more interesting to me than finance per se. But there's plenty of opportunity to apply my skills. I can sympathize with the carpal tunnel problem. A few years ago I had pain in my hands for nearly three years - don't know why but it finally cleared up this last two years.

ξ

Edit - yeah, never used querytables extensively due to my preference for ADO - but it sounds like you have a much more complex solution than the average post here anyway.

 

[chuckchuckit]

Your work sounds interesting. Must be nice to blend ones’ own type of financial analysis with coding skills and be hired to do so. Somewhat of a niche I would think.

I briefly thought about trying to sell or let others use my software. But would perhaps then involve a lot of coding change requests and interface problems upkeep. Also investing IMO should be a personal thing and I would not wish to be a part of anyone having trouble there as the software is tailored to how I invest. Might not work for others. Lots of variables in those types of things.

Looks like my software is now working again today. I am hoping this is not an intermittent problem, but if it stops working again, I think I will ask a question on this forum about how to use the “MaintainConnection” part of the QueryTable call as I can’t seem to get it to work. But as of now no need yet. The company who’s software new version is conflicting with my Excel code is saying the problem is external to their software, so likely not much help there if it continues. So likely will have to change my code somehow if it persists.

Our mention of Carpel Tunnel got me to start writing up a nutritional fix/improvement I discovered somewhat by coincidence, about all those types of injuries, which bad nutrition actually makes worse and may be a big factor in the cause. When I get done with my writing it up, I’ll post it here as I’ll bet a bunch of programmers here struggle with it on and off. Solution can be fairly simple and no drugs etc. Helped both my wife from a car accident injury that was getting worse over the years, and myself with severe Carpel Tunnel.

BTW my wife’s co-workers daughter 1<SUP>st</SUP> year in College is at Cleveland State Univ. She is into ballet and starting medical studies there.

Chuck

 

[chuckchuckit]

xenou - Am not having the query hang problems any more. I kept emailing with the software company who's software was causing my system to hang with their new version whenever my excel code was doing those queries. They at first said that my excel code was external to their software and so they could not do anything. But a few days later they seemed to have modified their software to occomodate my excel queries.

During queries now their software does hesitate and my queries slow down for a second or so on each one where they did not before, but this slight delay is no big deal. Works well now.

Also did a write up on a food cure for what my wife and I found to work for us with Carpal Tunnel Syndrome (CTS), and spinal vertabre and nerve damage from her car accident. Posted it to the Lounge section of this forum in case you may find it of interest.

http://www.mrexcel.com/forum/showthread.php?t=518282

Thanks again.

Chuck

 

[fasberg]

Again the solution is very simple:

http://vbaproexcel.blogspot.com/2011/07/query-after-refresh-method.html

you try to use this method inproperly