Thread: 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
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=&Board=82&Number=5141 04&Forum=,,All_Forums,,&Words=&Searchpage=1&Limit=25&Main=510514&Search= true&where=&Name=57586&daterange=&newerval=&newertype=&olderval=&olderty pe=#Post514104&bodyprev= 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 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
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
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. -----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 > 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
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
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 <ellen@urban.csuohio.edu> 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)