asked on

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

ASKER CERTIFIED SOLUTION

baltman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CodeWizrd

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.

baltman

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

iJolly4

ASKER

Thanks,

It works a treat now.

baltman

Great! Glad to be of service.....
Happy coding!

BA