> Hi,
> sure it's kind of OT but please bear with my question a wee bit.
> Perhaps you know a better place to ask PG, ODBC + Access questions?
You can try www.utteraccess.com I've often had helpful hints there.
> I work on this Access app. that occasionally has to export to
> (the one and only globally accepted data transport container) Excel.
I do the same. The connections, size of the dataset and query
complexity are more than Access can handle. I just basically use it as
a front end.
> My work pc has PostgreSQL 8.4 + psqlODBC 8.4 as well as
> Office 2000 installed.
> So my app does the usual chore ... get a connection and a
> readonly recordset ... open a excel-file ... try to dump with
> CopyFromRecordset into a range-object of Excel.
> Connection: tried with Client- and ServerCursor
> Recordset: adLockReadOnly, adOpenForwardOnly or adOpenStatic
I tried going the straight recordset path but seem to recall running
into issues where Access would barf on some export actions that should
have gone through just fine. It's been a few years since I struggled
with this though so my memory is a little hazy.
What I do know for sure is that when I'm exporting any data I use a
different approach. I build pass through queries - which if I remember
correctly can't be exported directly? - and put the results of the pass
through query into a local Access table and finally export the contents
of the table. I've written functions to create the pass through query
and to transfer the query results to a table. Then I just use the
DoCmd.TransferSpreadsheet command to export the table. It definitely
would seem better to be able to do it all straight from a recordset but
that wasn't working as expected
Mike