Ms Access Runtime Error 7965
soon) Ruby (coming soon) Getting Started Code Samples Resources Patterns and Practices App Registration Tool Events Podcasts Training API Sandbox Videos Documentation Office Add-ins Office Add-in Availability Office Add-ins Changelog Microsoft Graph API Office 365 Connectors Office 365 REST APIs SharePoint Add-ins Office UI Fabric Submit to the Office Store All Documentation https://www.yammer.com/ http://feeds.feedburner.com/office/fmNx Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: Binding a Form to an ADODB Command Microsoft Office for Developers > Access for Developers Question 0 Sign in to vote I am trying to bind an Access 2010 form to a SQL Server stored procedure using an ADODB Command. When I try to assign the command to the form recordset, I get "Error 91 - Object Variable or With Block not set". Please can anyone suggest what I need to do to make this work? The code is below: Dim cn As ADODB.Connection Dim cmd As ADODB.Command Dim prm As ADODB.Parameter Set cn = New ADODB.Connection With cn .ConnectionString = "DSN=KitchenDB" .Open End With Set cmd = New ADODB.Command 'Set cmd.ActiveConnection = cn With cmd .ActiveConnection = cn .CommandType = adCmdStoredProc .CommandText = "sp_BatchIntermediateProducts " End With Set prm = cmd.CreateParameter("BatchNo", adInteger, adParamInput, , 1) cmd.Parameters.Append prm cmd.Execute Me.Recordset = cmd '*** Error occurs here.... Set cmd = Nothing Set cn = Nothing PG A bit of experimentation by trial and error often helps. Tuesday, December 27, 2011 10:29 PM Reply | Quote Answers 0 Sign in to vote Thanks for the suggestion, it did not work. At this line: Set Me.Recordset = cmd.Execute() I get Error 7965 - Object is not a valid record set property. I have got something similar to work using a DAO recordset, but even then onlythe first records returned by the stored proc shows on the form when 10 should be returned. You need to ensur
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 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 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Bind Access form to the results from a Stored Procedure up vote 6 down vote favorite 2 https://social.msdn.microsoft.com/Forums/office/en-US/14fcd6cf-5053-4805-8720-f1a7bff3ccdf/binding-a-form-to-an-adodb-command?forum=accessdev I am trying to return the results of a stored procedure to a form. I have managed to iterate thru the results using an ADO recordset, but cannot bind the results to the form.. Here is the VBA code: Private Sub RetrieveSiteInformation() Dim cmd As New ADODB.Command Dim cnn As New ADODB.Connection Dim rs As ADODB.Recordset, f As ADODB.Field With cnn .Provider = "SQLOLEDB" .ConnectionString = "data source=UKFCSVR;initial catalog=ACACB;Trusted_Connection=Yes" .Open End With http://stackoverflow.com/questions/4784820/bind-access-form-to-the-results-from-a-stored-procedure Dim param1 As ADODB.Parameter If Nz(txtSiteID_Search.Value, vbNullString) <> vbNullString Then Set param1 = cmd.CreateParameter("@SiteID", adBigInt, adParamInput) param1.Value = txtSiteID_Search.Value cmd.Parameters.Append param1 End If With cmd .ActiveConnection = cnn .CommandText = "spSiteInformation_Retrieve" .CommandType = adCmdStoredProc **' THIS FAILS** Me.Recordset = .Execute **' THIS LOOP WORKS FINE** ' Set rs = .Execute ' rs.MoveFirst ' For Each f In rs.Fields ' Debug.Print f.Name ' Next ' With rs ' Do While Not .EOF ' Debug.Print ![CompanyName] & " " & ![Postcode] ' .MoveNext ' Loop ' End With End With cnn.Close End Sub sql-server ms-access stored-procedures share|improve this question edited Sep 3 '13 at 9:45 SteveC 4,042135198 asked Jan 24 '11 at 17:09 winshent 4315 add a comment| 3 Answers 3 active oldest votes up vote 3 down vote accepted Okay, I have tested this example. It includes changes to suit my set-up which I have left in, rather than guessing at your set-up. Most of this is taken from http://support.microsoft.com/kb/281998/EN-US/ Dim cn As New ADODB.Connection Dim cmd As New ADODB.Command Dim param1 As New ADODB.Parameter With cn .Provider = "Microsoft.Access.OLEDB.10.0" .Properties("Data Provider").Value = "SQLOLEDB" .Properties("Data Source").Value = "Server" .Properties("Integrated Security").Value = "SSPI" .Properties("Initial Catalog").Value = "Test" .Open End With txtSiteID_Search = 1 If Nz(txtSiteID_Search, vbNullString) <> vbNullString Then Set param1 = cmd.CreateParameter("@SiteID", adBigInt, adParamInput)
Custom Search UtterAccess Forums>Microsoft® Access>Access Modules Retrieving Recordset into http://www.utteraccess.com/forum/index.php?showtopic=1595051 form from ADODB Connection Forum HomeSearchHelpUA http://www.tek-tips.com/viewthread.cfm?qid=1751433 Messages|-- UtterAccess.com NewsAccess Knowledge Center|-- Access Code Archive|-- Access Knowledgebase / FAQ|-- Access TutorialsMicrosoft® Access|-- Local Access User Groups (AUGs)|-- Interface Design|-- Access Q and A|-- Access Tables + Relationships|-- Access Queries|-- ms access Access Forms|-- Access Reports|-- Access Macros|-- Access Modules|-- Access Date + Time|-- Access Errors + Error Handling|-- Access Built-in Functions|-- Access Searching + Data Mining|-- Access Records|-- Access Security|-- Access Automation|-- Access Database Container|-- Tool bars, Menu bars + Ribbon|-- ms access runtime Access Services (Web Databases)|-- Access Data Access Pages (Deprecated)|-- Access Data Projects - ADP|-- Access Runtime, Packaging & DeploymentAnd More...|-- Microsoft SQL Server|-- SQL Server Reporting Services|-- Microsoft SharePoint|-- Microsoft Internet Explorer|-- Visual Basic 6 and Earlier|-- Visual Basic 2003 and Later|-- ASP and VBScript|-- Web Development|-- PHP, Perl, MySQL and Postgres|-- Other Database Engines|-- BI Tools and Applications|-- JAVA and C#|-- GraphicsMicrosoft® Office|-- Microsoft Excel|-- Microsoft Excel FAQs|-- Microsoft Word|-- Microsoft Outlook|-- Microsoft PowerPoint|-- Microsoft FrontPage/Exp. Web|-- Other Microsoft ProductsPC|-- Network Issues|-- Q & A - Hardware|-- Q & A - Software|-- Q & A - Other PC|-- Virus + Security DiscussionUtterAccess Odds and Ends|-- General Chat|-- Q & A - UtterAccess.com Forums|-- Q & A - Acces
FORUMSFOR COMPUTER PROFESSIONALS Log In Come Join Us! Are you aComputer / IT professional?Join Tek-Tips Forums! Talk With Other Members Be Notified Of ResponsesTo Your Posts Keyword Search One-Click Access To YourFavorite Forums Automated SignaturesOn Your Posts Best Of All, It's Free! Join Us! *Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting Guidelines Promoting, selling, recruiting, coursework and thesis posting is forbidden.Tek-Tips Posting Policies Jobs Jobs from Indeed What: Where: jobs by Link To This Forum! Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.Just copy and paste the BBCode HTML Markdown MediaWiki reStructuredText code below into your site. Microsoft: Access Modules (VBA Coding) Forum at Tek-Tips HomeForumsProgrammersDBMS PackagesMicrosoft: Access Modules (VBA Coding) Forum set access form recordset to ADO recordset thread705-1751433 Forum Search FAQs Links MVPs set access form recordset to ADO recordset set access form recordset to ADO recordset clapper62 (Programmer) (OP) 23 Jun 15 15:25 I'm trying to set an Access Forms recordset to an ADO recordset containing a list of tables in an Access .mdb database with the following code CODE --> Set rst = objConnection.OpenSchema(adSchemaTables) rst.MoveLast rst.MoveFirst Debug.Print rst!TABLE_NAME Set Forms!frmData.Recordset = rst rst.Close objConnection.Close I get a Runtime Error 7965 / the object you entered is not a valid recordset property at line Set Forms!frmData.Recordset = rst I know the recordset was created ok because the debug.print statement returns a table name from the database also I have successfully used this same code when I return a list of tables from a MS SQL Server database RE: se