CODEWITHSUNDEEP
ms-accesscsv
Joshxtothe4
Joshxtothe4

Reputation: 4193

automatically convert access db

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?

Upvotes: 2

Views: 4456

Answers (3)

Fionnuala
Fionnuala

Reputation: 91356

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

Upvotes: 3

Patrick Cuff
Patrick Cuff

Reputation: 29786

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 ()

Upvotes: 3

Nick Berardi
Nick Berardi

Reputation: 54854

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

Upvotes: -1

Related Questions