ADO connection won't close using Active driver and ADO recordset in VB

[jbradley]

My report is is written using the Active driver and in the VB program I call it from I open a connection to an Access database, create a recordset, then point the report at the recordset. Finally I close the resordset and connection. My problem is that the connection never really closes until I end the program. I'm using the ActiveX control. Here's the code:

Dim cn As New ADODB.Connection, rsData As New ADODB.Recordset

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\CorSupt.mdb;"
rsData.Open "SELECT * FROM CorSupt WHERE CorSupt.LetterType='" & strLetterNumber & _
"' AND CorSupt.RecordDate=#" & strReportDate & "#", cn

With CrystalReport1
.ReportFileName = App.Path & "\" & strReportName & "p.rpt"
.SetTablePrivateData 0, 3, rsData
.Destination = crptToPrinter
.PrinterStopPage = 0
.Action = 1
.SetTablePrivateData 0, 3, Nothing
End With

rsData.Close
cn.Close

Any ideas how I can get it to drop the connection would be greatly appreciated.

 

[Guest_imported]

IF you are using the Report Viewer to display your report AND the database is a "secure" data source (one for which you have to supply a username/password), THEN your connection will not actually close until the report is no longer displayed in the report viewer. in other words, the connection will remain open until you either open a different report or close the viewer object.

HTH,
Lee
lee.meinhardt@smna.com

 

[jbradley]

Lee,

Thanks for your reply.

Actually, I was using the Crystal Reports ActiveX (OCX) control, first to export a series of one-record reports (form letters) as individual disk files, then to send a single report composed of the entire recordset directly to a printer with no user intervention. If I commented out the print routine then the connection dropped as soon as the export files were done.

I've since switched to using the RDC rather than the OCX to resolve a problem I was having with the file export process (it was displaying a page selection dialog box for every record) and that solved the problem of the connection not closing as well.

Brad Harris

 

[balves]

In any case, setting the object to Nothing in VB should destroy the connection.

Set cn = nothing