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;Integ
rated 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)
GetProjectNames = 0
Set rs = Nothing
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.