Ms Access Runtime Error 3065
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 About Us Learn cannot execute a select query pass through more about Stack Overflow the company Business Learn more about hiring developers or
Expression #1 Of Order By Clause Is Not In Select List
posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community
Openrecordset Vba
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 Run-time error '3065' Cannot execute a select query up vote 1 down vote favorite 1 I have written lots of the queries but I'm struggling with this one. I get the run-time error 3065 when I run the following sql. Dim db As DAO.Database Dim sqlstring As String Set db = DBEngine(0).Databases(0) sqlstring = "SELECT ebk.hr_leav_amnt AS hr_clia_hour, ebk.hr_leav_type, ebk.hr_leav_code, ebk.hr_empl_code, ebk.hr_loadg_amt AS hr_loadg_amt, 'Leave Pay' AS hr_provision, mst.hr_paym_code, mst.hr_base_hour, '' AS hr_splt_accr, mst.hr_leav_abbr, ype.hr_norm_pcnt, ype.hr_allw_amnt" sqlstring = sqlstring + " FROM hrtlvebk AS ebk, hrtlvmst AS mst, hrtptype AS ype" sqlstring = sqlstring + " WHERE ebk.hr_leav_code Like 'a%' And ebk.hr_leav_code = [mst].[hr_leav_code] And ebk.hr_leav_type Like '1%' And bk.hr_leav_type = [mst].[hr_leav_type] And ebk.hr_recd_type = 'a' And ebk.hr_lbkg_refn = 'ACCRUAL' And ebk.hr_from_dati >= 20140701 And ebk.hr_from_dati <= 20140730 And mst.hr_load_rule <> 'y' And mst.hr_paym_code = [ype].[hr_paym_code]" sqlstring = sqlstring + " GROUP BY ebk.hr_leav_amnt, ebk.hr_leav_type, ebk.hr_leav_code, ebk.hr_empl_code, ebk.hr_loadg_amt, mst.hr_paym_code, mst.hr_base_hour, mst.hr_leav_abbr, ype.hr_norm_pcnt, ype.hr_allw_amnt" db.Execute sqlstring, dbFailOnError When I run statement with Query (SQL) it works fine. The only thing I change is the text in the where clause.. ('a%' - Query it is "a%") Thank you in advance. John vba ms-access access-vba share|improve this question asked Sep 18 '14 at 7:21 user3029925 1313 add a comment| 1 Answer 1 active oldest votes up vote 2 down vote The message is true for SELECT queries you should use Openrecordset to be able to retrieve results of selection. Execute is for 'command' queries that don't return values. share|improve this answer answered Sep 18 '14 at 7:47 4dmonster 1,8971512 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Face
John Sanders Guest OK, I'm probably missing something basic here. I get a run-time error 3065, cannot execute a select query. The code seems to compile fine: .... Dim testqry As DAO.QueryDef Dim TestDB As DAO.Database Dim SQLString As String Stop SQLString = "SELECT * FROM " & Me.Recordset.Name & " WHERE (" & Me.Recordset.Name & _ ".[employee name] = """ SQLString = SQLString & "Jester" ' This will be replaced with a variable when I get it to execute SQLString = SQLString & """);" Debug.Print SQLString Set TestDB = CurrentDb Set testqry = TestDB.CreateQueryDef("", SQLString) testqry.Execute ... The generated SQL string http://stackoverflow.com/questions/25906567/run-time-error-3065-cannot-execute-a-select-query is SELECT * FROM tbl_Employees WHERE (tbl_Employees.[employee name] = "Jester"); When I save that string as a query outside the form, it will execute fine. Inside the form, it crashes on the testqry.execute line with the run-time error. I have as references: Visual Basic For Applications Microsoft Access 9.0 Object Library OLE Automation Microsoft DAO 3.6 Object Library I am running Access 2000 on a local machine with a local http://www.office-forums.com/threads/3065-cannot-execute-select-query-why-not.650724/ single-user database. Thanks for any help--this is getting me frustrated. John Sanders PersonalIT at Juno dot Com John Sanders, May 7, 2004 #1 Advertisements Dirk Goldgar Guest "John Sanders" <> wrote in message news:c7gjn4$7kv$ > OK, I'm probably missing something basic here. > > I get a run-time error 3065, cannot execute a select query. > > The code seems to compile fine: > > > ... > Dim testqry As DAO.QueryDef > Dim TestDB As DAO.Database > Dim SQLString As String > > Stop > SQLString = "SELECT * FROM " & Me.Recordset.Name & " WHERE (" & > Me.Recordset.Name & _ > ".[employee name] = """ > SQLString = SQLString & "Jester" > ' This will be replaced with a variable when I get it to execute > SQLString = SQLString & """);" > Debug.Print SQLString > Set TestDB = CurrentDb > Set testqry = TestDB.CreateQueryDef("", SQLString) > testqry.Execute > ... > > > The generated SQL string is > SELECT * FROM tbl_Employees WHERE (tbl_Employees.[employee name] = > "Jester"); > > > When I save that string as a query outside the form, it will execute > fine. Inside the form, it crashes on the testqry.execute line with > the run-time error. > >
for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask https://www.experts-exchange.com/questions/28330692/Error-3065-Cant-execute-a-Select-Query.html a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > Error 3065 ms access - Cant execute a Select Query Want to Advertise Here? Solved Error 3065 - Cant execute a Select Query Posted on 2014-01-04 MS Access 1 Verified Solution 9 Comments 578 Views Last Modified: 2014-01-05 Experts, What is Error 3065? Not sure what is setting off the error if it Appends ms access runtime correctly. Thank you Private Sub cmdNewProject_Click() On Error GoTo EH Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("select * from Projects where 1=0") With rs .AddNew ![Created By] = Nz([TempVars]![CurrentUserID], 0) ![Status2] = 7 ![Currency] = DLookup("CurrencyID", "tblCurrencyExchange", "[Currency] = '" & Me.txtCur & "'") ![ProjectNo] = Me.txtActivityCode ![Project Name] = "LC Ref: (delete it after appending if want): " & Me.txtLCRef & ", Project Name: " & Me.txtActivityDescription ![Start Date] = Date ![Notes] = "****APPENDED LC FROM CSM*****: " & Format(Date, "m\/d\/yyyy") & ", Beneficiary :" & Me.txtBeneficiary & ", Guarantor: " & Me.txtGtorDescrip .Update End With rs.Close CurrentDb.Execute "qryUpdateProjID_tblLC", dbFailOnError CurrentDb.Execute "qryUpdateProjID_CSM2", dbFailOnError DoCmd.OpenForm "frmUpdateBU" Me.Refresh Exit Sub EH: 'Add error handling here MsgBox "Error " & Err.Number