trouble linking a Parameter query to Excel 2007

  • Thread starter Thread starter Rob Wills
  • Start date Start date
R

Rob Wills

Greetings...

I've just written a "Stored Procedure" in Access in the hope that a user
would be able to use the "Get External Data" tab to link to my process and
export the data they specify direct into Excel without the need for code.

However the window that opens up for the users doesn't show the stored proc
- or the parameter query that I created as an alternative.

Is there a way round this?

Thanks
Rob
 
Rob,

I've not tried this in 2007, but my experience in 2003 and earlier was that
you cannot "link" an Access parameter query to an Excel spreadsheet. You
can, however, run the query in Access and export the results to your Excel
spreadsheet using the TransferSpreadsheet method, or via Office Automation.

BTW, You also cannot write a query in Access that contains a call to an
Access user defined function, and link to that query from Excel.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
I ended up coding this using ADOX

============================
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim dteResponse As Date

Set cat = New ADOX.Catalog
cat.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=c:\TEMP\AN_Access_Database.accdb"
Set cmd = New ADODB.Command

dteResponse = Range("TodayDate").Value

Set cmd = cat.Procedures("A_Paramater_Query").Command
cmd.Parameters(0) = dteResponse
Set rs = cmd.Execute
Sheets("A_Sheet").Range("A2").CopyFromRecordset rs
rs.Close
 
Rob,

Great to know you can do this. I've been told that the CopyFromRecordset
method is pretty quick. I'll have to give it a try.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
Hi Dale,

I'm familiar with the ADO recordset object - but as I said - I was hoping to
avoid code - and instead utilise the Excel functionality to interrogate the
external data - thus allowing them to just right-click in order to refresh
the data...

The key to the below cose is using the ADOX reference library and setting up
a catalog to run the paramater query

Cheers
Rob
(season's greetings and all)
===========================================
 
Back
Top