Обсуждение: Is there any known issue with MS-Office >2000 fetching Data?

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?

I work on this Access app. that occasionally has to export to (the one
and only globally accepted data transport container) Excel.

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

Then MY own pc gets the job done sooner or later even when I let it talk
to the remote 8.3.5 server via a ssh-tunnel.

My vnc-pc at work has odbc 8.4 but Office 2003 and the above mentioned
8.3.5 server in the LAN.
There those transfers wont go through.
They dump between 1 to x lines where x can be rather high sometimes.
Then comes "Method CopyFromRecordset for the object Range failed.

Especially Views are bad.
Plain selects tend to do better than   select * from my_view   type queries.
Even sql-functions do better. It still crashes but goes way further than
the same select in a view.

By the way ... someone with Vista-Business and Office 2007 has the same
problems.


regards
Andreas

> 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