Add method of QueryTables with Excel automation generates run-time error message "5"

• Use a client-side cursor.
• Use another technique for transferring the recordset to Excel. See the "References" section of this article for resources.

Steps to Reproduce Behavior

1. Create a new Standard EXE project in Visual Basic. Form1 is created by default.
2. Add a CommandButton control to Form1.
3. On the Project menu, click References.
4. Click the Object Library for your version of Excel. For example, click one of the following options:
   • For Microsoft Office Excel 2007, select Microsoft Excel 12.0 Object Library.
   • For Microsoft Office Excel 2003, select Microsoft Excel 11.0 Object Library.
   • For Microsoft Excel 2002, select Microsoft Excel 10.0 Object Library.
   • For Microsoft Excel 2000, select Microsoft Excel 9.0 Object Library.
5. Select one of the following:
   • Microsoft ActiveX Data Objects 2.6
   • Microsoft ActiveX Data Objects 2.5
6. Click OK to close the References dialog box.
7. Add the following code to the Click event of the CommandButton:

       Dim xlApp As Excel.Application
       Dim xlBook As Excel.Workbook
   
       Dim xlSheet As Excel.Worksheet
       Dim cn As ADODB.Connection
       Dim rs As ADODB.Recordset
       Dim cmd As ADODB.Command
       Dim oQueryTable As Excel.QueryTable
       
       'Start a new workbook in Excel
       Set xlApp = CreateObject("Excel.Application")
       xlApp.Visible = True
       xlApp.UserControl = True
       Set xlBook = xlApp.Workbooks.Add
       Set xlSheet = xlBook.Worksheets(1)
       
       'Connect to local SQL Server. You will need to replace <username> and <strong password>
       'with the User ID and password of an account who has appropriate permissions.
       Set cn = New ADODB.Connection
       cn.Open "Provider=SQLOLEDB.1;Data Source=YourServer;" & _
               "Password=<strong password>;User ID=<username>;Initial Catalog=Northwind"
       
       'Generate the recordset
       Set rs = New ADODB.Recordset
       rs.Open "Select * from Products", cn
       
       'Create the query table on the worksheet
       Set oQueryTable = xlSheet.QueryTables.Add(rs, xlSheet.Cells(1, 1))
       oQueryTable.Refresh
       
       'Close the recordset and the connection
       rs.Close
       cn.Close
       Set rs = Nothing
       Set cn = Nothing
   					

   NOTE: This code generates a recordset from the sample database Northwind on SQL Server. In the connection string, change "YourServer" to the name of your SQL Server.
   
   
8. Press the F5 key to run the application, and then click the CommandButton.
   
   Results: The line of code that attempts to add the QueryTable generates the run-time error message "5."

'Generate the recordset
Set rs = New ADODB.Recordset
rs.Open "Select * from Products", cn
				

'Generate the recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "Select * from Products", cn