Import External Data into Excel

  • Thread starter Thread starter Red
  • Start date Start date
R

Red

Happy New Year to all!

I'm trying to import data from an Access Database into Excel. The data
source in Access is a query which in itself is made up from several other
queries, one of which is a parameters query requesting 2 values: reporting
to and from dates. When I try to import the data I get "Too many parameters:
Expected 2" error. I'd really appreciate if someone could tell me a way
round this either in Access or Excel, or direct me to documentation that'll
help.

Thanks in advance.
 
You need to tell us how you're doing the import (post code or something),
and what is the SQL of the query.

My guess is that the query contains parameters that are not being replaced
by the actual values, and that the process you're using to do the import
isn't evaluating the parameters prior to doing the import.
 
Hi Ken,

The import is using Get External Data via MSQuery. I suspect you're right
about the import not evaluating the parameters prior to doing the import.
The query that requests the parameters isn't the query I'm importing. The
sequence in Access is a line of 5 queries each dependent on one another. The
parameter query is No 2 and the imported query is No 5 down the line. I've
sent the code for all 5 in Access plus the SQL from MSQuery and hope this
helps.

Many thanks.


Query 1
SELECT MSP_ASSIGNMENTS_QUERY.JobNo, MSP_ASSIGNMENTS_QUERY.VProjDesc,
MSP_ASSIGNMENTS_QUERY.Date, Sum((([Rate]/7.5)*[RemHours])) AS RemCost
FROM RTR_Rates_Query INNER JOIN MSP_ASSIGNMENTS_QUERY ON
RTR_Rates_Query.ResID = MSP_ASSIGNMENTS_QUERY.Resource
GROUP BY MSP_ASSIGNMENTS_QUERY.JobNo, MSP_ASSIGNMENTS_QUERY.VProjDesc,
MSP_ASSIGNMENTS_QUERY.Date, RTR_Rates_Query.Start, RTR_Rates_Query.End
HAVING (((MSP_ASSIGNMENTS_QUERY.VProjDesc) Is Not Null) AND
((MSP_ASSIGNMENTS_QUERY.Date) Between [RTR_Rates_Query]![Start] And
[RTR_Rates_Query]![End]+1) AND ((Sum((([Rate]/7.5)*[RemHours])))<>0))
ORDER BY MSP_ASSIGNMENTS_QUERY.Date;

Query2
SELECT MSP_RemCost_Calc1.JobNo, MSP_RemCost_Calc1.VProjDesc,
MSP_RemCost_Calc1.Date, MSP_RemCost_Calc1.RemCost AS RemCost
FROM MSP_RemCost_Calc1
WHERE (((MSP_RemCost_Calc1.Date) Between [Report FROM Date:] And [Report TO
Date:]))
ORDER BY MSP_RemCost_Calc1.JobNo;

Query 3
SELECT MSP_RemCost_Calc2.JobNo, MSP_RemCost_Calc2.VProjDesc,
Sum(MSP_RemCost_Calc2.RemCost) AS RemCost
FROM MSP_RemCost_Calc2
GROUP BY MSP_RemCost_Calc2.JobNo, MSP_RemCost_Calc2.VProjDesc
ORDER BY MSP_RemCost_Calc2.JobNo;

Query 4
SELECT MSP_Contribution_Query.JobNo, MSP_RemCost_Calc3.RemCost,
MSP_Contribution_Query.Percent_Complete, MSP_Contribution_Query.Target_Cont,
MSP_Contribution_Query.[TargCont%], MSP_Contribution_Query.TargContTD,
MSP_Contribution_Query.Cont_TD, MSP_Contribution_Query.[ContTD%]
FROM MSP_RemCost_Calc3 RIGHT JOIN MSP_Contribution_Query ON
MSP_RemCost_Calc3.JobNo = MSP_Contribution_Query.JobNo
ORDER BY MSP_Contribution_Query.JobNo;

Query 5
SELECT RTR_Project_Summary.VProjDesc, MSP_RemCost_Calc4.JobNo,
RTR_Project_Summary.ContractFee, MSP_RemCost_Calc4.RemCost,
RTR_Project_Summary.CostTD,
IIf([NRExpsTD]>[TargNRExp],[NRExpsTD],[TargNRExp]) AS NRExpsCalc,
[RemCost]+[CostTD] AS EAC, MSP_RemCost_Calc4.Percent_Complete,
MSP_RemCost_Calc4.Target_Cont, MSP_RemCost_Calc4.[TargCont%],
MSP_RemCost_Calc4.Cont_TD, MSP_RemCost_Calc4.[ContTD%],
RTR_Project_Summary.FeesEarned, RTR_Project_Summary.FeeInvoiced,
[ContractFee]-[EAC]-[NRExpsCalc] AS FwdCont£, [FwdCont£]/[ContractFee] AS
[FwdCont%], RTR_Project_Summary.PD, RTR_Project_Summary.PL,
RTR_Project_Summary.DL, RTR_Project_Summary.CL, RTR_Project_Summary.ITL,
RTR_Project_Summary.TDL
FROM MSP_RemCost_Calc4 LEFT JOIN RTR_Project_Summary ON
MSP_RemCost_Calc4.JobNo = RTR_Project_Summary.JobNo
ORDER BY MSP_RemCost_Calc4.JobNo;

