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,