ADO SQL Connection Closing

[ben91686]

Sub ConnectDB()
Dim sFileName       As String
sFileName = ActiveWorkbook.FullName
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sFileName _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
End Sub
 
Sub ImportPri
 
.......
 
Call ConnectDB
 
sAreaName = Range("sAreaName").value
 
Qry = "SELECT RelativePriority, Status, ID, ProjectID, Tranche, DemandStartDate, DemandEndDate, Contingency, " _
        & "AnchorStatus, AnchorBucket, ProjectName, Predecessors, HardStopDate, ProjectStartDate, ProjectEndDate " _
        & "FROM [Prioritization$A10:AN1010] " _
        & "WHERE DevelopmentUnit = '" & sAreaName & "' " _
        & "ORDER BY [Prioritization$A10:AN1010].Order ASC"
 
Set rst = New ADODB.Recordset
rst.ActiveConnection = cnn
    Debug.Print cnn.State
rst.Open Qry, cnn, adOpenStatic, adLockReadOnly
 
.... 
 
End Sub

I keep getting the following error message on the last line of code... the connection for viewing your linked excel worksheet was lost

I've heard using ADO connections it's possible to inadvertently close the connection but the connection state is open on the line before. Anyone have any experience with this or can offer a possible solution?

Thanks!

 

[CharlesChuckieCharles]

Why use an ADO SQL connection to another workbook? isn't that just causing you unnecessary Pain

 

[ben91686]

Why use an ADO SQL connection to another workbook? isn't that just causing you unnecessary Pain

Clearly it is. But it does run faster (back when it used to run).

 

[CharlesChuckieCharles]

Is your connection cnn set up as a public variable?