Solved

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

Posted on 2016-11-28
4
82 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 85

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 85
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

695 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