Sending Access Query Results to Excel

[skyline16]

I have a query (qry_count_buildingcode). It returns the number of work orders for each building. This query gets its fields from another query. Although 'qry_count_buildingcode' returns two simple columns of data ('Count of work orders' and 'Building Name'), I cannot make Access automatically export this data to Excel.

CountOfWO_NUMBER FU_BLDGCODE_DUP
46 FMTSC
14 ADMFMTS
10 RECY
4 MTRPL
2 RANSH
2 PAISH
1 POLEB
1 OGG
1 HTGPLT
1 GRDNC
1


When I try the VBA code on a set of data derived from only one table, it works fine. When running the code on qry_count_buildingcode, a break occurs at the following line

' Create recordset
Set rst = New ADODB.Recordset
rst.Open _
Source:=conQuery, _
ActiveConnection:=CurrentProject.Connection

I was wondering if there is a way to make Access just take the query results and simply copy them into Excel.

I hope I was clear.

 

[PHV]

Is conQuery a string containing valid SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[skyline16]

Private Const conQuery = "qry_count_buildingcode"

The error message says:
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDUERE', 'SELECT' OR 'INSERT'

 

[PHV]

Replace qry_count_buildingcode with the proper SQL code

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[skyline16]

Do I copy the SQL code of qry_count_buildingcode and paste it into the module?

 

[lespaul]

yep:

Private Const conQuery = "Put SQL Select Statement HERE"

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[skyline16]

Private Const conQuery = "SELECT Count(qry_planning_report.WO_NUMBER)AS CountOfWO_NUMBER, qry_planning_report.FU_BLDGCODE_DUP FROM qry_planning_report GROUP BY qry_planning_report.FU_BLDGCODE_DUP ORDER BY Count(qry_planning_report.WO_NUMBER) DESC;"


Now the error message says:
"No value given for one or more required parameters"

And the following lines are still highlighted during Debug:
rst.Open _
Source:=conQuery, _
ActiveConnection:=CurrentProject.Connection

 

[lespaul]

what's the SQL of qry_planning_report?

 

[skyline16]

It's really long.

SELECT [OMNIS_f_WorkOrder].[WO_NUMBER], [OMNIS_f_WorkOrder].[WO_ACT_REQ], [OMNIS_f_WorkOrder].[WO_REQUEST_DATE], [OMNIS_f_WorkOrder].[WO_TYPE_CODE], [OMNIS_f_WorkOrder].[WO_STATUS], [OMNIS_f_WorkOrder].[WO_CLOSE_DATE], [tbl_PRIORITY].[New_Priority], [OMNIS_f_Job_Library].[FO_JOB_CODE], [OMNIS_f_Job_Library].[FO_JOB_DESCRIP], [tbl_PRIORITY].[Time], [OMNIS_f_Trades].[FTR_DESCRIPTION], [OMNIS_f_Areas].[FU_UNIT_ID], [OMNIS_f_Areas].[FU_BLDGCODE_DUP], [tbl_TRADE].[TRADE]
FROM tbl_TRADE INNER JOIN ((tbl_CREW INNER JOIN (OMNIS_f_WO_Trades INNER JOIN (((OMNIS_f_Trades INNER JOIN OMNIS_f_WorkOrder ON [OMNIS_f_Trades].[FTR_PK]=[OMNIS_f_WorkOrder].[WO_FTR_FK]) INNER JOIN tbl_PRIORITY ON [OMNIS_f_WorkOrder].[WO_PRIORITY]=[tbl_PRIORITY].[Old_Priority]) INNER JOIN OMNIS_f_Job_Library ON [OMNIS_f_WorkOrder].[WO_FO_FK]=[OMNIS_f_Job_Library].[FO_PK]) ON ([OMNIS_f_WO_Trades].[WOT_FTR_FK]=[OMNIS_f_Trades].[FTR_PK]) AND ([OMNIS_f_WO_Trades].[WOT_WO_FK]=[OMNIS_f_WorkOrder].[WO_PK])) ON [tbl_CREW].[Crew/trade]=[OMNIS_f_Trades].[FTR_DESCRIPTION]) LEFT JOIN OMNIS_f_Areas ON [OMNIS_f_WorkOrder].[WO_FU_FK]=[OMNIS_f_Areas].[FU_PK]) ON [tbl_TRADE].[TRADE]=[tbl_CREW].[TRADE]
WHERE ((([OMNIS_f_WorkOrder].[WO_TYPE_CODE])<>"PM") And (([OMNIS_f_WorkOrder].[WO_STATUS])<>"DEFERRED" And ([OMNIS_f_WorkOrder].[WO_STATUS])<>"VOID") And (([OMNIS_f_WorkOrder].[WO_CLOSE_DATE])<=#11/30/1899#) And (([tbl_PRIORITY].[New_Priority])<>5) And (([tbl_TRADE].[TRADE])=[Forms]![frm_select_trade]![cboTrade]))
ORDER BY [tbl_PRIORITY].[New_Priority];

Also, unless I delete the CreateExcelSpreadsheet Module, neither of the queries work.

 

[PHV]

(([tbl_TRADE].[TRADE])=[!][Forms]![frm_select_trade]![cboTrade][/!]))

You have parameter in the recordset.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[skyline16]

Can you not have a parameter in the recordset? I have the form open and the 'Trade' selected in the combo box (cboTrade). Both queries work properly as long as the frm_select_trade is open and a trade has been selected.

 

[PHV]

You have to play with the Parameters collection of an ADODB.Command object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JA3395]

Why don't you use a set of macro instructions. Change your query to create a temporary table and then create a macro to perform the 2 steps:-

OpenQuery -> your query
TransferSpreadsheet -> temporary table to excel

I find that trying to make Access do multiple things in one step just causes headaches...

J.