Excel Runtime 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
Runtime Error 3704 Vba
About Us Learn more about Stack Overflow the company Business Learn more about runtime error 3704 in vb6 hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join
Runtime Error 3704 Operation Not Allowed
the Stack 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 Excel VBA Run-time Error run time error 3704 excel vba '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. 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 run time error 3704 object is closed 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 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
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
Run Time Error 3704 In Spectrum
you can post: click the register link above to proceed. To start viewing kdk software run time error 3704 messages, select the forum that you want to visit from the selection below. Results 1 to 10 of 10
Run-time Error '3704' Application-defined Or Object-defined Error
Thread: VBA-Excel error 3704- operation is not allowed when the object is closed Tweet Thread Tools Show Printable Version Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded http://stackoverflow.com/questions/22204409/excel-vba-run-time-error-3704 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 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 http://www.vbforums.com/showthread.php?569433-VBA-Excel-error-3704-operation-is-not-allowed-when-the-object-is-closed 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 + "] " & 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
in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that http://forum.chandoo.org/threads/run-time-error-3704-operation-not-allowed-when-object-is-closed.15772/ we have people here for whom English is not there first language and we need http://www.dreamincode.net/forums/topic/279878-run-time-error-3704-operation-is-not-allowed-the-object-is-closed/ to allow for this in our dealings. Yours, Chandoo Hi All Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam Post Spam and you Will Be Deleted as a User Hui... When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post. Welcome Message to New time error users Spam Sample File Run time error 3704 operation not allowed when object is closed Discussion in 'VBA Macros' started by Satish.digit, Apr 1, 2014. Satish.digit Member Messages: 44 Hi, I am using below code to update record based on ID but i am getting Runtime error 3704 can any one help me on this. Private Sub LblAllot_Click() Dim counter As Integer, cn As New ADODB.Connection, ssql14 As String counter = 0 DB_Open_Connection For i = 0 time error 3704 To lstClaimLineList.ListCount - 1 If lstClaimLineList.Selected(i - counter) Then ssql14 = ("update Table1 set [Name] = 'agandhi' where ID = 2") cn.Execute ssql14 End If Next i DB_Close_Connection End Sub Satish.digit, Apr 1, 2014 #1 Luke M Excel Ninja Messages: 9,209 There are several objects/macros references in your sample that we have no insight to. Do you have a check to make sure that the query is returning at least 1 record? When you get the error message, what specific line is highlighted when you debug? Luke M, Apr 1, 2014 #2 NARAYANK991 Excel Ninja Messages: 14,033 Hi Satish , Correct me if my understanding is wrong , but DB_Open_Connection is a subroutine , which is opening the database connection ; since you are declaring : cn As New ADODB.Connection within the parent procedure , even if the connection is opened in the subroutine , cn will never get a value ; when you return to the parent procedure and execute the statement : cn.Execute ssql14 cn is not an open object. You might have to rewrite your code either to make DB_Open_Connection a function so that you can have something like : cn = DB_Open_Connection() or pass cn as a parameter to the DB_Open_Connection procedure so that when you return to the parent procedure cn is available. Narayan NARAYANK991, Apr 1, 2014 #3 Luke M likes this.
is closed Page 1 of 1 New Topic/Question Reply 9 Replies - 44556 Views - Last Post: 23 May 2012 - 06:53 AM Rate Topic: #1 doty New D.I.C Head Reputation: 0 Posts: 4 Joined: 18-May 12 Run-time error 3704 operation is not allowed the object is closed Posted 19 May 2012 - 12:59 AM Guys i need you help in this error i use vb6 project with orcal DB and when i want to enter admin user name and password he give me this error Run-time error 3704 operation is not allowed the object is closed and when i click Debug he give me this code and the like is Yalow pls i need u help guys Dim cn As adodb.Connection Dim cmd1 As String Dim rs As adodb.Recordset Dim rk As adodb.Recordset Dim i, j As Integer Dim sqlcmd, sqk, strsql, strname, str1, rate, a, b, c As String Private Sub cmdCancel_Click() main1.Show Unload Me End Sub Private Sub cmdOK_Click() Dim flag As Integer flag = 0 [b]If rs.RecordCount <> 0 Then[/b] rs.MoveFirst Do While Not rs.EOF If txtUserName.Text = rs.Fields(0) And txtPassword.Text = rs.Fields(1) Then flag = 1 Exit Do Else rs.MoveNext End If Loop If flag = 0 Then MsgBox "INVALID USER" txtUserName.Text = "" txtPassword.Text = "" Else admn.Show Unload Me End If Else MsgBox "NO RECORDS PRESENT !!!!" End If End Sub Private Sub Form_Load() On Error Resume Next cmd1 = "Provider=MSDAORA.1;User ID=scott;password=tiger;Persist Security Info=False" Set cn = New adodb.Connection Set rs = New adodb.Recordset With cn .ConnectionString = cmd1 .CursorLocation = adUseClient .Open End With rs.Open "SELECT * FROM admlog", cn, 2, 3 End Sub Is This A Good Question/Topic? 0 Back to top MultiQuote Quote + Reply Replies To: Run-time error 3704 operation is not allowed the object is closed #2 maj3091 D.I.C Lover Reputation: 327 Posts: 1,945 Joined: 26-March 09 Re: Run-time error 3704 operation is not allowed the object is closed Posted 19 May 2012 - 01:21 AM As a guess, I would say your record set isn't getting populated. Try putting a breakpoint in your form_load and step through the code to check that the connection is opened and/or the recordset is populated. Try adding some "proper" error handling to it as well, On Error Resume Next is going to hide issues. Was This Post Helpful? 0 Back to top MultiQuote Quote + Reply #3 doty New D.I.C Head Reputation: 0 Posts: 4 Joined: 18-May 12 Re: Run-time error 3704 operation is not allowed the object is closed Posted 19 May 2012 - 01:26 AM Thank you Maj but is not working the issue is here If rs.RecordCount <> 0 Then Was This Post Helpful? 0 Back to top MultiQuote Quote + Reply #4 maj3091 D.I.C Lover Reputation: 327 Posts: 1,945 Joined: 26