Q U O T E
do exactly as you ask, I open a connection at program startup with a .ConnectionOpen method in my goApp object, and store the connection handle to a property .ConnectionHandle of my goApp object, I just close the connection just before exiting the application, again with a ConnectionClose method in my goApp object, or when connection parameters are changed by the user, in which case I close and reopen the connection (to change connection parameters, I require all other forms to be closed).
* Open:
goApp.ConnectionHandle = SQLConnect(.....
* Close:
If goApp.ConnectionHandle > 0 Then
SQLDisconnect(goApp.ConnectionHandle)
Endif
So I just use one connection shared among all objects, procedures, methods, remote views, etc. that require a connection handle. I do not know if this is "Best Practice", but it always worked for me, and I can tell you from experience that everything remote-data-related starts a lot faster this way.
E N D Q U O T E This is pretty good and to all the suggestions here provided that your SQL Server or network connection is stable.
There is a draw back with this approach.
What if you stored your connection handle during your app startup and just close the handle on exit or on your forms load & destroy procedures but then your network connection link is broken or your SQL server just experience a problem and it goes down? This means that your connection handle is useless and not valid anymore.
For me, I always connect and disconnect whenever inserting, updating, deleting and querying records, which means that I always create,connect & disconnect a connection handle.I prefer this and its a "GOOD & BEST PRACTICE".
What do you think? Am I wrong with my BEST PRACTICE?
Regards,