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

Open 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.
dsimcoxAsked:
Who is Participating?
 
dsimcoxConnect With a Mentor Author Commented:
This issue has been resolved.  The problem was that I had mistakenly coded a dimension statement for my connection string twice - one in each of two modules. The procedure for the connection was in one module, and the query was in the other. So when the query was executed, the reference to the connection was established - and then lost.

It didn't show up in my older workbook because the procedures were in one module and the variable for the connection string was only dimensioned once.
0
 
David L. HansenProgrammer AnalystCommented:
This may help

cnn.ConnectionString = "Provider=SQLNCLI10;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CRV_PMO;Data Source=MAPDBSHRN01\DEV; "
0
 
dsimcoxAuthor Commented:
The connection opened with your connection string, but  the SELECT query triggered the same error.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
David L. HansenProgrammer AnalystCommented:
So there was some progress?
0
 
dsimcoxAuthor Commented:
Sorry - but it behaves exactly the same as the connection string in my original post. The connection succeeds, but the query fails.
0
 
dsimcoxAuthor Commented:
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.
0
 
David L. HansenProgrammer AnalystCommented:
You may need to get the SQLNCLI10 download.  See here:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=15748
0
 
dsimcoxAuthor Commented:
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.
0
 
dsimcoxAuthor Commented:
I can't give myself a better grade since I made a pretty stupid error.
0
 
dsimcoxAuthor Commented:
An object lesson from an object variable
0
 
David L. HansenProgrammer AnalystCommented:
Yes indeed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.