Accessing SQL Database in Excel-VBA

Question

I am copying an VBA code snippet from MSDN that shows me how to grab results from a SQL query into excel sheet  Excel 2007    Sub GetDataFromADO         Declare variables          Set objMyConn   New ADODB Connection         Set objMyCmd   New ADODB Command         Set objMyRecordset   New ADODB Recordset       Open Connection          objMyConn ConnectionString    Provider SQLOLEDB Data Source localhost User ID abc Password abc           objMyConn Open       Set and Excecute SQL Command          Set objMyCmd ActiveConnection   objMyConn         objMyCmd CommandText    select   from myTable          objMyCmd CommandType   adCmdText         objMyCmd Execute       Open Recordset          Set objMyRecordset ActiveConnection   objMyConn         objMyRecordset Open objMyCmd       Copy Data to Excel          ActiveSheet Range  A1   CopyFromRecordset  objMyRecordset   End Sub   I have already added Microsoft ActiveX Data Objects 2 1 Library under as a reference  And this database is accessible   Now  when I run this subroutine  it has an error   Run-time error 3704  Operation is not allowed when object is closed   On the statement      ActiveSheet Range  A1   CopyFromRecordset  objMyRecordset    Any idea why   Thanks

User · Answer

Is that a proper connection string  Where is the SQL Server instance located   You will need to verify that you are able to conenct to SQL Server using the connection string  you specified above   EDIT  Look at the State property of the recordset to see if it is Open  Also  change the CursorLocation property to adUseClient before opening the recordset

User · Answer

Add set nocount on to the beginning of the stored proc  if you re on SQL Server    I just solved this problem in my own work and it was caused by intermediate results  such as  1203 Rows Affected   being loaded into the Recordset I was trying to use

User · Answer

I ve added the Initial Catalog to your connection string   I ve also abandonded the ADODB Command syntax in favor of simply creating my own SQL statement and open the recordset on that variable   Hope this helps   Sub GetDataFromADO        Declare variables          Set objMyConn   New ADODB Connection         Set objMyRecordset   New ADODB Recordset         Dim strSQL As String       Open Connection          objMyConn ConnectionString    Provider SQLOLEDB Data Source localhost Initial Catalog MyDatabase User ID abc Password abc           objMyConn Open       Set and Excecute SQL Command          strSQL    select   from myTable        Open Recordset          Set objMyRecordset ActiveConnection   objMyConn         objMyRecordset Open strSQL                   Copy Data to Excel          ActiveSheet Range  A1   CopyFromRecordset  objMyRecordset   End Sub

User · Answer

Suggested changes    Do not invoke the Command object s Execute method  Set the Recordset object s Source property to be your Command object  Invoke the Recordset object s Open method with no parameters  Remove the parentheses from around the Recordset object in the call to CopyFromRecordset  Actually declare your variables      Revised code   Sub GetDataFromADO         Declare variables          Dim objMyConn As ADODB Connection         Dim objMyCmd As ADODB Command         Dim objMyRecordset As ADODB Recordset          Set objMyConn   New ADODB Connection         Set objMyCmd   New ADODB Command         Set objMyRecordset   New ADODB Recordset       Open Connection          objMyConn ConnectionString    Provider SQLOLEDB Data Source localhost User ID abc Password abc               objMyConn Open       Set and Excecute SQL Command          Set objMyCmd ActiveConnection   objMyConn         objMyCmd CommandText    select   from mytable          objMyCmd CommandType   adCmdText       Open Recordset          Set objMyRecordset Source   objMyCmd         objMyRecordset Open       Copy Data to Excel          ActiveSheet Range  A1   CopyFromRecordset objMyRecordset  End Sub

User · Answer

I m sitting at a computer with none of the relevant bits of software  but from memory that code looks wrong  You re executing the command but discarding the RecordSet that objMyCommand Execute returns   I d do   Set objMyRecordset   objMyCommand Execute      and then lose the  open recordset  part

User · Answer

firedrawndagger  to list field names column headers iterate through the recordset Fields collection and insert the name   Dim myRS as ADODB Recordset Dim fld as Field Dim strFieldName as String   For Each fld in myRS Fields     Activesheet Selection   fld Name      Some code that moves to next column  Next

[sql] Accessing SQL Database in Excel-VBA

Examples related to sql

Examples related to vba

Examples related to excel

Examples related to adodb