Question about adodb programming & mssql server 2008

Posted on 2009-05-01
Last Modified: 2013-12-25
I've written a few programs that did minimal access to a sql server.

I just finished converting a VB6 app that we use in house to use sql server as the datasource.  Previously we used random access files shared over a network.  

The connection to the datasource is used constantly throughout the program to get new support issues, to refresh other employees' status on the screen, etc.   And each employee is in a different location, so data access speed is critical to the app.

Should I open the connection one time when I first load and close it when I exit?  Or should I open/close the connection every time I access the datasource.

IF I should leave the connection open, what about recordsets?  What circumstances would dictate whether recordsets are left open or open/closed?

Thanks for your help!
Question by:lthames
    LVL 6

    Accepted Solution

    We never open the datasource until the moment a transaction needs to be processed; period.

    Author Closing Comment

    Thanks!  I followed your advice and so far everything seems very fast to access.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now