[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Connection held open

Posted on 2002-03-26
5
Medium Priority
?
175 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

656 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