Run-time error '3065' Cannot execute a select query

Question

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

Answer 1

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.