Access Vba Error 3704
Contents |
not allowed when the object is closed If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start 3704-operation is not allowed when the object is closed viewing messages, select the forum that you want to visit from the selection below. Results run-time error '3704' application-defined or object-defined error 1 to 10 of 10 Thread: VBA-Excel error 3704- operation is not allowed when the object is closed Tweet Thread Tools Show Printable Version operation is not allowed when the object is closed sql Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode May 15th, 2009,02:18 AM #1 krishna1985 View Profile View Forum Posts Thread Starter New Member Join Date May 2009 Posts 4 VBA-Excel error operation is not allowed when the object is closed vbscript 3704- operation is not allowed when the object is closed hi, i am using VBA(excel-macros) to pull data from MS-Sql 2000. There is a common module that connects to the database and calls different stored procedures. Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim Parameter As String Set cnn = New ADODB.Connection 'Create Connection Set rst = New ADODB.Recordset 'Create Recordset With cnn cnn.Open "Provider=SQLOLEDB; " .............." End With Set rst = cnn.Execute("EXEC [dbo].[" + NameProcedure
Operation Is Not Allowed When The Object Is Open Vba
+ "] " & Parameter) If rst.EOF = True Then ' ' Now at this point(rst.EOF), i'm getting the error. The parameters passed 'NameProcedure'(name of the stored procedure in SQL) and 'Parameter' comes from other modules which calls this module to execute on SQL. Different modules provides different 'NameProcedure' for this execute statement. There is no problem with the connection because the same code is working fine for modules that call other stored procedures. Even when i run the Stored procedure on SQL, it is working fine. The problem is when one particular stored procedure (which is giving proper results when run on sql) is being made to run. Reply With Quote May 15th, 2009,03:10 AM #2 JJJCR_FOX View Profile View Forum Posts Addicted Member Join Date Jan 2009 Posts 233 Re: VBA-Excel error 3704- operation is not allowed when the object is closed What's the error message? print screen the error with no private data The taller the bamboo grows the lower it bends... Reply With Quote May 15th, 2009,03:36 AM #3 krishna1985 View Profile View Forum Posts Thread Starter New Member Join Date May 2009 Posts 4 Re: VBA-Excel error 3704- operation is not allowed when the object is closed here is the screen-shot: Attached Images Reply With Quote May 15th, 2009,05:16 AM #4 JJJCR_FOX View Profile View Forum Posts Addicted Member Join Date Jan 2009
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 hiring developers
Operation Is Not Allowed When The Object Is Closed Vb6
or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x error 3704 teradata Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it operation is not allowed when the object is open vb6 only takes a minute: Sign up run-time error '3704' operation is not allowed when the object is closed in VB6 while using SP using temp tables up vote 3 down vote favorite I am using the Stored procedure "sp_Missingdata" to http://www.vbforums.com/showthread.php?569433-VBA-Excel-error-3704-operation-is-not-allowed-when-the-object-is-closed get the data and to display in the MSFlexGrid, But i am getting the error as - run-time error '3704' operation is not allowed when the object is closed I searched in google a lot and changed these things - 1. set NOCOUNT ON for the Stored procedure. 2. set SET ANSI_WARNINGS OFF for stored procedure. Private Sub Command2_Click() Dim cmd As ADODB.Command Dim sqlnew, dd Set cmd = New ADODB.Command cmd.ActiveConnection = ArtmConn cmd.CommandType = adCmdStoredProc cmd.CommandText = "sp_missingData" Dim http://stackoverflow.com/questions/14950443/run-time-error-3704-operation-is-not-allowed-when-the-object-is-closed-in-vb6 rsnew As ADODB.Recordset Set rsnew = New ADODB.Recordset Set rsnew = cmd.Execute If Not rsnew.EOF Then <-- Error is occuring here Set tblSop.DataSource = rsnew End If rsnew.Close Set rsnew = Nothing End Sub Note- My SP is using the temp table- here is my sp SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET NOCOUNT ON GO SET ANSI_WARNINGS OFF GO ALTER procedure [dbo].[sp_missingData] --exec [sp_missingData] as begin delete from tbl_missingData select ol_code as outletNo, start as StartreceiptNo into #temp from (select l.s_ol_code as ol_code, l.sno + 1 as start from vSalesNo as l left outer join vSalesNo as r on l.sno + 1 = r.sno and l.s_ol_code = r.s_ol_code where r.sno is null) as tmpa inner join (select s_ol_code, max(s_no) as s_no from sales group by s_ol_code) as tmpb on s_ol_code = ol_code and start <= substring(s_no, 0, 11) where start!=0 union all select ol_code as outletNo, start as EndreceiptNo from (select l.s_ol_code as ol_code, l.sno - 1 as start from vSalesNo as l left outer join vSalesNo as r on l.sno - 1 = r.sno and l.s_ol_code = r.s_ol_code where r.sno is null) as tmpa inner join (select s_ol_code, max(s_no) as s_no from sales group by s_ol_code) as tmpb on s_ol_code = ol_code and start <= substring(s_no, 0, 11) where start!=0 order by ol_code, start; insert into tbl_missingData SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY outletNo) AS rownumber, * FROM #temp) AS Documents select *,dbo.countOfIds(out
Forums Excel Questions SQL via VBA error: Operation is not allowed when the object is closed Page 1 of 3 123 Last Jump to page: Results 1 to 10 http://www.mrexcel.com/forum/excel-questions/691520-sql-via-visual-basic-applications-error-operation-not-allowed-when-object-closed.html of 26 1Likes Top All SQL via VBA error: Operation is not allowed when the object is closedThis is a discussion on SQL via VBA error: Operation is not allowed when the object is closed within the Excel Questions forums, part of the Question Forums category; Hello, I've been trying to connect to a SQL Server database, but consistently get an "error 3704: Operation is is not not ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Mar 14th, 2013,08:04 PM #1 Viceroy369 New Member Join Date Jan 2013 Posts 25 SQL via VBA error: Operation is not allowed when the object is closed Hello, I've is not allowed been trying to connect to a SQL Server database, but consistently get an "error 3704: Operation is not allowed when the object is closed" on this line: ws1.Range("A14").CopyFromRecordset rs The SQL query itself runs fine when I paste it into the query window of the SQL Server, so I don't think it's that. Also, I have some code to check if the recordset is empty, and it is not empty. Since I open the recordset just before I copy it, not sure why it's saying the RS is closed. Here is my complete code: Dim cn As ADODB.Connection Dim Server_Name As String Dim Database_Name As String Dim User_ID As String Dim Password As String Dim SQLStr As String Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset Dim strConn As String Dim objMyCmd As ADODB.Command Set objMyCmd = New ADODB.Command Server_Name = Range("B1").Value Database_Name = Range("B2").Value User_ID = Range("B3").Value Password = Range("B4").Value Set cn = New ADODB.Connection strConn = "PROVIDER=SQLOLEDB;" strConn = strConn & "DATA SOURCE=" & Server_Name & ";INITIAL CATALOG=" & Database_Name & ";" strConn = strConn & "User ID=" & User_ID & "; Password=" & Password cn.ConnectionString = strConn cn.Open Set objMyCmd.ActiveCo