MS Query (Excel 2002 (xp) SP2)

Topic

New Reply

Is it possible to import data from a parameter query in a Access database using MS Query. I keep getting an error stating that “Too few parameters. N expected. How do I pass them on from MSQ or Excel?? The Access query gets its parameters from a form. I’ve tried leaving that Access form open and populated during the import to Excel, but no go. Thanks for any ideas you might have.

Randy

Reply | Quote

Replies

MS Query isn’t aware of the existence of Access forms. Is it possible for you to export from Access to Excel instead?

Reply | Quote

Hans–

Thanks for the suggestion. I looked into transferring the recordset to Excel through TransferDatabase method, but that will create a new spreadsheet file each time. I have several charts already formated that I just need to update data to. If need be, I could create a chart in Excel using automation from Access, but that is very advanced for my skills and I’m not sure I’d be able to get it done. Is there code already written for this sort of automation that I could use as a template? Again, thanks for your help.

Reply | Quote

If you use TransferSpreadsheet to export a query to an existing workbook, it will export the query to a worksheet with the same name as the query within that workbook; if the worksheet already exists it will be overwritten.

Alternatively, you could use ralphad’s suggestion: prompt the user for the values in Excel, construct a SQL statement using the values provided by the user, then open a recordset (ADO or DAO) from this SQL and use CopyFromRecordset to import the data into a worksheet. You can look up CopyFromRecordset in the Excel VBA help

Reply | Quote

If you use TransferSpreadsheet to export a query to an existing workbook, it will export the query to a worksheet with the same name as the query within that workbook; if the worksheet already exists it will be overwritten.

Alternatively, you could use ralphad’s suggestion: prompt the user for the values in Excel, construct a SQL statement using the values provided by the user, then open a recordset (ADO or DAO) from this SQL and use CopyFromRecordset to import the data into a worksheet. You can look up CopyFromRecordset in the Excel VBA help

Reply | Quote

Hans–

Thanks for the suggestion. I looked into transferring the recordset to Excel through TransferDatabase method, but that will create a new spreadsheet file each time. I have several charts already formated that I just need to update data to. If need be, I could create a chart in Excel using automation from Access, but that is very advanced for my skills and I’m not sure I’d be able to get it done. Is there code already written for this sort of automation that I could use as a template? Again, thanks for your help.

Reply | Quote

MS Query isn’t aware of the existence of Access forms. Is it possible for you to export from Access to Excel instead?

Reply | Quote

If you need to control everything from Excel then you’re going to have to write some VBA. Probably the most straightforward approach would be to challenge the user for the parameters using InputBoxes or a form, contruct an SQL string and then pass it to Access using ADO.

Reply | Quote

Actually, the solution I’m creating is in Access. I can never get Access Charts to do what I want them to do, so I have created an Excel spreadsheet with charts formated as I want them. The end result of this Access solution is a report. When the report is run, one of its actions displays a chart that is linked from Excel. it is included in my report using Paste Special… . When the report is opened in Access, the Excel Chart updates in data prior to being displayed. Its datasource is a linked query from the Access solution I’m creating. So, when I open the report in Access, it pulls in a chart from Excel, but before the chart is displayed, the chart updates it datasource by re-querying Access. Neat little circular logic, I think. This worked well when the Access query did not include parameters. I was just using static criteria in the query grid. It worked fine. When I changed the criteria to use parameters, that is when it broke.

So, Adam, while your solution doesn’t quite fit my design, you got me thinking. Is it possible to construct an SQL statement in Access and present it to Excel to be used in MSQ? I know how to construct the SQL statement, but I wouldn’t know where to begin to get MSQ to use it.

Reply | Quote

You can pass the SQL string to the query in Excel as follows:-

shtData.QueryTables(1).CommandText=sSQL

Where

shtData

is a reference to the worksheet object containing the query and

sSQL

is a String containing your SQL statement. If you have more than one query on the worksheet then you’ll have to adjust

QueryTables(1)

accordingly.

Reply | Quote

You can pass the SQL string to the query in Excel as follows:-

shtData.QueryTables(1).CommandText=sSQL

Where

shtData

is a reference to the worksheet object containing the query and

sSQL

is a String containing your SQL statement. If you have more than one query on the worksheet then you’ll have to adjust

QueryTables(1)

accordingly.

Reply | Quote

Actually, the solution I’m creating is in Access. I can never get Access Charts to do what I want them to do, so I have created an Excel spreadsheet with charts formated as I want them. The end result of this Access solution is a report. When the report is run, one of its actions displays a chart that is linked from Excel. it is included in my report using Paste Special… . When the report is opened in Access, the Excel Chart updates in data prior to being displayed. Its datasource is a linked query from the Access solution I’m creating. So, when I open the report in Access, it pulls in a chart from Excel, but before the chart is displayed, the chart updates it datasource by re-querying Access. Neat little circular logic, I think. This worked well when the Access query did not include parameters. I was just using static criteria in the query grid. It worked fine. When I changed the criteria to use parameters, that is when it broke.

So, Adam, while your solution doesn’t quite fit my design, you got me thinking. Is it possible to construct an SQL statement in Access and present it to Excel to be used in MSQ? I know how to construct the SQL statement, but I wouldn’t know where to begin to get MSQ to use it.

Reply | Quote

If you need to control everything from Excel then you’re going to have to write some VBA. Probably the most straightforward approach would be to challenge the user for the parameters using InputBoxes or a form, contruct an SQL string and then pass it to Access using ADO.

Reply | Quote