Solved

Connection held open

Posted on 2002-03-26
5
169 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
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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS expression Issue finding a string 10 79
Windows 10 start screen issues 9 55
Computer crashes, following error message in event manager 5 199
MsgBox 4 61
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
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…

778 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