Output report to file

[dzirkelb]

I wish to output a report to a file. I wish the file to be a word document, saved in the same place everytime, and with the same name everytime. i also wish to overwrite the current file that would be in the folder automatically.

The report name is INVOICE - E-INVOICE-A.

How would I go about doing this?

 

[namliam]

Lookup the "Docmd.OutputTo" in the access help and see how far you can get.
Unfortunatly word (directly) is not an option, RTF is though...

"Kill" may also prove usefull

 

[dzirkelb]

I got the following to work:

DoCmd.OutputTo acOutputReport, "INVOICE - E-INVOICE-A", acFormatRTF, "c:\test.doc"

Now, what I am doing is placing that code into an autoexec maco that runs when access is opened, then it closes access, so this is a one click auto create file process.

I do the same process for printing a report.

The problem I am having now is running this access macro/program from an sql store procedure / query analyzer. What happens is msaccess.exe runs in the task manager, the database creates a ldb file, but it just hangs then.

If I run the database by double clicking on it, it runs perfectly.

I am thinking it is hanging up because it is afraid access will prompt for something. Is there something in my code I have above that could possibly prompt the user for anything? Or, any ideas on how to get this accomplished from a stored precedure / query analyzer?

 

[dzirkelb]

It appears it is failing on the output to portion. I placed a kill("c:\test.doc") in my code. It deletes the file, but does not output a new one. Again, this only happens when running from query analyzer on sql 2000 or a stored procedure...but, this will be the ultimate goal.

 

[namliam]

You can export from SQL Server using the *damn what is it called again* data-transfer or something like that. Except you will not be able to export the report as far as I know.

Try the SQL Server part of this forum... see what they think.

 

[dzirkelb]

Problem is I have to use the access report for bar fonts to print.

I can always code it on the web to creat it, but was hoping I could do this with the report that is already made, would make it a lot easier.

 

[dzirkelb]

I found the problem. The problem is the linked tables in access. The report links numerous other small access tables, and fails when they are linked.

I found this by rebuilding the query table by table, and it failed everytime i added a ms access linked table to another access linked table. If I imported that table into sql and then re-built the query again, then it worked ok.

Any ideas? or, any ideas on where I should look?

 

[namliam]

Well are you executing an Access native query or a pass-through query.

With the PT query it will be executed on the server...
A native query, make sure your linked tables are on the right links/right paths.

 

[dzirkelb]

The linked tables are correct as I can open the table itself and view the data.

The query is a simple select query with one where clause.

 

[namliam]

And you have other tables from SQL Server, the local db and linked tables??

Linked tables should work just the same as real tables... Sorry I am at a loss....

 

[dzirkelb]

I have sql tables linked, access tables linked (don't work), but I do not have any local tables. That was something I was going to try next just to try and narrow down the issue.

 

[namliam]

sounds like there may be an issue in using both ODBC linked tables and normal linked tables?? Perhaps the DB is trying to send the query to SQL Server (pass it through) where it offcourse cannot find your 'normal' linked tables??

** Gambling here **

 

[dzirkelb]

That very well could be, but I don't really know a workaround to that.

One idea I thought of, which I will try after upgrading to 2007 access, is on the macro, import the tables being used so it is local to the database. If that works, then we'll see about the speed degredation as the tables linked odbc aren't that big.