VBA Outlook app - keeping a sql connection open indefinetely

I'm doing a bit of work with an Outlook VBA application that a colleauge created. It's fairly basic, just connects to a sql server and runs some commands, depending on what the user is changing etc. The problem we're having is in trying to speed it up. It's not slow, but at the moment every time the user clicks to add some time (it's mostly a time-keeping thing), it opens a connection to the server, processes the stuff the user sends, and then closes the connection when it's done. When the user is simply updating a tiny bit of data, say a single field update in the database, it seems (to my boss) to be terribly wasteful to open a new connection, do some stuff, and then just close it. Therefore, I - with my very little experience of programming - have been tasked with trying to answer the question; would it be possible/quicker/safe to simply have the form open a connection when it's first opened for the day, and then keep it open until outlook is closed at the end of the day?

So, can you do this? There's only a handful of staff in the office, 50 at most, so the server won't fall over trying to keep 50 connections open (I assume!). Will it be alot quicker than opening a connection and closing it again? Is it secure to keep a connection open for such a long time? Am I asking too many questions?

Thanks for any replies,


  • Hi Barns, I hate to sound negative, but in a nut-shell, no. First off it is something of a security issue but more importantly sql connections automatically timeout after a few minutes and though I believe this can be worked-around, it's tricky and just generally not recommended.

    The general consensus on sql connections is open it, send it, close it. Which is fine if you're using vb6 or vb.net where you can just use multi-threading to ensure the program still runs normally while sending the data, however vba does not support threading (internally anyway, it can call up COM objects which will support threading but that means programming COM objects, which ain't easy).

    So with VBA I personally think you are left with few choices, continue as you are now and perhaps add a timer event ([red]Application.OnTime[/red] in VBA) to allow updates to be sent every 2/5/10 etc. minutes (though this ofcourse means the sql db will not be real-time).

    Or you could pass the info along to something else and allow that to update the database. This something else could either be another application on the computer (if you know vba you could use any of the other Office programs like Excel or Access), i.e. send it into a blank Access db, which would then check for new records every say 30 seconds, if found it would open the sql connection, send updates and then delete from Access, ofcourse this would probably mean Access would be slower so if Access/Excel etc. are commonly used, this option may not be viable. Alternatively, you could send the data to another (mostly unused) computer in the office and have that update the sql db as above.

    I'm sorry I couldn't suggest an easier way, however if it's any help, my personal recommendation would be to create a very simple vb.net application (which you should pick up easily if you know some vba) which would run invisibly on each pc and receive the data from outlook and send it to the sql db, as .net supports threading there shouldn't be any performance issues and it is secure.

    It's completely free to download and indefinitely use Visual Basic Express from: http://www.microsoft.com/express/product/default.aspx

    If you were to go with this final option I would be more than happy to give you all the help you need to code a program for this situation. Hope this helps, Dai.

    P.S. No you weren't asking too many questions, it is a forum after all, though I fear I may have given too many answers!

    Do or do not, there is no try. |
  • Thanks for the reply.

    It's a bit odd as we actually tried it on Friday quickly, and the connection didn't seem to timeout that often - assuming the debug feedback was working, it didn't seem to timeout that often (we have a bit of code for reconnecting that checks the current state of the connection, and reconnects if it registers as unconnected. The only problem with this is that if outlook crashes or the network does, the sql connection disconnects but doesn't set its state to be disconnected, so we needed to add in a dummy query to check if it's working... not a perfect solution.) Then again, the code only runs every time you try to add/retrieve time, so it's theoretically possible I suppose that we simply didnt try for long enough. We're trying a longer test today.

    May I ask about the security issue surrounding it?

    The multi-threading idea is a good one, but has the problem that we also make a connection to retrieve data and populate the form that allows the user to add time; therefore, as far as I can judge, sending the data elsewhere and multi-threading it wouldnt make a difference, as the user wants the form up when they click on the button - whether the delay happens in the VBA app or elsewhere, it's still a delay! It'd be a great idea if we were simply adding time, and I'll keep it in mind, ta.

    Thank you very much for the help. We're going to be trying out the new code on a single machine today - we'll see if our rather hacked together solution holds!

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!