Excel 2010 Connection to SQL Server 2008R2

I routinely use Excel (2010) VBA to query SQL Server tables on a network server. Yesterday, I created a new table on a different database and server where I have dbo permissions, and populated the table using a VBA routine to scan the worksheet and write to the table - worked fine.

Here's the problem.  Using the same connection string that I used for the Write, I'm unable to execute a simple SELECT query to the table I just populated. I'm getting the following error:

  "The connection cannot be used to perform this operation . It is either closed or invalid in this context."

In discussing this with my database administrator, I learned that the server I've used in the past is SQL Server 2005, and the one I'm having trouble querying is SQL Server 2008R2.

I don't understand why I can successfully connect to and UPDATE the table, but I can't SELECT from it. Are there changes to the connection string that I need to make to accommodate 2008R2?

Here's the connection string I'm using:

cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CRV_PMO;Data Source=MAPDBSHRN01\DEV; "

The cnn.Open statement succeeds, but when I try to open the recordset with this code, the error occurs:

 

Public Function GetProjectNames()

If cnn Is Nothing Then Call CONNECT_TO_SERVER

strSQL = "SELECT DISTINCT ProjectName " & _
         "FROM tblPIP_TeamMembers " & _
         "ORDER BY ProjectName ;"
         
Set rs = New ADODB.Recordset

rs.Open strSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText

If rs.RecordCount > 0 Then
    GetProjectNames = rs.GetRows(rs.RecordCount)
Else:
    GetProjectNames = 0
End If

rs.Close
Set rs = Nothing

End Function

Select allOpen in new window

I am able to execute this query in SSMS using Windows Authentication with no problem - but when I run the same query from Excel - it fails.