How to export SQL statement results to an Excel File -

i have access database , form in excel vba. data input db input through vba form.

this db contains benefits cards received year in company. same employee can ask card twice or more, we'll have more 1 record on db him.

what need when number of records greater one, sql statement result should appear in excel report.

i use select (*) count statement know when there more 1 record compatible search criterion. can't make result appear in excel file.

here code:

public function relatorio()     dim sql string     dim cn  adodb.connection     dim rs  adodb.recordset     dim rel string      set cn = new adodb.connection     cn.connectionstring = "provider=microsoft.ace.oledb.12.0;data source=" & enderecodb & ";jet oledb:database"     cn.open     set rs = new adodb.recordset     sql = "insert openrowset('microsoft.ace.oledb.12.0', 'excel 12.0;database=" & enderecodb & ";', 'select * [planilha1$]') select * controle bp = " & controlectform.nmbpbox.value & ";"     rs.open sql, cn end function 

when run code gives me message saying like:

can't locate openrowset table exit

i'm not able install new programs, need using excel vba , access db.

how can make work?

i don't believe access supports openrowset, dynamic table you're working there. have lot of old projects though, here's method

public function relatorio()      dim sql string     dim cn  adodb.connection     dim rs  adodb.recordset     dim rel string       set cn = new adodb.connection      cn.connectionstring = "provider=microsoft.ace.oledb.12.0;data source=" & enderecodb & ";jet oledb:database"      cn.open      set rs = new adodb.recordset      dim path_to_xlsx     dim name_of_sheet     path_to_xlsx = "c:\temp\output.xlsx"     name_of_sheet = "planilha1"     sql = sql = "select * [excel 12.0;database=" & path_to_xlsx & "]." & name_of_sheet & " controle bp = '" & controlectform.nmbpbox.value & "';"       rs.open sql, cn      'if application in unsecure environment, use following code instead!  prevent sql injection, security concern here.     'as access database, overkill project     'create command object.     set cmd1 = new adodb.command     cmd1.activeconnection = cn     cmd1.commandtext = "select * controle [excel 12.0;database=" & path_to_xlsx & "]." & name_of_sheet & " bp = ?"     ' create parameter object.     set param1 = cmd1.createparameter(, adinteger, adparaminput, 5) 'use advarchar strings(versus adinteger), https://www.w3schools.com/asp/met_comm_createparameter.asp     param1.value = controlectform.nmbpbox.value     cmd1.parameters.append param1     set param1 = nothing     set rs = cmd1.execute()  end function