SQL PassThrough query - CSV Error

[Nicodemus]

In MS Access I connect to TeraData server through ODBC.

I've built an SQL PassThrough query (named 'Q_Test') that extracts the data
I need.
This query works perfectly (ODBC TimeOut property set to 3600 seconds)
Now I want to execute this query and export the outcome to a CSV file via
VBA :
DoCmd.TransferText acExportDelim, , "Q_test", "C:\temp\Q_test.csv", True

When running the VBA code, I get the following msg after +- 5minutes :
“Time error 3251 Operation not supported for this type of objectâ€

Does anyone know a solution/workaround to get the query result being
exported to CSV ?

Thank you already for any help.
Nicodemus

 

[Alex Dybenko]

Hi,
make a "new table query" or append query, run it to copy data from
PassThrough query to a temporary table, export this table, and then delete
it or its records

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[dmac101]

I'm curious, can you use a passthrough query to append/insert data to a local table? (I haven't tried).
Could you provide an example?

 

[Nicodemus]

Hi Alex,

I'm afraid this solution would have an impact on the performance.
I've found another trick, though.
Open the SQL PassThrough query as an ADODB recordset, and write each record
one by one to a file using Open statement.

Thanks anyway for your help and your time.
Nico

 

[Alex Dybenko]

Hi,
not sure that my method would be slower, queries are normally faster then
looping through records

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[David W. Fenton]

I'm afraid this solution would have an impact on the performance.
I've found another trick, though.
Open the SQL PassThrough query as an ADODB recordset, and write
each record one by one to a file using Open statement.

Writing a temp table is likely going to be substantially faster than
looping through all the records. Why? Because it will use a SQL
append operation to append all the records at once. Once that's
done, TransferText will also run very fast.

In any event, you should test it to see -- you might be shocked.