Solved

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

Posted on 2016-11-28
4
61 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

840 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