Solved

Connection held open

Posted on 2002-03-26
5
167 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 100 total points
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
Thanks,

It works a treat now.
0
 
LVL 1

Expert Comment

by:baltman
Comment Utility
Great! Glad to be of service.....
Happy coding!

BA
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

772 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

9 Experts available now in Live!

Get 1:1 Help Now