Thread: ODBC Problem

> Len Morgan wrote:
>
> I have successfully connected to PostgreSQL from Access97 and have
> even been able to make a query that gets it's results from several
> tables all at once.  The problem is that no matter what I do, the
> ORDER BY clause of the query is ignored.  I have set the sort criteria
> and I have tried with a pass-through query but the sorting always
> seems to come out in the order of the subtables that are joined to my
> main table.
>

I have had this problem also, but for the life of me, I can't reproduce
it now.  I remember though, when I had the problem, if I turned on
"Totals" and just had "Group By" for all the fields, it allowed me to
order by whatever I wanted.  It *seems* like a bug in Access?  Try that
and see if it at least has an effect.

Also, what version of the odbc driver are you using?  And, can you
illustrate your query (what tables, joins, etc) so I could try to
reproduce it?

> I also have had a problem where I want to write a pass-through query
> but can't get it to execute without bringing up the "Select Data
> Source" dialog box.  We are only ever going to use one data source and
> I know what it is so why do I get this message EVERY time I run the
> query?  Is there some way to specify the DSN in the pass-through query
> so that I don't have to have my users do this everytime?
>

Yes, I have this as well.  I do not know any way to make Access save the
Data source with the query.  As far as I can tell, it is not the driver
but Access forcing this behavior?  Have you or anyone seen differently
with other databases like Oracle?

Byron

> Len Morgan wrote:
>
> I have successfully connected to PostgreSQL from Access97 and have
> even been able to make a query that gets it's results from several
> tables all at once.  The problem is that no matter what I do, the
> ORDER BY clause of the query is ignored.  I have set the sort criteria
> and I have tried with a pass-through query but the sorting always
> seems to come out in the order of the subtables that are joined to my
> main table.
>

We have learned a few things about the Microsoft Jet Engine since this
mail.  Access uses "dynasets" to handle data in query recordsets.  If
you don't need to update values in your queries, you can use a
"snapshot" recordset, which can be faster.  It also may help your
order-by random problem.  To do this in Access, try bringing up the
"design view" of your query.  Then right click and you should get a
"Query Properties" box.  There is a field called "RecordSet Type" which
probably says Dynaset.  If you set this to "Snapshot", your problem may
go away.

> I also have had a problem where I want to write a pass-through query
> but can't get it to execute without bringing up the "Select Data
> Source" dialog box.  We are only ever going to use one data source and
> I know what it is so why do I get this message EVERY time I run the
> query?  Is there some way to specify the DSN in the pass-through query
> so that I don't have to have my users do this everytime?
>

This one we definately have figured out.  When looking at your pass-thru
query, go to "View" then "Properties" in Access.  This will bring up a
box of properties.  There is one called "ODBC Connect Str" and it should
be defaulted to "ODBC;".  Simply add "DSN=<your_datasource>" after the
ODBC part and it won't keep asking you.

Hope this is helpful.

Byron