export records from linked odbc as flat file?

[nat1]

Hi,

Is it possible to export records from a linked odbc database to a flat file?.

I would like to on a weekly basis backup data to a vertical pipe or comma delimited text file.

Is this relatively straight forward? I looked at creating a macro from the queries already created, using the transfertext command but it didn't work?

Any help would be appreciated.

[stanl]

Hi,

Is it possible to export records from a linked odbc database to a flat file?.
Any help would be appreciated.

Yes; look into the SELECT INTO syntax for Access (and while you are at it get familiar with ISAMs).

something like


cSQL ="SELECT * INTO [HTML Export;DATABASE=c:\temp].[test.htm] FROM [mytable]"

creates an HTML table from an Access Table. In you case, you would want the Text ISAM, so for example


cSQL ="SELECT * INTO [Text;DATABASE=c:\temp;HDR=Yes;Format=Delimited].[test.csv] FROM [mytable]"

creates a csv file with header row. Obviously this can be modified to SELECT specific fields, WHERE.... HAVING.... GROUP BY... ORDER BY and all the other neat SQL stuff. .02 Stan

[nat1]

Thanks for the reply stanl. I will have a go at this when I get back to work and post back if sucessful.

Thanks again.

[nat1]

stanl,

I tried your suggestion but it did not work. I did however after much head scratching have this working..........DoCmd.RunSQL ("SELECT * INTO [Text;DATABASE=e:\export;HDR=Yes;Format=Delimited].[test.csv] FROM [Equip];")

I don't know why the code you posted did not work, most likely something I am doing wrong!! or not understanding.

Thank-you for your help.

[nat1]

Would it be possible to export around 15 tables into just one file?

What I would like is a file with 2 columns, the first with the table name and the second with the delimited text from that table. Is this possible??

Thanks for any help.