vba - Create comma separated file (csv) from access - scheduled daily from windows

Question

I would like to create a vbs file to export an access table in a csv file (comma separated). I saw this coda from Remou. It works, but it create tab separated. Can anyone help me? Thank you!

db = "C:DocsLTD.mdb"
TextExportFile = "C:DocsExp.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 1

It is not difficult to create a standard CSV

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=z:Docsest.accdb"

sSQL = "select * into " 
sSQL= sSQL & "[text;database=z:docs;FMT=Delimited;HDR=Yes].[csvfile.csv]"
sSQL= sSQL & " from table1"

cn.Execute sSQL

A VBScript file will run quite well from the Task Scheduler.

Edit re comments

If the decimal separator in your locale is a comma, you may run into problems. You can override the Windows locale settings with a schema.ini file, you need only include the name of the file and items you wish to change:

[csvfile.csv]
DecimalSymbol=.

A full list is available form Microsoft: Schema.ini

There is no reason why you should not write the schema.ini in your code prior to export, except be sure not to overwrite existing schemas -- you can append.

You will get information on your system locale from the windows control panel: http://windows.microsoft.com/en-IE/windows7/Change-the-system-locale