Excel Vba Error 3709
Contents |
Forums Excel Questions Run time Error 3709 Results 1 to 8 of 8 Run time Error 3709This is a discussion on Run time Error 3709 within the Excel Questions forums, part of the Question run time error 3709 the connection cannot be used Forums category; Code: Sub getdata() cur_mon = Date - (Weekday(Date, 2) - 1) cur_fri run time error 3709 the search key was not found = Date + (5 - Weekday(Date, 2)) mondt ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread run-time error '3709' requested operation requires an ole db to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Nov 17th, 2009,04:39 AM #1 hsk Board Regular Join Date Oct 2006 Posts
Runtime Error 3709 Vb6
563 Run time Error 3709 Code: Sub getdata() cur_mon = Date - (Weekday(Date, 2) - 1) cur_fri = Date + (5 - Weekday(Date, 2)) mondt = Year(cur_mon) & "-" & Day(cur_mon) & "-" & Month(cur_mon) & " 00:00:00" fridt = Year(cur_fri) & "-" & Day(cur_fri) & "-" & Month(cur_fri) & " 00:00:00" mydb = ............... (my sheet name) mypath = ...... (path C:\ ...etc) & mydb ' Connet to database error 3709 the connection cannot be used to perform this operation Dim cn As ADODB.Connection Set cn = New ADODB.Connection With cn .Provider = "MSDASQL" .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & "DBQ=" & mypath & "; ReadOnly=False;" .Open End With Set rs = New ADODB.Recordset rs.Open "SELECT * FROM [Sheet1$]" cn.Close Set cn = Nothing End Sub I am trying to query a sheet , getting run time error 3709 on the line rs.Open. any help ? Share Share this post on Digg Del.icio.us Technorati Twitter Thanks, hsk Progress is not made by early risers, but by the LAZY people, trying to find easier ways to do the same ! Reply With Quote Nov 17th, 2009,04:56 AM #2 Richard Schollar MrExcel MVPModeratorInactive Join Date Apr 2005 Location UK Posts 23,696 Re: Run time Error 3709 You need to tell it which connection to use: Code: rs.Open "SELECT * FROM [Sheet1$]", cn Share Share this post on Digg Del.icio.us Technorati Twitter Richard Schollar Using xl2013 Reply With Quote Nov 17th, 2009,04:56 AM #3 Andrew Poulsom MrExcel MVPModerator Join Date Jul 2002 Posts 73,092 Re: Run time Error 3709 This may help you: http://support.microsoft.com/kb/257819#RetrieveExcel Share Share this post on Digg Del.icio.us Technorati Twitter Microsoft MVP - Excel Reply With Quote Nov 17th, 2009,05:19 AM #4 hsk Board Regular Join Date Oct 2006
be..... If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to the connection cannot be used to perform this operation vba register before you can post: click the register link above to
3709 Error In Vb
proceed. To start viewing messages, select the forum that you want to visit from the selection below.
Adodb.recordset Error '800a0e7d'
Results 1 to 11 of 11 Thread: Run-time error '3709' The connection cannot be..... Tweet Thread Tools Show Printable Version Subscribe to this Thread… Display Linear Mode Switch to http://www.mrexcel.com/forum/excel-questions/430015-run-time-error-3709-a.html Hybrid Mode Switch to Threaded Mode Nov 23rd, 2010,03:00 PM #1 Patoh View Profile View Forum Posts Thread Starter Lively Member Join Date Nov 2010 Posts 71 Run-time error '3709' The connection cannot be..... Hey people! Watsup! i need your help am getting a Run-time error '3709': (The connection cannot be used to perform this operation. Its either http://www.vbforums.com/showthread.php?633744-Run-time-error-3709-The-connection-cannot-be closed or invalid in this context) when this code tries to execute. Can anyone see the problem i have tried re writing the sql to no avail. please help Code: Private Sub lvButtons_H1_Click() Dim MyCon As New ADODB.Connection Dim MyRs As New ADODB.Recordset 3709 Error Code: MyRs.Open "SELECT plans.Number, plans.plan, patients.fname, patients.lname , plans.phone number, plans.card number, plans.vcode, plans.expiry FROM plans LEFT JOIN patients ON plans.payerid = patients.number WHERE plans.plan='" & Me.Combo1.Text & "'" With DataReport9.Sections("Section1").Controls 'section1 mean that section you create in datareport .Item("rptpayid").DataField = MyRs("payerid").Name .Item("rptplan").DataField = MyRs("plan").Name .Item("rptname").DataField = MyRs("fname").Name .Item("rptlname").DataField = MyRs("lname").Name .Item("rptphone").DataField = MyRs("phone number").Name .Item("rptcard").DataField = MyRs("card number").Name .Item("rptvcode").DataField = MyRs("vcode").Name .Item("rptexp").DataField = MyRs("expiry").Name End With Reply With Quote Nov 23rd, 2010,03:48 PM #2 RhinoBull View Profile View Forum Posts PowerPoster Join Date Mar 2004 Location New Amsterdam Posts 24,135 Re: Run-time error '3709' The connection cannot be..... You need to set connection/recordset object and also open connection before opening recordset. Try this instead but KIM that sample code below was not tested: Code: Priva
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 http://stackoverflow.com/questions/11868824/error-3709-when-connecting-thru-adodb-connection-to-sql-server-2008-in-excel this site About Us Learn more about Stack Overflow the company Business http://www.access-programmers.co.uk/forums/showthread.php?t=140284 Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask 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 them; it only takes a minute: Sign up error 3709 Error 3709 when connecting thru ADODB.connection to SQL Server 2008 in Excel up vote 1 down vote favorite I'm trying to open a recordset on a SQL Server in Excel using the below code, but get a 3709 - The connection cannot be used to perform this operation. It is either closed or invalid in this context. error. Where am I going wrong?: time error 3709 EDIT: It is now working, i'll update the code below: 'Declare variables' Set objMyConn = New ADODB.Connection Set objMyRecordset = New ADODB.Recordset Dim strSQL As String 'Open Connection' objMyConn.ConnectionString = "Driver={SQL Server};Server=SERVER\SERVER; Database=we_ci_db; Trusted_Connection=Yes" objMyConn.Open 'Set and Excecute SQL Command' strSQL = "select * from tblUsers" 'Open Recordset' Set objMyRecordset.ActiveConnection = objMyConn objMyRecordset.Open strSQL 'Copy Data to Excel' ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset) sql-server-2008 excel-vba adodb share|improve this question edited Aug 13 '12 at 14:55 asked Aug 8 '12 at 16:18 aSystemOverload 956143149 The string itself wasn't wrong, I've tried several combinations which are completely different, but all work. It was the rst.open line that I didn't have right. I needed to use the other attributes, cnn, adOpenStatic etc... –aSystemOverload Aug 13 '12 at 14:41 add a comment| 2 Answers 2 active oldest votes up vote 1 down vote accepted Remou pointed me in the direction of an alternative Connection String, but I still got the same results. I googled around for a bit and came up with another SO Question LINK with a different implementation, which resulted in: 'Declare variables' Set objMyConn = New ADODB.Connection Set objMyRecordset
Posters Today's Posts Search Community Links Social Groups Pictures & Albums Members List Calendar Search Forums Show Threads Show Posts Tag Search Advanced Search Find All Thanked Posts Go to Page... Thread Tools Rate Thread Display Modes 12-11-2007, 06:12 AM #1 Alisa Registered User Join Date: Jun 2007 Location: Colorado, U.S.A. Posts: 1,931 Thanks: 0 Thanked 7 Times in 5 Posts Runtime error 3709 The search key was not found in any record I am getting this error in my import routine: Code: DoCmd.TransferDatabase acImport, "Paradox 5.x", ImportDir, , oFile.Name, Left(oFile.Name, Len(oFile.Name) - 3) Since it is happening when I am importing, the solutions previously suggested like the Microsoft kb fix won't work - I haven't even imported the table yet so how can I delete an index or delete corrupt records? Does anyone have any ideas about fixing this? My only idea is to rewrite my routine to link instead of import, but I'm not sure that would even help. Alisa View Public Profile Find More Posts by Alisa 01-09-2008, 08:35 AM #2 ctolkamp Registered User Join Date: Jan 2008 Posts: 1 Thanks: 0 Thanked 1 Time in 1 Post I had the same message when importing an Excel spreadsheet. The problem was that one of the field/column names in Excel started with a space. Removing the space fixed the problem. The Following User Says Thank You to ctolkamp For This Useful Post: VBA(06-07-2011) ctolkamp View Public Profile Find More Posts by ctolkamp 06-08-2009, 11:01 AM #3 Johnny Redneck Registered User Join Date: Apr 2009 Posts: 8 Thanks: 0 Thanked 0 Times in 0 Posts Re: Runtime error 3709 The search key was not found in any record A bit late I know... But I just bumped into this error in an Access 2007 application. I'm adding records via a very simple form and have no indexes at all on the table that gives the error seemingly at random when adding new records. Also, there are no memo fields or field names with spaces as have been implicated in other instances of this problem on the net. In my case, I realised when re-examining the form (which I had copied from another) that I had accidentally left the "Filter on load" property set to True with no filter specified. I switched off "Filter on load" and the error went away. Hope this helps Johnny Redneck View Public Profile Find More Posts by Johnny Redneck « Previous Thread | Next Thread » Thread Tools Show Printable Version Email this Page Display Modes Linear Mode Switch to Hybrid Mo