Ms Access Error 3065
here for a quick overview of the site Help Center Detailed answers to any cannot execute a select query pass through questions you might have Meta Discuss the workings and policies expression #1 of order by clause is not in select list of this site About Us Learn more about Stack Overflow the company Business Learn more about openrecordset vba 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 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
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 http://stackoverflow.com/questions/25906567/run-time-error-3065-cannot-execute-a-select-query 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 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 http://www.pcreview.co.uk/threads/3065-cannot-execute-select-query-why-not.1638292/ 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. > > 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 single-user > database. > > > Thanks for any help--this is getting me frustrated. > > John Sanders > PersonalIT at Juno dot Com The error message i
FORUMSFOR COMPUTER PROFESSIONALS Log In Come Join Us! Are you aComputer / IT professional?Join Tek-Tips Forums! Talk With Other Members Be http://www.tek-tips.com/viewthread.cfm?qid=1737835 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 ms access 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 Run-time error '3065' Cannot execute ms access error a select query. thread705-1737835 Forum Search FAQs Links MVPs Run-time error '3065' Cannot execute a select query. Run-time error '3065' Cannot execute a select query. johnve (TechnicalUser) (OP) 18 Sep 14 02:16 Hi, 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_c