Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Connection held open

Posted on 2002-03-26
5
Medium Priority
?
176 Views
Last Modified: 2010-05-02
I have the following code.  dcCompany is a dataCombo.  After the line mcnCust_Name.Open strConn a connection is made to an access database and a .ldb file is created.  If I run
    Set mcnCust_Name = Nothing
The .ldb file is removed and the connection closed.  How do I do the same for the dataCombo.  I have tried

                If Not dcCompany.DataSource Is Nothing Then Set dcCompany.DataSource = Nothing
                If Not dcCompany.RowSource Is Nothing Then Set dcCompany.RowSource = Nothing
                dcCompany.BoundColumn = ""
                dcCompany.ListField = ""

but the .ldb file is still there and thus I still assume the connection.  If I run this code more than approx 15 times it will crash and I can only assume that this has something to do with the connections being left open.  I will never use dataCombo's again.

Thanks

Dim strConn As String
Dim strConString As String

    Set mcnCust_Name = New ADODB.Connection
    Set mrsCust_Name = New ADODB.Recordset
    strConString = "Data Source=" & CompanyDBFilePath

    'Define the connection string and open the connection
    'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    '             "Data Source=C:\databaseTL\Company.mdb;"
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & strConString
    mcnCust_Name.CursorLocation = adUseClient
    mcnCust_Name.Open strConn

    'Open the Recordset
    mrsCust_Name.Open "SELECT c_Name,Comp_Key FROM Company ORDER BY c_name", mcnCust_Name, adOpenStatic, adLockOptimistic, adCmdText

    Set dcCompany.DataSource = mrsCust_Name

    Set dcCompany.RowSource = mrsCust_Name
    dcCompany.ListField = "c_name"
    dcCompany.Refresh
    dcCompany.BoundColumn = "Comp_Key"
   
    Set mcnCust_Name = Nothing
    Set mrsCust_Name = Nothing
0
Comment
Question by:iJolly4
  • 3
5 Comments
 
LVL 1

Accepted Solution

by:
baltman earned 400 total points
ID: 6897638
I don't think it is a problem with the dataCombo, but more a not-closing of the Access database. In the code above, you don't close the connection, you simply destroy it (= Nothing). That could create problems... (a database is opened 15 times, not closed, but abruptly abandoned)

According to your code:


mrsCust_Name.Open "SELECT c_Name,Comp_Key FROM Company ORDER BY c_name", mcnCust_Name, adOpenStatic,
adLockOptimistic, adCmdText

Set dcCompany.DataSource = mrsCust_Name

   Set dcCompany.RowSource = mrsCust_Name
   dcCompany.ListField = "c_name"
   dcCompany.Refresh
   dcCompany.BoundColumn = "Comp_Key"


Now before destroying the objects, close it first:

   mcnCust_Name.Close
   mrsCust_Name.Close


Then continue with your code:


   Set mcnCust_Name = Nothing
   Set mrsCust_Name = Nothing



And don't forget to destroy the dataCombo (like you said in the top of your question):

If Not dcCompany.DataSource Is Nothing Then Set dcCompany.DataSource = Nothing
If Not dcCompany.RowSource Is Nothing Then Set dcCompany.RowSource = Nothing
dcCompany.BoundColumn = ""
dcCompany.ListField = ""

I did a little test here on my system (Win 2000) and ran about 150 times, no hangs/crashes....

Hope it helps,

BA
0
 
LVL 1

Expert Comment

by:CodeWizrd
ID: 6898330

I personally never use any data-bound controls.  I usually open a connection to my database when I first start my application (in a global variable), and then get my data and fill controls "the hard way".

It takes a little more work, but I find that I have much more control over my code.  Maybe I'm just too much of a control freak.
0
 
LVL 1

Expert Comment

by:baltman
ID: 6898346
Agreed with you there CW. Personally I use DSN Less connections and connect with a database, get the recordset, close the connection and populate whatever I want by assigning the recordset values to variables....

Also much better in controlling data and the controls don't get bound to any datasource, which means you always have only one data connection to worry about at the same time. Also, I believe, much saver and better in performance.

BA
0
 

Author Comment

by:iJolly4
ID: 6898937
Thanks,

It works a treat now.
0
 
LVL 1

Expert Comment

by:baltman
ID: 6898941
Great! Glad to be of service.....
Happy coding!

BA
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month14 days, 13 hours left to enroll

577 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