Le 16/02/2010 16:22, Relyea, Mike a écrit :
>> Yes, that's the last option.
>> I would have liked something more straightforward but I think
>> I'll have to go this way.
>> Quite painful to parse the recordset in a plain SQL INSERT
>> though, and it feels like doing in VBA exactly what the ODBC
>> driver is doing.
>
> I agree. I've had to do many such workarounds with my own
> Access/Postgres database. It's not the way things are 'supposed' to
> work but it may end up getting the end result you want.
>
> A word of caution that I bumped into - passthrough queries have a limit
> on the number of characters they can contain. I don't remember what
> that limit is off the top of my head but I'd guess somewhere around
> 65000. If your insert string(s) are very long you may have to split it
> up into more than one passthrough.
I wrote a small function that returns a nice SQL-formatted string when
passed an ADO Field object (escaped with E'' for strings, NULL for
nulls, ISO for dates, etc...).
I juste loop through the Fields collection of my recordset, create the
SQL string and execute the resulting command directly via
Connection.Execute.
Speed is OK now, ~15s for 10.000 records so that is only twice slower
that direct insert in psql ou pgAdmin. Good enough for me !
Since I juste use a VB string to store the query and pass it to the
Execute method, I think the limitation won't apply. VB strings can be
very long (2^31 characters I think).
Thanks for your help
Regards
--
Arnaud Lesauvage