ADO File Open Error

[Guest]

Hello,

I have a strange error I've not seen before.

When opening a recordset, I get the following error:
"The connection cannot be used to perform this operation. It is either
closed or invalid in this context."

The following snippet of code is what I am using. The last line produces
the error noted above.

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset, rst2 As New ADODB.Recordset, rst3 As New
ADODB.Recordset, rst4 As New ADODB.Recordset

Set cnn = CurrentProject.Connection
rst.Open "qry_RT_SelectRTCriteria", cnn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
rst2.Open "qry_RT_DropCurrentCriteria", cnn, adOpenKeyset,
adLockOptimistic, adCmdTableDirect

Do Until rst.EOF = True
rst3.Open "SELECT * FROM tblCriteria WHERE CriteriaDesc = '" &
rst![Round Trip Criteria] & "'", cnn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

If rst3.RecordCount > 0 Then
strSQL = "INSERT INTO tblCriteriaDetail ( CriteriaDesc,
CriteriaValue, Score, SortOrder ) " & _
" SELECT
tbl_Temp_RT_CriteriaDetail.CriteriaDesc,
tbl_Temp_RT_CriteriaDetail.CriteriaValue, tbl_Temp_RT_CriteriaDetail.Score,
tbl_Temp_RT_CriteriaDetail.SortOrder " & _
" FROM tbl_Temp_RT_CriteriaDetail WHERE
tbl_Temp_RT_CriteriaDetail.CriteriaDesc = '" & rst![Round Trip Criteria] & "'"
DoCmd.RunSQL strSQL

rst3.Close

Else

If Not rst3 Is Nothing Then
If rst3.State <> adStateClosed Then
rst3.Close
End If
End If

If Not rst4 Is Nothing Then
If rst4.State <> adStateClosed Then
rst4.Close
End If
End If

rst4.Open "Select * From tblCriteria Where [CriteriaDesc] = '" &
rst2![Current Criteria] & "'"

Any help would be appreciated.

Thanks.

 

[Brendan Reynolds]

You're not passing the connection when you try to open rst4 ...

<quote>
rst4.Open "Select * From tblCriteria Where [CriteriaDesc] = '" &
rst2![Current Criteria] & "'"
</quote>

Should be ...

rst4.Open "Select * From tblCriteria Where [CriteriaDesc] = '" &
rst2![Current Criteria] & "'", cnn

Also, the use of 'adCmdTableDirect' appears to be incorrect, as the sources
appear to be saved queries (adCmdStoredProc) or SQL statements (adCmdText).

 

[Guest]

Brendan,

Well, not sure how I missed the connection, so thanks for pointing that out.

Also, thanks for the pointer on the sources.
--
David

You're not passing the connection when you try to open rst4 ...

<quote>
rst4.Open "Select * From tblCriteria Where [CriteriaDesc] = '" &
rst2![Current Criteria] & "'"
</quote>

Should be ...

rst4.Open "Select * From tblCriteria Where [CriteriaDesc] = '" &
rst2![Current Criteria] & "'", cnn

Also, the use of 'adCmdTableDirect' appears to be incorrect, as the sources
appear to be saved queries (adCmdStoredProc) or SQL statements (adCmdText).

--
Brendan Reynolds
Access MVP

Hello,

I have a strange error I've not seen before.

When opening a recordset, I get the following error:
"The connection cannot be used to perform this operation. It is either
closed or invalid in this context."

The following snippet of code is what I am using. The last line produces
the error noted above.

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset, rst2 As New ADODB.Recordset, rst3 As New
ADODB.Recordset, rst4 As New ADODB.Recordset

Set cnn = CurrentProject.Connection
rst.Open "qry_RT_SelectRTCriteria", cnn, adOpenKeyset,
adLockOptimistic,
adCmdTableDirect
rst2.Open "qry_RT_DropCurrentCriteria", cnn, adOpenKeyset,
adLockOptimistic, adCmdTableDirect

Do Until rst.EOF = True
rst3.Open "SELECT * FROM tblCriteria WHERE CriteriaDesc = '" &
rst![Round Trip Criteria] & "'", cnn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

If rst3.RecordCount > 0 Then
strSQL = "INSERT INTO tblCriteriaDetail ( CriteriaDesc,
CriteriaValue, Score, SortOrder ) " & _
" SELECT
tbl_Temp_RT_CriteriaDetail.CriteriaDesc,
tbl_Temp_RT_CriteriaDetail.CriteriaValue,
tbl_Temp_RT_CriteriaDetail.Score,
tbl_Temp_RT_CriteriaDetail.SortOrder " & _
" FROM tbl_Temp_RT_CriteriaDetail WHERE
tbl_Temp_RT_CriteriaDetail.CriteriaDesc = '" & rst![Round Trip Criteria] &
"'"
DoCmd.RunSQL strSQL

rst3.Close

Else

If Not rst3 Is Nothing Then
If rst3.State <> adStateClosed Then
rst3.Close
End If
End If

If Not rst4 Is Nothing Then
If rst4.State <> adStateClosed Then
rst4.Close
End If
End If

rst4.Open "Select * From tblCriteria Where [CriteriaDesc] = '"
&
rst2![Current Criteria] & "'"

Any help would be appreciated.

Thanks.