?
Solved

Excel 2010 Connection to SQL Server 2008R2

Posted on 2011-10-20
11
Medium Priority
?
354 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:dsimcox
  • 7
  • 4
11 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 37001758
This may help

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

Author Comment

by:dsimcox
ID: 37002086
The connection opened with your connection string, but  the SELECT query triggered the same error.
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 37002227
So there was some progress?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:dsimcox
ID: 37002319
Sorry - but it behaves exactly the same as the connection string in my original post. The connection succeeds, but the query fails.
0
 

Author Comment

by:dsimcox
ID: 37002349
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
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 37002602
You may need to get the SQLNCLI10 download.  See here:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=15748
0
 

Author Comment

by:dsimcox
ID: 37002662
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
 

Accepted Solution

by:
dsimcox earned 0 total points
ID: 37003497
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
 

Author Closing Comment

by:dsimcox
ID: 37023134
I can't give myself a better grade since I made a pretty stupid error.
0
 

Author Comment

by:dsimcox
ID: 37003513
An object lesson from an object variable
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 37003796
Yes indeed.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question