2

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?

Improve this question

3 Answers 3

Reset to default
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 ()
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

I have tried this, but when creating a Macro I do not have the option to export delimited(in the macro builder) and I can not just put this into the visual basic editor. There is no way for me to set a parameter for TransferText, certainly not a tab stop.
What version of Access are you using?
Access 2003 standard. My copy is in german, so some things may be different.
Maybe :( I'm using Access 2003 with all default settings and no add-ins. I edited my answer with another option to try.
Hi Patrick, I have tried this again, and it does not seem to do anything. I don't understand why my copy cannot simply run the query, although searching a lot of people seem to have this. Is it possible to make it tab delimited. or is only comma possible?
|
3

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
Improve this answer

13 Comments

In Access 2003 I can only export to textdata as ascii records, not a csv file.
I meant as a click-able option, I was not familiar enough with VB to attempt anything else.
If you create a query and then choose file->export and select Save as Type: Text Files, you will get a dialog that will talk you through your export.
This is only true for tables for me, not queries. That is the first thing I tried.
What goes wrong? Does you query work ok when you open it? Do you get an error message?
|
-1

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

Improve this answer

2 Comments

I am still reading over this, but have not yet got the gist. Is it a tool I would run and can schedule, or must I build a separate query from the one I have already?
There is no need whatsoever to resort to SQL Server for something that is actually quite trivial to accomplish with Access by itself.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.