PRB: Add Method of QueryTables with Excel Automation Generates Run-time Error Message "5" (263498)

MORE INFORMATION

Steps to Reproduce Behavior

The following steps demonstrate how you might receive this error while automating Excel from a Visual Basic client. However, you should note that this problem can occur with any client that automates Microsoft Excel and is not specific to Visual Basic Automation clients.

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. Select Microsoft Excel 9.0 Object Library and Microsoft ActiveX Data Objects 2.5 (or 2.6) Library, and then click OK.
4. 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.
   
   
5. 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."

To correct the error, modify the code so that the recordset uses a client-side cursor. In the sample code, change the following

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

to:

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