Import Query
SELECT MSP_Contribution_Forecast_TimePhased.VProjDesc,
MSP_Contribution_Forecast_TimePhased.JobNo,
MSP_Contribution_Forecast_TimePhased.ContractFee,
MSP_Contribution_Forecast_TimePhased.RemCost,
MSP_Contribution_Forecast_TimePhased.CostTD,
MSP_Contribution_Forecast_TimePhased.NRExpsCalc,
MSP_Contribution_Forecast_TimePhased.EAC,
MSP_Contribution_Forecast_TimePhased.Percent_Complete,
MSP_Contribution_Forecast_TimePhased.Target_Cont,
MSP_Contribution_Forecast_TimePhased.`TargCont%`,
MSP_Contribution_Forecast_TimePhased.Cont_TD,
MSP_Contribution_Forecast_TimePhased.`ContTD%`,
MSP_Contribution_Forecast_TimePhased.FeesEarned,
MSP_Contribution_Forecast_TimePhased.FeeInvoiced,
MSP_Contribution_Forecast_TimePhased.`FwdCont£`,
MSP_Contribution_Forecast_TimePhased.`FwdCont%`,
MSP_Contribution_Forecast_TimePhased.PD,
MSP_Contribution_Forecast_TimePhased.PL,
MSP_Contribution_Forecast_TimePhased.DL,
MSP_Contribution_Forecast_TimePhased.CL,
MSP_Contribution_Forecast_TimePhased.ITL,
MSP_Contribution_Forecast_TimePhased.TDL
FROM `B:\Reports\SAM_Sync\TEST - DO NOT
DELETE`.MSP_Contribution_Forecast_TimePhased
MSP_Contribution_Forecast_TimePhased
 
Based on your description, I'm guessing that the first four queries are
being used by the fifth (imported) query, but I can't tell that because you
didn't give the names of the first four queries.

When you "call" the import query, it runs all the other queries, so, yes, if
the second query in your listing is calling for parameters, then the import
query process also will look for those same parameters. The method that
you're using to do this won't work unless that query substitutes actual
values for those parameters. This substitution can be done by opening a
DAO.QueryDef object in VBA in ACCESS or in EXCEL (the DAO library must be
selected as a reference) and then evaluating the parameters before you
execute the query, but I'm not aware of a way to do it via the method that
you're using in EXCEL (that doesn't mean there isn't a way, just that I
don't know of one).

You may need to take a different approach to how you get your data into
EXCEL. You might "google" the EXCEL newsgroups to see if there is an
approach posted for this by others who've needed it for EXCEL.
--
Ken Snell
<MS ACCESS MVP>


Red said:
Hi Ken,

The import is using Get External Data via MSQuery. I suspect you're right
about the import not evaluating the parameters prior to doing the import.
The query that requests the parameters isn't the query I'm importing. The
sequence in Access is a line of 5 queries each dependent on one another. The
parameter query is No 2 and the imported query is No 5 down the line. I've
sent the code for all 5 in Access plus the SQL from MSQuery and hope this
helps.

Many thanks.


Query 1
SELECT MSP_ASSIGNMENTS_QUERY.JobNo, MSP_ASSIGNMENTS_QUERY.VProjDesc,
MSP_ASSIGNMENTS_QUERY.Date, Sum((([Rate]/7.5)*[RemHours])) AS RemCost
FROM RTR_Rates_Query INNER JOIN MSP_ASSIGNMENTS_QUERY ON
RTR_Rates_Query.ResID = MSP_ASSIGNMENTS_QUERY.Resource
GROUP BY MSP_ASSIGNMENTS_QUERY.JobNo, MSP_ASSIGNMENTS_QUERY.VProjDesc,
MSP_ASSIGNMENTS_QUERY.Date, RTR_Rates_Query.Start, RTR_Rates_Query.End
HAVING (((MSP_ASSIGNMENTS_QUERY.VProjDesc) Is Not Null) AND
((MSP_ASSIGNMENTS_QUERY.Date) Between [RTR_Rates_Query]![Start] And
[RTR_Rates_Query]![End]+1) AND ((Sum((([Rate]/7.5)*[RemHours])))<>0))
ORDER BY MSP_ASSIGNMENTS_QUERY.Date;

