Scheduled export of Access Table to .csv

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
 
Z

z.heineman

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.


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
 
J

John Nurick

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top