** RESOLVED **Access Query Results shown in Excel S/Sheet

[TheBionicOrange]

I'm trying my luck posting in here as it looks pretty empty.

I have an Access DB and, via a VB front end I need to execute a query and show the results in an Excel spreadsheet, only I'm not too sure which is the best way to go about it.

I thought maybe create the query in Access, and then make the VB prog just execute the query and then duno the results in Excel, but I need an input parameter for the query (week no) as it will change week on week.

Is it possible to design a query in Access allowing for an input parameter ? or do I need to design a form ? If so thats gonna be a bit pointless as I have a VB front end where the week no is entered, so would rather use it from there ...

Am I making any sense ?

[Pozzi]

Hi,

Makes sense to me.

You say you want to show the results in Excel. Do you just want the data dumped into Excel, or do you want to format the data on the sheet it'll appear on?

Also, when the query is run, will your VB front end open Excel so the results can be viewed?

The reason for asking is that if once the query has run and you do want to show the results formatted in Excel, why don't you have it so your app fires up the applicable sheet and have a 'Report Settings' button on the sheet that the user can press which calls an input form to allow the user to enter the Week No and have Excel run the query against the DB?

Regards

[WorkHorse]

No problem. You don't have to set up your Access query to accept a variable. You can pass variables after the WHERE statement in the SQL when you open the query from VB. In DAO it would be something like this:

iRequestedWeekNo = 5
sSQL = "SELECT * FROM MyQueryName WHERE ((WeekNo)='" & iRequestedWeekNo & "');"
    Set rs = db.OpenRecordset(sSQL)

It doesn't really matter whether you are using DAO, ADO, etc. Just put the criteria after the WHERE statement in an SQL to pull up the records that match that criteria.

Then you can open a file in Excel and use the Excel CopyFromRecordset method to put your recordset data into Excel.

Here's a hint: You don't even have to have a query in Access. You can do the whole query from VB by setting the SQL when you open the recordset. If you aren't sure what the SQL should be, create a query in Access then switch to SQL view. It will show you the SQL used to create the query. Sort of like recording a macro in Excel or Word.

[TheBionicOrange]

Cheers guys