Basic Error 3709
Contents |
Connection Run-time error ‘3709' ★★★★★★★★★★★★★★★ David Musgrave10 April 20132 0 0 0 It has been a long time since I shared a Quick Tip for Visual Basic for Applications (VBA), but I have had error 3709 the search key was not found this one waiting for me to write up for a while. When using VBA
Error 3709 Access
on a window with ActiveX Data Object (ADO) to connect to SQL Server to read and/or write data to tables, the best
Error 3709 Access 2010
practice is to open the ADO connection on the Window_BeforeOpen() event and to close the ADO connectionusing the Window_AfterClose() event. This is similar to the best practice for reports discussed in theUsing ADO with VBA with
Error 3709 The Connection Cannot Be Used
Report Writerpost. There is one big difference in behaviour between windows and reports that can cause problems with this technique…. cue dramatic music….. Windows have a user interface which might cause a dialog to open and the window closure to be aborted. OK, big deal, why should this cause a problem for us? Well, let's work with the following scenario: The user opens the window and the Window_BeforeOpen() event opens the run time error 3709 the connection cannot be used ADO connection. The user is working in the window and the VBA code is reading and writing via ADO as desired. The user is has finished editing data in the window (but has not saved) and closes the window. The "Do you want to Save, Discard or Cancel?" dialog opens and the user decides to cancel. The window closure is aborted and the window stays open. The user continues working and the next time VBA attempts to use ADO, the following error shows up: Run=time error ‘3709': Requested operation requires an OLE DB Session object, which is not supported by the current provider. So what happened? The issue here is that while theDexterity WIN_POST script abortedthe window closure, the script itself as still executed. Therefore the VBA Window_AfterClose() script also executed… and closed the ADO connection even though the window remained open. The solution to this issue is to make sure that the Window_AfterClose() script does not close the ADO connection if Cancel was selected on the pop up dialog. Please see the Knowledge Base (KB) Article below for details of the solution including example scripts: How to use ActiveX Data Object (ADO) with VBA on a window with Microsoft Dynamics GP and with Microsoft Business Solutions - Great Plains 8.0(KB 942327)
? Ask a question, help others, and get answers from the community Discussions Start a thread and discuss today's topics runtime error 3709 with top experts Blogs Read the latest tech blogs written runtime error 3709 in ms access by experienced community members How to fix run-time error 3709 with my computer beginner1 5 pts. runtime error 3709 excel vba Tags: Thanks! We'll email youwhen relevant content isadded and updated. Following Follow Visual Basic 6 Thanks! We'll email youwhen relevant content isadded and updated. Following Follow https://blogs.msdn.microsoft.com/developingfordynamicsgp/2013/04/10/quick-tip-vba-ado-connection-run-time-error-3709/ Visual Basic Error Messages When I try to run my program it says run-time error 3709 the connection cannot be used to perform this action, it is either closed or invalid in this context. mean while I used a module in which i put the following codes : Public cn As ADODB.Connection http://itknowledgeexchange.techtarget.com/itanswers/how-to-fix-run-time-error-3709-with-my-computer/ Public Sub Main() Dim cn As ADODB.Connection Set cn = New ADODB.Connection cn.ConnectionString = "C:UsersLab2c28DocumentsAuthors databaseauthors.mdb" cn.Open Form1.Show End Sub and then I put the following codes in a save button : Private Sub cmdsave_Click() Dim rsauthors As New ADODB.Recordset Set rsauthors = New ADODB.Recordset With rsauthors: ActiveConnection = cn Source = ("SElect * From authors") . LockType = adLockOptimistic Open End With With rsauthors: AddNew Fields("Book ID_") = Text1.Text Fields("Name") = Text2.Text Update End With End Sub Please help meSoftware/Hardware used: VB6 Asked: October 11, 20122:08 PM Last updated: October 24, 201312:21 PM Related Questions Error in Select Statement How to connect Access database to a Visual Studio 2005? Runtime error: server has not yet been opened Crystal reports Runtime error: server has not yet been opened Crystal reports Error when tried to update the SSOledbGrid connected to with Sybase Server Database Answer Wiki Last updated: October 24, 201312:21 PM GMT Ametuer30 p
Tips/Tricks Top Articles Beginner Articles Technical Blogs Posting/Update Guidelines Article Help Forum Article Competition Submit an article or tip Post your Blog quick answersQ&A Ask a Question http://www.codeproject.com/Questions/557798/VB-plusrun-timepluserrorplus-plustheplusconnect View Unanswered Questions View All Questions... C# questions Linux questions ASP.NET questions SQL questions VB.NET questions discussionsforums All Message Boards... Application Lifecycle> Running a Business Sales / Marketing Collaboration / http://www.mrexcel.com/forum/excel-questions/430015-run-time-error-3709-a.html Beta Testing Work Issues Design and Architecture ASP.NET JavaScript C / C++ / MFC> ATL / WTL / STL Managed C++/CLI C# Free Tools Objective-C and Swift Database Hardware & error 3709 Devices> System Admin Hosting and Servers Java .NET Framework Android iOS Mobile SharePoint Silverlight / WPF Visual Basic Web Development Site Bugs / Suggestions Spam and Abuse Watch features Competitions News The Insider Newsletter The Daily Build Newsletter Newsletter archive Surveys Product Showcase Research Library CodeProject Stuff communitylounge Who's Who Most Valuable Professionals The Lounge The Insider News The Weird time error 3709 & The Wonderful The Soapbox Press Releases Non-English Language > General Indian Topics General Chinese Topics help What is 'CodeProject'? General FAQ Ask a Question Bugs and Suggestions Article Help Forum Site Map Advertise with us About our Advertising Employment Opportunities About Us Ask a Question All Questions All Unanswered FAQ VB6 run-time error 3709 the connection cannot be used to perform the operation-it is either closed or invalid Rate this: Please Sign up or sign in to vote. See more: VB6 I always get this error everytime I ran the program. Dim rs As New ADODB.Recordset rs.Open "Select * From tbluser Where uname = '" & txtuser.Text & "'", cnn, adOpenStatic, adLockReadOnly If rs.RecordCount < 1 Then MsgBox "Invalid Username!", vbCritical, "Login Error" txtuser.SetFocus Exit Sub Else If txtpass.Text = rs!pword Then Unload Me Load Form2 Form2.Show Exit Sub Else MsgBox "Invalid Password!", vbCritical, "Login Error" txtpass.SetFocus Exit Sub End If End If Set rs = Nothing MODULE CODE: Option Explicit Public cnn As ADODB.Connection Public Sub getconnected() Set cnn = New ADODB.Connection cnn.CursorLocation = adUseClient
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 Forums category; Code: Sub getdata() cur_mon = Date - (Weekday(Date, 2) - 1) cur_fri = Date + (5 - Weekday(Date, 2)) mondt ... 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 Nov 17th, 2009,04:39 AM #1 hsk Board Regular Join Date Oct 2006 Posts 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 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 Qu