export data from Access adp Project to Excel

[RickW]

I have an Access ADP project and wish to export data to Excel. I have tried
docmd.TransferSpreadsheet and docmd.TransferText, both of which I am very
familiar with when using MDB databases. However they so not seem to work
with ADP as all I can get is a message saying Access cannot find the SQL view
that appears in the Access Query window. I have checked the permissions and
have dbo prefixing the name of the View but can't think what else could be
wrong.

Can you confirm whether these methods can be made to work with ADPs, and it
so how?

If they are not available, can you point me in the right direction please?

Rick

 

[RickW]

I have now also tried DoCmd.SendObject with the same result - Access couldn't
find the View. So I then tried the Macro version of SendObject which has
just three arguements which are all combo selections:
Object Type: Server View
Object Name: dbo.qryDataLoad_Properties
In Database Window: Yes

However this returned the same error - Couldn't find
"dbo.qryDataLoad_Properties" which I had selected!!

Looks very like a bug to me.
Rick

 

[John Nurick]

I never use ADPs, but a quick visit to http://groups.google.com suggests
that one solution is to create a view on the server and then use
DoCmd.OutputTo. The following seemed to work for the poster it was
addressed to:

CurrentProject.Connection.Execute _
"IF OBJECT_ID('View1') IS NOT NULL " & _
" DROP VIEW View1"

CurrentProject.Connection.Execute _
"CREATE VIEW View1 AS " & _
"Select * From MyTable " & _
"Where Id Between 10 AND 20"

DoCmd.OutputTo acOutputServerView, _
"view1", acFormatXLS, CurrentProject.Path & "\Test.xls"

Another suggestion was "I would use SQL Server DTS to create the
process, and then add functionality to the ADP to call the task."