Hi Again Stephen,
I've also had similar issues with Oracle and SQL Server. I don't use
access as part of the system, but use it to link to tables for quick
data checks, or quick data entry, especially during development. It's
also great for import/export and initial data setup. I think that the
problem may be more with the server and not especially with the ODBC
driver, and possibly a little bit to do with access. I know that access
simplifies things, and it doesn't alway do what I expected (especially
queries with joined linked tables).
One workaround I've found is that instead of opening the table directly,
create an updatable query that includes the entire source table, (Select
* from source_table) then use that query instead of the linked table
directly. I think that the query will do a cursor, but opening a linked
table doesn't.
I'm posting this to the list in case other people have had similar
issues.
Steve
-----Original Message-----
From: Stephen Frost [mailto:sfrost@snowman.net]
Sent: Wednesday, October 01, 2003 8:18 AM
To: Steve Lutz
Subject: Re: [ODBC] FUD!! ODBC will not be supported by Microsoft in the
future
* Steve Lutz (slutz@alacritude.com) wrote:
> When you say open a table, what do you mean? Are you doing a SELECT *
> from BIG_TABLE? Or are you just attempting to select individual rows?
> (Select * FROM BIG_TABLE WHERE SOMECOLUMN=SOMEVALUE)
>
> We have many huge tables, but we never really select the entire table,
> but if you are having problems with sub-sets of those tables, I'd like
> to know.
As I mentioned, we're using Access. When you link in a table with
access and then 'open' it by double-clicking on the 'table' you get the
table view. With Oracle, at least, it seems to do this alright, even
for pretty big tables. With Postgres for the same tables it grinds the
machine to a halt. My guess as to the reason would be that the postgres
libraries will attempt to copy the entire table into local memory and
then give it to access, which also tries to put it all in memory, so
you end up with two copies of the data in memory. A cursor in the ODBC
driver could help with this issue, or a change in the API to allow you
to get things piecemeal, which is what Oracle does and what I would
prefer personally.
Stephen
