automatically convert access db

Question

Hello,

I have an access database, with a query made. I need to automate it so that each night this query can run and export to a tab delimited csv file. It is not possible to export a query to a csv file from within access. My question is, are there any tools that can select certain tables, or perform an sql query on an mdb file, and export to a csv file?

Answer 1

VBScript works quite well with the Jet engine. However, I do not see why you say " It is not possible to export a query to a csv file from within access."

 Sub TransferCSV()

    DoCmd.TransferText acExportDelim, , "PutNameOfQueryHere", "C:\PutPathAnd\FilenameHere.csv", True

 End Sub

Is the usual way in VBA.

EDIT: It is possible to run a VBScript file (.vbs) from the command line. Here is some sample VBScript to output a tab delimited file.

db = "C:\Docs\LTD.mdb"
TextExportFile = "C:\Docs\Exp.txt"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open _
   "Provider = Microsoft.Jet.OLEDB.4.0; " & _
   "Data Source =" & db

strSQL = "SELECT * FROM tblMembers"

rs.Open strSQL, cn, 3, 3

Set fs = CreateObject("Scripting.FileSystemObject")

Set f = fs.CreateTextFile(TextExportFile, True)

a = rs.GetString

f.WriteLine a

f.Close

Answer 2

SQL Server Integration Services is able to do the transformation that you are talking about. Don't be fooled by the name, because you don't need SQL Server in order to automate and run the packages.

http://msdn.microsoft.com/en-us/library/ms141026.aspx

Answer 3

Actually, you can export a query to a csv file from within Access.

You can do this with a Macro using the TransferText method.

Macro:

   Name = ExportQuery
   Action = TransferText
   Transfer Type = Export Delimited
   Table Name = [name of your Access query]
   File Name = [path of output file]
   Has Field Names = [Yes or No, as desired]

You can execute the macro from the command line like this:

"[your MS Office path]\msaccess.exe" [your databse].mdb /excl /X ExportQuery /runtime

Since you're having trouble with TransferText in a macro try this:

1) Create a Module named "ExportQuery". In this module, create a function called "ExportQuery":

Function ExportQuery()
    DoCmd.TransferText acExportDelim, , "[your query]", "[output file].csv"
End Function

2) Create a Macro named RunExportQuery:

Action = RunCode
Function Name = ExportQuery ()