Copyfromrecordset Error 3704
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About
3704-operation Is Not Allowed When The Object Is Closed
Us Learn more about Stack Overflow the company Business Learn more about hiring developers vba recordset operation is not allowed when the object is closed or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack
Run-time Error '3704' Application-defined Or Object-defined Error
Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up run-time error '3704' operation is not allowed operation is not allowed when the object is closed vbscript when the object is closed up vote 0 down vote favorite Noob here. This vba code in excel is supposed to connect to sql 2008 using ADO, run the query and populate sheet1 in excel. Now, the heading error keeps popping up On this line " Sheet1.Range("A1").CopyFromRecordset rst". Sub Code1() Dim cnt As ADODB.Connection Dim rst As ADODB.Recordset Dim SqlQuery As String Application.ScreenUpdating = False Const operation is not allowed when the object is closed adodb recordset stADO As String = "Provider=SQLOLEDB.1;Password=NOPWDHERE;Persist Security Info=True;User ID=sa;Initial Catalog=StockControl;Data Source= PCSMIS01" Set wbBook = ActiveWorkbook Set WsSheet = wbBook.Worksheets(1) SqlQuery = " SELECT * FROm dbo.Site " Set cnt = New ADODB.Connection Set rst = New ADODB.Recordset With cnt .CursorLocation = adUseClient .Open stADO .CommandTimeout = 0 Set rst = .Execute(SqlQuery) End With Sheet1.Range("A1").CopyFromRecordset rst 'With rst ' .ActiveConnection = cnt ' .Open SqlQuery ' Sheet1.Range("A1").CopyFromRecordset rst ' .Close 'End With rst.Close cnt.Close Set rst = Nothing Set cnt = Nothing End Sub I have also tried an alternative by using the piece of commented code to copy the data into the sheet.I have researched similar topics on the internet to the best of my ability, but i am more confused now. Please help. excel vba sql-server-2008 excel-vba share|improve this question edited Nov 26 '15 at 11:28 R3uK 6,63141340 asked Nov 26 '15 at 10:17 Thabang Keele 32 Replace Sheet1 by WsSheet and that should work! ;) –R3uK Nov 26 '15 at 10:28 and in general: do not use the sa account for such queries. Don't paste the password for your sa account in a public forum ;) –cboden Nov 26 '15 at
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about
Operation Is Not Allowed When The Object Is Open Vba
hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask operation is not allowed when the object is closed vb6 Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join
Set Nocount On
them; it only takes a minute: Sign up Excel VBA Run-time Error '3704' up vote 0 down vote favorite I am getting run-time error '3704' when trying to dump a recordset into the tab of an Excel 2010 workbook. http://stackoverflow.com/questions/33935959/run-time-error-3704-operation-is-not-allowed-when-the-object-is-closed The recordset should contain a couple of hundred records that come from a stored procedure I wrote using SQL Server 2008 R2. I know the exact same statement executes when ran in SQL Server Management Studio and I have used the same connection string in the past so I am pretty sure those parts of my code are working correctly. I have researched the error and the only solutions I have seen have to do with the connection http://stackoverflow.com/questions/22204409/excel-vba-run-time-error-3704 timing out. You will see that I have set CommandTimeout = 30 (I assume that is in seconds). I get the error in just a few seconds of clock time so I am sure that this is not a problem with my connection timing out. My VBA code: Sub Add_Results_Of_ADO_Recordset() Dim cnt As ADODB.Connection Dim rst As ADODB.Recordset Dim stSQL As String Dim wbBook As Workbook Dim wsSheet As Worksheet Dim rnStart As Range Const stADO As String = "Provider=SQLOLEDB.1;User ID =xxxxx;Password=xxxxx;" & _ "Persist Security Info=False;" & _ "Initial Catalog=MyDatabase;" & _ "Data Source=xxxxxxxxxxxx" Set wbBook = ActiveWorkbook Set wsSheet = wbBook.Worksheets(1) With wsSheet Set rnStart = .Range("TopLeft").Offset(1, 0) End With Set cnt = New ADODB.Connection With cnt .CursorLocation = adUseClient .Open stADO .CommandTimeout = 30 End With stSQL = "EXEC MyDatabase.dbo.PolicyList '2/1/2014','2/1/2014','BOOK'" With cnt Set rst = .Execute(stSQL) End With 'Dump recordset into my WorkBook rnStart.CopyFromRecordset rst 'This is where the error occurs!!! 'Cleaning up. rst.Close cnt.Close Set rst = Nothing Set cnt = Nothing End Sub This is the error message I get: Run-time error '3704': Operation is not allowed when object is closed. excel-vba share|improve this question edited Mar 5 '14 at 17:13 Konrad Morawski 4,08633164 asked Mar 5 '14 at 17:07 GlennW 63 1 Have you checked if there are any records being fetched? –Pankaj Jaju Mar 5 '14 at 17:15 @Pankaj Jaju I have en
SQL Server 2014 Express resources Windows Server 2012 resources Programs MSDN subscriptions Overview Benefits Administrators Students Microsoft Imagine Microsoft https://social.msdn.microsoft.com/Forums/en-US/11853173-14c6-40dd-8057-6ce57d5a0ef8/runtime-error-3704-loading-recordset-from-stored-procedure?forum=isvvba Student Partners ISV Startups TechRewards Events Community Magazine Forums Blogs Channel 9 Documentation APIs and reference Dev centers Retired content Samples We’re sorry. https://www.experts-exchange.com/questions/24467466/3704-operation-is-not-allowed-when-the-object-is-closed.html The content you requested has been removed. You’ll be auto redirected in 1 second. Ask a question Quick access Forums home Browse forums users is not FAQ Search related threads Remove From My Forums Asked by: RunTime Error 3704 Loading Recordset from Stored Procedure Microsoft ISV Community Center > Visual Basic for Applications (VBA) Question 0 Sign in to vote I am attempting to load a recordset generated by an SQL stored procedure is not allowed (no parameters) into Excel using VBA. When my code hits the last line, highlighted below, a "RunTime Error 3704: Application-Defined or Object-Defined Error" error occurs. Any ideas? Dim RSData2 As ADODB.RecordsetDim DBConn As New ADODB.Connection Const stADO = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _ "Initial Catalog=PennTel_Core_Demo;" & _ "Data Source=172.17.9.44"stSQL = "aaa_WPAHS1" Set RSData2 = New ADODB.Recordset 'On Error GoTo Cleanup Call RSData2.Open(stSQL, stADO, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdStoredProc) Call Range("C10").CopyFromRecordset(RSData2) kedst1 Thursday, August 03, 2006 3:38 PM Reply | Quote All replies 0 Sign in to vote Hi I would normally use the below syntax Range("C10").CopyFromRecordset RSData2 But I suspect that the recordset has not been correctly returned. Try checking to see if the recordset has records or is equal nothing before this line. I have seen a number of posts where the time taken to execute procedures has caused simal
Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > 3704 operation is not allowed when the object is closed Want to Advertise Here? Solved 3704 operation is not allowed when the object is closed Posted on 2009-06-05 VB Script MS SQL Server 2005 1 Verified Solution 8 Comments 2,112 Views Last Modified: 2012-05-07 I am trying to run a select statement from SQL which I do all the time, but for some reason I am getting this error on this one. Can someone see what I am doing wrong please? Sub Get_Detail() Call ClearForm Dim command As String Dim parm As String parm = Range("D1").Value Set SuiteConn = New ADODB.Connection Dim strConn As String strConn = "PROVIDER=SQLOLEDB;" strConn = strConn & "Data Source=10.0.0.3; Initial Catalog=mydatabase;" strConn = strConn & " INTEGRATED SECURITY=sspi;" SuiteConn.Open strConn Dim rsCas As ADODB.Recordset Set rsCas = New ADODB.Recordset With rsCas .ActiveConnection = SuiteConn .Open "ap_detail '" & parm & "'" Sheet3.Range("A3").CopyFromRecordset rsCas .Close End With SuiteConn.Close Set rsCas = Nothing Set RSuiteConn = Nothing End Sub Select all Open in new window 0 Question by:rjackmanwyn