• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

Connection held open

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
iJolly4
Asked:
iJolly4
  • 3
1 Solution
 
baltmanCommented:
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
 
CodeWizrdCommented:

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
 
baltmanCommented:
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
 
iJolly4Author Commented:
Thanks,

It works a treat now.
0
 
baltmanCommented:
Great! Glad to be of service.....
Happy coding!

BA
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now