Still No Solution. Need Microsoft or Microsoft MVP....HELP

[Russ]

I've been posting this for the last 10 days with no reply on a real solution. Can someone at Microsoft or a Microsoft MVP reply to this very frustrating issue. All I've gotten thus far is guesses or an "I'm not sure, but there is a problem with Access 2000/2002â€. But, no solutions! I would like to know if there is a way to accomplish a dataset export to excel from an Access 2000/2002 ADP/Project application. The help file says that there is, using the spreadsheet transfer method. But it does not work due to issues which are unclear or at the very least not clearly defined.

I was in a time crunch when I posted 10 days ago. Now, the mess is about the hit the fan and I am no further along than I was 10 days ago. Please, please, can someone who knows how to accomplish this export supply a solution

I am working on an application developed in Access 2000 as an ADP Project. Naturally it contains Views and Stored Procedures. One of the queries called: "qryViewByMachineReworkedEmailData" is to be exported to an Excel97/2000/2002 format. However, the line of code that should do the exporting to excel is generating a strange error. The error reads as follows: Run Time Error 7874: can't find (database' name) the object "qryViewByMachineReworkedEmailData

Private Sub cmdExportToExcel_Click(

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryViewByMachineReworkedEmailData", "MachineParts.XLS", Tru

End Su

Since this is an Access 2000 project application (Now running under Access XP) are there any issues that can possibly be causing this error? What is the fix? Is the code syntax wrong, if it is, can someone assist in correcting it

Thanks in advance for the assistance

 

[Albert D. Kallal]

TransferSpreadsheet only works with mdb applications.

If you use it with a ADP project, you are suing sql server, and you CAN NOT
use a view. You can use the command with a actual table name. So, you if you
*must* use TransferSpreadSheet, then you can send your data to a table, and
then use that table for the export.

A better solution is to use OutPutTo. Using OutPutTo does allow you to use a
query.

Try:

DoCmd.OutputTo acOutputServerView, "qryViewByMachineReworkedEmailData",
acFormatXLS, "MachineParts.XLS"

The above does export the first row as field names, but that is what you
have now.

 

[Albert D. Kallal]

you are suing sql server

That should read

you are using sql server

 

[Russ]

Albert

Thanks, for responding and supplying some sort of option. I will apply your solution to see if it will work in my case. Yes, the ADP system does use Sql server backend

Thanks again for the help. I will post the result of your suggestion, if it works

I've search high and low for the answer to this question. Are the Microsoft guys not involved in supplying answers anymore at this site's forum

 

[Albert D. Kallal]

Albert:

Thanks, for responding and supplying some sort of option. I will apply

your solution to see if it will work in my case. Yes, the ADP system does
use Sql server backend.

Thanks again for the help. I will post the result of your suggestion, if it works!

I've search high and low for the answer to this question. Are the

Microsoft guys not involved in supplying answers anymore at this site's
forum?
Actually, 99% of us are volunteers. There are a few MS folks that also drop
by and contribute. Also, often we mvp's when stuck will ask other MVP's.
Often, we MVP's will even move or ask the question to MS folks that we have
contact with.

Part of your problem is that many use the TransferSpreadsheet and it works
fine for them! The real problem here is that the help file does NOT mention
that you can't use TransferSpreadsheet on a stored views when using a adp
project. I supposed the help file does not also mentioned that stored
procedures are for adp projects, and not a simple mdb file share either!
So, there is certanly a lot of issues when you use a ADP project.

However, as a developer I would not be surprised if a simple mdb file share
did not have stored procedures. However, it is a BIG SURPRISE that views
don't work with TransferSpreadsheet in a ADP project.

Anyway, as mentioned, it DOES WORK when you use a table name...but I don't
think many of us would want or care to use a table name anyway!

The help is now divide between basic new ms-access users trying to use
ms-access, the help also has a Visual basic part for the VB side, and now we
have to throw in the ADP part. We kind of have about 3 separate systems
here.

Between people not realizing this limitation of ADP projects, and the lower
percentage of people using ADP projects...the answer to your problem was a
lot slower then it should have been.

I am at least going to make a note of this, and least suggest to MS that
this "limitation" is either noted in the help file, or even try and get a
Knowledge base article explaining this limitation.

So, while I am kind of trying to explain why there was so little response to
your problem...in fact, a quick and easy answer by most people here using
the KB or even the help file should have been quickly available (so, in
fact, I have nothing but a poor excuse as to why no good clear answer was
available).

This is a problem that has slipped through the cracks...and I going to at
lest make a effort to remediate this lack of information on this.
..
Anyway:

The OutPutTo example I posted should work for you just fine. I would as
a habit use a full pathname for the output.