MS Office 365 ProPlus, Access 2007 - 2016
I need to be able to capture the query results of an Access DB and send that to a txt file on the C drive. This will be done 1x/wk using task scheduler which (I'm thinking) will be running some sort of script. I found different SQL Server based examples of this, but nothing for Access.
Can anyone provide an example of how to do that?
1.From the task scheduler you can actually call macros which can fire modules. Research this as I do not know the syntax off the top of my head but its really easy (just execute the applications with a x/callmymacro command after it)
Creating texts files is super easy. You can either export using DoCmd.TransferText like mentioned above you can build it in code, especially if the query result doesnt have everything or you only need certain pieces. Here is a quick and dirty template for you.
'this is only a template for proof of concept for you - google syntax of how to appropriately
'declare and use recordsets. Same with declaring other variables
'if your text file requieres headers, google how to loop through recordset headers.
'Pretty easy stuff
sep = "|" 'your choice of delimiter
NewTextFile = "pathwhereyouwanttodropyourfile.FileNametxt"
Open NewTextFile For Output As #2
do until rs.eof
'wholeLine is a string variable used to store the currentline of the text file.
WholeLine = WholeLine & rs("filed1") & sep
Print #2, WholeLine
WholeLine = "" 'important to reset variable
rs.movenext
Loop
Close #2
*You get cool points if you figure out how to use an array instead of cycling through a recordset. *
DoCmd.TransferTextor text file object Read/Write .