Access as a front-end or another query builder

I've tried the many suggestions about making Access as a front-end to
postgreSQL faster, i.e. turned the ODBC trace off, set Declare/Fetch On,
used the commlog, but it still is about 10 times slower than running the
queries in pgadmin. The Access pass-thru queries kind of defeat the
purpose of having a query builder since there isn't any design view with
them. Is there an FAQ on setting up Access as a front-end or just steps
that are known to provide reasonable performance?

Are there any good free query builders out there?

Ellen


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Access as a front-end or another query builder

Ellen,

I use Access as my front end, and had the same problem. In the end I
ended up using pass-thru queries that I created on the fly with a little
VBA. I still use the query builder in Access to get all of my joins
right (it's so much easier to see it visually when there are a lot of
joins). I bring the SQL that Access generates into Notepad and hand
massage it until I've got it just right. After I've got the SQL the way
I want it, I use that string of SQL in VBA and generate my queries on
the fly, changing each of the criteria that needs to be changed each
time I generate the query. Check out:
http://www.utteraccess.com/forums/showflat.php?Cat=3D&Board= 3D82&Number=3D5=
141
04&Forum=3D,,All_Forums,,&Words=3D&Searchpage=3D1&Limit=3D25 &Main=3D510514&=
Search=3D
true&where=3D&Name=3D57586&daterange=3D&newerval=3D&newertyp e=3D&olderval=
=3D&olderty
pe=3D#Post514104&bodyprev=3D

Mike

-----Original Message-----
From: pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Ellen Cyran
Sent: Thursday, February 23, 2006 11:18 AM
To: pgsql-odbc@postgresql.org
Subject: [ODBC] Access as a front-end or another query builder

I've tried the many suggestions about making Access as a front-end to=20
postgreSQL faster, i.e. turned the ODBC trace off, set Declare/Fetch On,

used the commlog, but it still is about 10 times slower than running the

queries in pgadmin. The Access pass-thru queries kind of defeat the=20
purpose of having a query builder since there isn't any design view with

them. Is there an FAQ on setting up Access as a front-end or just steps=20
that are known to provide reasonable performance?

Are there any good free query builders out there?

Ellen


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Access as a front-end or another query builder

On Thu, Feb 23, 2006 at 11:17:49AM -0500, Ellen Cyran wrote:
> I've tried the many suggestions about making Access as a front-end to
> postgreSQL faster, i.e. turned the ODBC trace off, set Declare/Fetch On,
> used the commlog, but it still is about 10 times slower than running the
> queries in pgadmin. The Access pass-thru queries kind of defeat the
> purpose of having a query builder since there isn't any design view with
> them. Is there an FAQ on setting up Access as a front-end or just steps
> that are known to provide reasonable performance?
>
> Are there any good free query builders out there?

Unfortunately I don't know of any good GUI front ends, free or not. But
if you're only looking to build queries that you're then embedding into
an application, there are probably some commercial tools you could use
But since I do stuff 'the hard way', I can't really recommend any.
Though, would pgAdmin do what you wanted? It might have a query builder.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Access as a front-end or another query builder

You might try WinSql from
http://www.synametrics.com/SynametricsWebApp/WinSQL.jsp They have a
query wizard that works with Postgresql. Not sure if the free version
has it or not, but at $250 per copy it's not what I'd call expensive.=20

-----Original Message-----
From: pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Jim C. Nasby
Sent: Thursday, February 23, 2006 6:54 PM
To: Ellen Cyran
Cc: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Access as a front-end or another query builder

On Thu, Feb 23, 2006 at 11:17:49AM -0500, Ellen Cyran wrote:
> I've tried the many suggestions about making Access as a front-end to=20
> postgreSQL faster, i.e. turned the ODBC trace off, set Declare/Fetch
On,=20
> used the commlog, but it still is about 10 times slower than running
the=20
> queries in pgadmin. The Access pass-thru queries kind of defeat the=20
> purpose of having a query builder since there isn't any design view
with=20
> them. Is there an FAQ on setting up Access as a front-end or just
steps=20
> that are known to provide reasonable performance?
>=20
> Are there any good free query builders out there?

Unfortunately I don't know of any good GUI front ends, free or not. But
if you're only looking to build queries that you're then embedding into
an application, there are probably some commercial tools you could use
But since I do stuff 'the hard way', I can't really recommend any.
Though, would pgAdmin do what you wanted? It might have a query builder.
--=20
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Access as a front-end or another query builder

Relyea, Mike wrote:
> Ellen,
>
> I use Access as my front end, and had the same problem. In the end I
> ended up using pass-thru queries that I created on the fly with a little
> VBA. I still use the query builder in Access to get all of my joins
> right (it's so much easier to see it visually when there are a lot of
> joins). I bring the SQL that Access generates into Notepad and hand
> massage it until I've got it just right.

For this hand massaging job I'd recommend pgAdmin's query tool: you have
syntax highlighting and can check syntax and performance immediately.

We still don't have a programmer who's interested in spending time on a
query builder for pgAdmin, volunteers are welcome.

Regards,
Andreas

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Access as a front-end or another query builder

Have you tried Excel, using MS-Query? MS-Query comes on the standard
Windows install CD, you just have to install it. It's nothing fancy,
but it works, and speed doesn't seem bad. E.g. you can drag/drop a
field from one table to another to form a join. Our clients want to
*use* the data in Excel anyway, so it's a win.

-- George Young

On Thu, 23 Feb 2006 11:17:49 -0500
Ellen Cyran threw this fish to the penguins:

> I've tried the many suggestions about making Access as a front-end to
> postgreSQL faster, i.e. turned the ODBC trace off, set Declare/Fetch On,
> used the commlog, but it still is about 10 times slower than running the
> queries in pgadmin. The Access pass-thru queries kind of defeat the
> purpose of having a query builder since there isn't any design view with
> them. Is there an FAQ on setting up Access as a front-end or just steps
> that are known to provide reasonable performance?
>
> Are there any good free query builders out there?
>
> Ellen
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>


--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings