Scheduled export of Access Table to .csv

  • Thread starter Thread starter z.heineman
  • Start date Start date
Z

z.heineman

I am looking for a way to set up a scheduled export of an Access table
to .csv file. We want to have this take place each evening and be
automated. We are using 2003 office and if it is easier it can go
straight to an excel file. Any help would be greatly appreciated.

Thanks
 
Jerry said:
Make a Macro named AUTOEXEC that opens frmExportCSV. Both Access and this
form must be open (but not necessarily visible) for the above to work.
Click to expand...


Well, we need something that will run automatically with out anything
being open. So we were thinking of setting up a scheduled task with
windows to open access an then execute a macro, switchboard, or
anything.

Thanks
 
No need to open Access if you just want to export a simple table to CSV.
Just modify this VBScript as required and call it from a scheduled task
in Windows:

'Sample VBScript to export data from an MDB database to a CSV file
'without opening Access

'Modify DB_NAME, QRY_NAME, DATA_DESTINATION as required

Option Explicit

Dim oJet 'As DAO.DBEngine
Dim oDB 'As DAO.Database
Dim oTDef 'As DAO.TableDef
Dim strSQL 'As String

Const DB_NAME = "C:\Temp\Test 2003.mdb"
Const QRY_NAME = "qryTest" 'Query or table to export

'Next line specifies the folder and file for export.
'This example specifies C:\Temp\MyFile.txt
Const DATA_DESTINATION = "[Text;HDR=Yes;Database=C:\Temp\;].MyFile#csv"

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(DB_NAME)

strSQL = "SELECT * INTO " & DATA_DESTINATION _
& " FROM " & QRY_NAME & ";"

wscript.echo strSQL
oDB.Execute strSQL

oDB.Close
'END
 
Back
Top