is this possible???

[muscle]

I'm to the point where I'm desperate!! I have an Access 2000 DB which consists of several reports. I can export each report into Word Files or htm files easily and then post them on the web. But I was wondering, if it was possible, to somehow write a macro? or something to do this automatically. For example, every 1 hour, it exports a specific report into Microsoft Word, and places the Word file in a specific location on the server. Is this possible. Any insight would be GREATLY appreciated. Thanks beforehand!!

 

[simongallop]

Yes it is possible. You can move files from directory to directory using code. Either use a module (not sure of the syntax) or you can make a BAT file which contains DOS commands.

Sorry so brief, but if you post any more q's then will try to answer later

hth

 

[BukHix]

There are a couple of different ways that you could manage this. If you just want to have access put the files in a directory for you this will work.

DoCmd.OutputTo acReport, "ReportName", "RichTextFormat(*.rtf)", _
    "c:\Inetpub\wwwroot\FileName", False

You can also use the Shell command to manipulate files but if the database is on a server why not use ASP to display your data live? No need to come up with a routine to send the data at time intervals when it get be delivered to the browser as it is requested.

 

[muscle]

Thanks so much for the replies. Much appreciated. I'm presently using ColdFusion, and would actually jsut like to on my CF page to display a link like the following.<a href="Reports/BMSreport.doc">Budget Report</a> , then jsut run a macro daily to export the access report into Word and placed in that specific directory. I have the macro that does this, except it keeps popping up a message that ready "file already exists, replace file yes or no". If I could jsut solve this and set some sort of schedule, I'd hhave it made. Any ideas?

 

[simongallop]

If the code is in Access try DoCmd.SetWarnings False

 

[muscle]

where would I input this code? In the Action Arguements in the specific Macro design view or perhaps a condition?

 

[BukHix]

I am not sure of the best way to trigger the event but your macro would look similar to this:

If you wanted to do it with code instead of a macro you would so it something like this:

    DoCmd.SetWarnings False
    DoCmd.OutputTo acReport, "VehicleReport", "RichTextFormat(*.rtf)", _
        "C:\", False
    DoCmd.SetWarnings True

Hopefully some one else can help you with the timing.

 

[muscle]

Thank you so much for the reply. The code thing is totally new to me. If I go with the code, do I insert this into my webpage? Or perhaps in the Access Database someplace? Thanks again!!

 

[simongallop]

The code is inserted into a module page.

If you stick with the macro approach, then you can use the Scheduled Tasks bit of windows to call access and autorun the code.

Say that your db is C:\MyFiles\MyDB.mdb and that the macro is called PeriodicRun

Create a bat file called Periodic.bat(in Notepad). Insert this line of code:

"c:\program files\microsoft office\office\msaccess.exe" c:\MyFiles\MyDB.mdb /X PeriodicRun

Now scedule the bat file to run whenever needed.

HTH

 

[muscle]

Ok. What a great idea. I did the following, but I'm still getting a popup that reads "File already exists, replace yes or no". What if I were to include some code in the batch file to delete the file each time or something to this degree. Also is it possible to shut down the program Access after te task runs, so it's not open on the server? Again, thanks a million!!! I think we've almost got it!!

 

[simongallop]

Yes make the first line of the batch file del c:\....\myfile

Make the last line of the macro: Quit This closes access or if you are using VBA (module) then Docmd.Quit

[This message has been edited by Harry (edited 03-27-2002).]

 

[muscle]

Everything worked great. Thank you so much. But I wanted to see if I could tap into your knowledge base one more time. In my macro in Access, I'm having to output to a specific folder and listing the file name, for example...D:/Reports/bms.doc in the Output File in the Action Arguements box. Well, then in the batch file I'm deleting the file then adding the new file. Super. But my question is, what if I wanted to save a history of these files. Could I perhaps, rename the file after the macro runs in the batch file to the current system date??? I woul dimagine I may be able to, but not sure how. Again thanks so much and I owe you one!!