SQL PassThrough query - CSV Error

  • Thread starter Thread starter Nicodemus
  • Start date Start date
N

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
 
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?
 
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
 
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.
Click to expand...

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.
 
Back
Top