dsimcox
asked on
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;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:
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.
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
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
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.
ASKER
The connection opened with your connection string, but the SELECT query triggered the same error.
So there was some progress?
ASKER
Sorry - but it behaves exactly the same as the connection string in my original post. The connection succeeds, but the query fails.
ASKER
The error message references a connection or permissions issue - it says that "the connection cannot be used to perform this operation" i.e the SELECT query.
You may need to get the SQLNCLI10 download. See here:
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=15748
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=15748
ASKER
OK - here's some new info.
I think this is an Excel 2010 issue.
I copied the connection procedure and the query procedure (created in an Excel 2010 workbook) to a workbook that was created in Excel 2007 (and RUN in Excel 2010) and the query runs perfectly.
I checked the Tools:References and both workbooks have the same references.
This is the first time I've created VBA code to query SQL Server from Excel 2010. Yet, it works fine to write to the table - but I cannot query the table in the Excel 2010-created workbook.
I think this is an Excel 2010 issue.
I copied the connection procedure and the query procedure (created in an Excel 2010 workbook) to a workbook that was created in Excel 2007 (and RUN in Excel 2010) and the query runs perfectly.
I checked the Tools:References and both workbooks have the same references.
This is the first time I've created VBA code to query SQL Server from Excel 2010. Yet, it works fine to write to the table - but I cannot query the table in the Excel 2010-created workbook.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I can't give myself a better grade since I made a pretty stupid error.
ASKER
An object lesson from an object variable
Yes indeed.
cnn.ConnectionString = "Provider=SQLNCLI10;Integr