Auto Export of Data

[Tim Jenkins]

Hello,

I need to be able to schedule a batch job that will run
each evening and take an Access table and export it into a
CSV format file in order that the file can then be used by
another custom application.

Does anyone know of a way of automating a batch job (say,
via Windows Scheduler) that can do this.

Thanks,
Tim.

 

[Glen Appleton]

Hi Tim,

Here's what I do:

Create an mdb file that is linked to the main database tables you need to
use, and then create the query in the new mdb file that will contain the
fields of data you wish to export.

Next, create a simple routine something like this in VBA:
--- Begin Code ---
Function RunExport()

DoCmd.SetWarnings False

DoCmd.TransferText TransferType:=acExportDelim, _
TableName:="YourQueryNameHere", _
FileName:="//YourServer/YourPath/YourExport.csv", _
HasFieldNames:=True

DoCmd.SetWarnings True
DoCmd.Quit acQuitSaveNone

End Function
--- End Code ---

Next, create a macro in the new mdb file called "Autoexec" and use the
RunCode action to call the RunExport function. The autoexec macro will
automatically execute when the mdb file is opened. If you need to edit the
mdb file, just hold the shift key while opening the file and the macro will
not run.

Finally, create a scheduled task in windows and point it to the mdb file
that contains the macro. Make sure you set the password on the event if
network resources are required during the event.

That's about it. Happy automating! :~)-

- Glen