Query2
SELECT MSP_RemCost_Calc1.JobNo, MSP_RemCost_Calc1.VProjDesc,
MSP_RemCost_Calc1.Date, MSP_RemCost_Calc1.RemCost AS RemCost
FROM MSP_RemCost_Calc1
WHERE (((MSP_RemCost_Calc1.Date) Between [Report FROM Date:] And [Report TO
Date:]))
ORDER BY MSP_RemCost_Calc1.JobNo;

Query 3
SELECT MSP_RemCost_Calc2.JobNo, MSP_RemCost_Calc2.VProjDesc,
Sum(MSP_RemCost_Calc2.RemCost) AS RemCost
FROM MSP_RemCost_Calc2
GROUP BY MSP_RemCost_Calc2.JobNo, MSP_RemCost_Calc2.VProjDesc
ORDER BY MSP_RemCost_Calc2.JobNo;

Query 4
SELECT MSP_Contribution_Query.JobNo, MSP_RemCost_Calc3.RemCost,
MSP_Contribution_Query.Percent_Complete, MSP_Contribution_Query.Target_Cont,
MSP_Contribution_Query.[TargCont%], MSP_Contribution_Query.TargContTD,
MSP_Contribution_Query.Cont_TD, MSP_Contribution_Query.[ContTD%]
FROM MSP_RemCost_Calc3 RIGHT JOIN MSP_Contribution_Query ON
MSP_RemCost_Calc3.JobNo = MSP_Contribution_Query.JobNo
ORDER BY MSP_Contribution_Query.JobNo;

Query 5
SELECT RTR_Project_Summary.VProjDesc, MSP_RemCost_Calc4.JobNo,
RTR_Project_Summary.ContractFee, MSP_RemCost_Calc4.RemCost,
RTR_Project_Summary.CostTD,
IIf([NRExpsTD]>[TargNRExp],[NRExpsTD],[TargNRExp]) AS NRExpsCalc,
[RemCost]+[CostTD] AS EAC, MSP_RemCost_Calc4.Percent_Complete,
MSP_RemCost_Calc4.Target_Cont, MSP_RemCost_Calc4.[TargCont%],
MSP_RemCost_Calc4.Cont_TD, MSP_RemCost_Calc4.[ContTD%],
RTR_Project_Summary.FeesEarned, RTR_Project_Summary.FeeInvoiced,
[ContractFee]-[EAC]-[NRExpsCalc] AS FwdCont£, [FwdCont£]/[ContractFee] AS
[FwdCont%], RTR_Project_Summary.PD, RTR_Project_Summary.PL,
RTR_Project_Summary.DL, RTR_Project_Summary.CL, RTR_Project_Summary.ITL,
RTR_Project_Summary.TDL
FROM MSP_RemCost_Calc4 LEFT JOIN RTR_Project_Summary ON
MSP_RemCost_Calc4.JobNo = RTR_Project_Summary.JobNo
ORDER BY MSP_RemCost_Calc4.JobNo;

Import Query
SELECT MSP_Contribution_Forecast_TimePhased.VProjDesc,
MSP_Contribution_Forecast_TimePhased.JobNo,
MSP_Contribution_Forecast_TimePhased.ContractFee,
MSP_Contribution_Forecast_TimePhased.RemCost,
MSP_Contribution_Forecast_TimePhased.CostTD,
MSP_Contribution_Forecast_TimePhased.NRExpsCalc,
MSP_Contribution_Forecast_TimePhased.EAC,
MSP_Contribution_Forecast_TimePhased.Percent_Complete,
MSP_Contribution_Forecast_TimePhased.Target_Cont,
MSP_Contribution_Forecast_TimePhased.`TargCont%`,
MSP_Contribution_Forecast_TimePhased.Cont_TD,
MSP_Contribution_Forecast_TimePhased.`ContTD%`,
MSP_Contribution_Forecast_TimePhased.FeesEarned,
MSP_Contribution_Forecast_TimePhased.FeeInvoiced,
MSP_Contribution_Forecast_TimePhased.`FwdCont£`,
MSP_Contribution_Forecast_TimePhased.`FwdCont%`,
MSP_Contribution_Forecast_TimePhased.PD,
MSP_Contribution_Forecast_TimePhased.PL,
MSP_Contribution_Forecast_TimePhased.DL,
MSP_Contribution_Forecast_TimePhased.CL,
MSP_Contribution_Forecast_TimePhased.ITL,
MSP_Contribution_Forecast_TimePhased.TDL
FROM `B:\Reports\SAM_Sync\TEST - DO NOT
DELETE`.MSP_Contribution_Forecast_TimePhased
MSP_Contribution_Forecast_TimePhased
Click to expand...
 
Back
Top