Solved

Ms Access How To Automatically Export Query To A Text File At Specified Times

Posted on 2016-11-28
4
69 Views
Last Modified: 2016-11-29
How can I export my query to a text file automatically lets say 3 times a day at specific times or timing intervals. Every 8 hours. Would this be with the DoCmd.TransferText? I am unfamiliar with this so if you could guide me in the right way that would be awesome. Thanks. Also as a side note does anyone know of a way to set up a URL link that would hold the text file so I could download it later....
0
Comment
Question by:Dustin Stanley
  • 2
  • 2
4 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 41905551
You can use TransferText. If your database is open, you could use the Form Timer event and fire the process off.

Or you could use the Windows Task Scheduler to do this, which is probably a better solution. It's best to use a .BAT file or VBS script instead of manipulating Access directly. It would also be best to create a small database with links to your tables, and ONLY that query in the database, and then use that database for the task. You could create a macro that fires off your query, and then quits Access.

Your .BAT file could be as simple as:

"full path to msaccess.exe" "full path to your database" /X NameOfYourMacro
0
 

Author Comment

by:Dustin Stanley
ID: 41905776
You say unless my database is open....Do you mean open as in like when I'm using it? If so that answers one of my current thoughts.

Task schedular I am aware of and check outed very little but never used.

Would I use it to open the database?

Small database linked to my tables... Can you please explain what the exact reason for this is and why it is better to do than the original database. If I understand things then I can use that thought later for other events.
0
 
LVL 84
ID: 41905818
You say unless my database is open....Do you mean open as in like when I'm using it? If so that answers one of my current thoughts.
IF you use the Form Timer event, then your database must be open, and that Form must be open. If you do not use the Form Timer method, then your database does not need to be opened.

Task schedular I am aware of and check outed very little but never used.

Would I use it to open the database?
If you use the Task Scheduler, you don't really need to open the database - just use the Macro method as suggested earlier.

Small database linked to my tables... Can you please explain what the exact reason for this is and why it is better to do than the original database. If I understand things then I can use that thought later for other events.
Assuming you're going to be using the application daily, if you try to run a Scheduled Task against that database it may or may not fire, depending on what you're doing in the database at the time. If you use a small database with tables linked to your source data, and do NOT use that small database for anything else, then you don't have to worry about things like this.
0
 

Author Closing Comment

by:Dustin Stanley
ID: 41906631
Thanks! Sounds good!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question