Thread: Re: BEST odbc driver for windows MS Access

I am not yet using Access as a front end to pgsql, but it is something I would like to get set up in the future, and I AM in the very early stages of developing an application which has an Excel front end to pgsql, and the data connection issues here may be very similar.  I am currently comparing the use of ODBC vs. OLEDB (with ADO in VBA) to connect to pgsql and pull data in.  I think OLEDB will be faster and more stable...IF a good OLEDB driver for pgsql exists, that is.  And if not, will I have to go thru MSQuery to get to pgsql via ODBC?  I hope not...

So I don't have an answer for Karabac, but I'll looking at the same questions and I'll post what I find.  And if anyone has thoughts or experience on OLEDB vs. ODBC for connecting to pgsql from Microsoft software, I'd love to hear of them!

~Kelly

Karebac wrote:

I installed pgsql yesterday on my windows xp.  I was under the impression that I needed to also download and install a specific odbc driver so that MS Access could link to pgsql tables.   Someone explained to me that an ODBC automatically installs now, when I install pgsql.  Sure enough, they were right. I was able to find an ODBC driver and link MS Access to pgsql.

 

My question is this:  Is that default odbc driver, which installs with pgsql the best one to use for MS Access?   I notice that there are a confusing array of odbc drivers to download.   What is the difference between all of them. How can one decide which is best for a particular purpose, such as MS Access.

 

Do many of you use MS Access as a front end to pgsql?

 

Thanks!

No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.15.15/580 - Release Date: 12/8/2006 

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
               http://www.postgresql.org/about/donate 

-- 
**************************************
Kelly Close
Database and GIS Specialist
Hydrosphere Resource Consultants, Inc
(303) 443-7839
(303) 442-0616 (fax)
kkc@hydrosphere.com

Visit our web site!
http://www.hydrosphere.com

> PARENT DIRECTORY
> DLL
> MM
> MSI
> OLD
> SRC

Each of the above directories are giving you the option has to how you would like to have your
ODBC driver installed.

DLL -- probably give you only the dll files. You will have to manual install them and register in
the windows resistry your self.  MM -- not to sure what it is for.  MSI --  give you the odbc
drivers in a format that will install themselves after you execute the msi file.  OLD -- probably
old drivers kept for anyone that specifically needs the old drivers.  SRC -- give you the sources
code from the odbc driver.  This way you can compile it yourself and then install it into windows
yourself.

I expect you should just choose the MSI directory since it is the easiest method to install your
odbc drivers.

> And the DLL link shows a myriad of zipped choices for download, at the top
> of the list being

Pick the newest driver.  it will be the driver with the largerst number.

Regards,

Richard Broersma Jr.

> And if
> not, will I have to go thru MSQuery to get to pgsql via ODBC?  I hope not...

I am not to sure how to answer your question.  The functionality does MSQquery give you that you
get some other way with OLEDB.

> And if anyone has thoughts or
> experience on OLEDB vs. ODBC for connecting to pgsql from Microsoft
> software, I'd love to hear of them!

My opionion of OLEDB for postgresql is that it is broken and certainly not more stable in this
case.  Even worse, there doesn't seem to be much support for the oledb project as compared with
the odbc projects.  If you find that a bug keeps you from completing your project, there aren't
very many places to turn.

from the following link:

http://archives.postgresql.org/pgsql-odbc/2006-10/msg00009.php

you see that an old version of ODBC was able to handle a load of 600 transactions requests per
second.  The Command Prompt odbc-ng appears to almost double this throughput, however it breaks
when I try to use it.  This should be expected since this driver is still released as being under
the "public test mode".

If you are enterested in using the odbc-ng driver you could use the following mailing list for
help:

http://lists.commandprompt.com/mailman/listinfo/odbcng

Regards,

Richard Broersma jr.

And if 
not, will I have to go thru MSQuery to get to pgsql via ODBC?  I hope not...   

I am not to sure how to answer your question.  What functionality does MSQquery give you that you
get some other way with OLEDB.
 

This is what I'm not actually sure of, but some more research I've done tonight seems to indicate that the method I've used accessing an OLEDB source entirely from within code (which is what I'm trying to do) may be completely adaptable to an ODBC source.

Using SQL Server as an example, I can connect from Excel VBA using code that looks something like this:
oConn.Open "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"00)

I was hoping I could replace the "sqloledb" provider with the proper reference for the postgresql oledb provider and have a working connection!

I now find that I can theoretically also connect to SQLServer thru ODBC using a connection string in VBa that looks something like:
oConn.Open "Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"

So again, if I install the PostgreSQL ODBC driver and put it in there in place of {SQL Server}, I should have a working connection I can pass SQL thru from code...right?  :-)  That's what I'm going for anyway.  I am really just looking to do pretty simple querying and writing back to the database with this.  If ANYONE out there has any experience with this, from any MS application using VBA, I would appreciate hearing from them...and any references to other message lists are welcome too - thanks for the pointer to the odbcng list, Richard.

~Kelly

And if anyone has thoughts or 
experience on OLEDB vs. ODBC for connecting to pgsql from Microsoft 
software, I'd love to hear of them!   

My opionion of OLEDB for postgresql is that it is broken and certainly not more stable in this
case.  Even worse, there doesn't seem to be much support for the oledb project as compared with
the odbc projects.  If you find that a bug keeps you from completing your project, there aren't
very many places to turn.

from the following link:

http://archives.postgresql.org/pgsql-odbc/2006-10/msg00009.php

you see that an old version of ODBC was able to handle a load of 600 transactions requests per
second.  The Command Prompt odbc-ng appears to almost double this throughput, however it breaks
when I try to use it.  This should be expected since this driver is still released as being under
the "public test mode".

If you are enterested in using the odbc-ng driver you could use the following mailing list for
help:

http://lists.commandprompt.com/mailman/listinfo/odbcng

Regards,

Richard Broersma jr.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
              http://archives.postgresql.org 

-- 
**************************************
Kelly Close
Database and GIS Specialist
Hydrosphere Resource Consultants, Inc
(303) 443-7839
(303) 442-0616 (fax)
kkc@hydrosphere.com

Visit our web site!
http://www.hydrosphere.com

> If ANYONE out there has any experience with this,
> from any MS application using VBA, I would appreciate hearing from
> them...and any references to other message lists are welcome too -
> thanks for the pointer to the odbcng list, Richard.

If you install the MSI version of the driver, it will also install odbc help documentation in your
windows start menu.  There is an example of how to connect using vb.  Also if you search the odbc
mailing list archieves you will find many other examples that show how to do this.  There are some
important "lessons learned"  about linking tables via ms-acces also documented in the archieves.

to search odbc list archieves check out the following link:
http://archives.postgresql.org/pgsql-odbc/

one of the many sample ado connection scripts:
http://archives.postgresql.org/pgsql-odbc/2006-11/msg00019.php

Here is one such bit of advice:
http://archives.postgresql.org/pgsql-odbc/2006-10/msg00022.php

Regards,
Richard Broersma Jr.

> You could visit utteraccess.com and post at their subforum for posgressql
> and mysql.  It is a pretty active forum with lots of people knowledgable in
> VBA and SQL.

Thanks for the compliment with you refered to me as an "expert". :-)
http://www.utteraccess.com/forums/showflat.php?Board=36&Number=1312899

But I am really just a novice on the list just like you are.  Only I've had a little more time to
get familar with PG web site than you have.

If you want to see a list of the REAL experts check out:
http://www.postgresql.org/developer/bios

Do a "find" for "odbc" on that webpage and you will see the people that actually deserve that
compliment.

Regards,

Richard Broersma Jr.

.> > You could visit utteraccess.com and post at their subforum for posgressql
> > and mysql.  It is a pretty active forum with lots of people knowledgable in
> > VBA and SQL.


You bring up valid points regarding the postgresql website about where it can be improved.
However, your suggestions can only be inacted if you report them to the correct people.

So regarding you consern about what MSI,DLL,MM,SRC, et.al acutally mean:
http://www.postgresql.org/ftp/odbc/versions/

and why the odbc driver are sorted in ascending vs descending order:
http://www.postgresql.org/ftp/odbc/versions/msi/

since your concerns address the display of the postgresql website, you should post your concerns
to:
webmaster@postgresql.org

If this is the wrong person, they will know who to forward your conserns on to.  At least this way
your concerns can be addressed by people who have the ablity to make changes.

When you post problems such as these on non-postgresql mailinglists you are not doing yourself or
anyone else a favor.

Regards,

Richard Broersma Jr.

Karebac wrote:
> I posted my question at utteraccess.com and was directed to this link as
> the best odbc driver for postgres
>
> http://pgfoundry.org/projects/psqlodbc/
>
> I am wondering how it differs (or if it differs) from the odbc which was
> installed automatically when I installed pgsql.

It's the same code (might be a different version though). The pgFoundry
site is where the development work happens.
>
> That link leads to this link for Window
>
> http://pgfoundry.org/softwaremap/trove_list.php?form_cat=216
>

Not sure how you got there - some pgFoundry link I guess. Not one we've
published.

>
> The page at the first mentioned link ALSO least to this page
>
> http://www.postgresql.org/ftp/odbc/versions/
>

Yes, it says:

psqlODBC is the official PostgreSQL ODBC Driver. Downloads are available
on the PostgreSQL Mirror network at http://www.postgresql.org/ftp/odbc/

> and it is at THIS LINK that I find great confusion as to WHICH ODBC
> driver I should be downloading for my DELL Windows XP
>
>
> PARENT DIRECTORY

I'm sure you can guess what that one is :-)

> DLL

Thats the driver distributed as a zip of the dll's - not recommended
unless you know what you're doing.

> MM

That's the driver distributed in a Windows Installer Merge Module, for
developers to include in their own installers.

> MSI

That's probably what you want - the standard Windows Installer MSI package.

> OLD

Old (well, archaic versions)

> SRC

Source code.

>
> And the DLL link shows a myriad of zipped choices for download, at the
> top of the list being

Yes - unless you specifically need an older version for some reason, get
the latest. No-one is likely to support the older ones, and the first
question you're likely to be asked is 'are you running the latest version'?

I'll add a readme to that directory on the FTP site though... thought
there was one already :-(

Regards, Dave

Very helpful, thanks!

~K.

Richard Broersma Jr wrote:

If ANYONE out there has any experience with this, 
from any MS application using VBA, I would appreciate hearing from 
them...and any references to other message lists are welcome too - 
thanks for the pointer to the odbcng list, Richard.   

If you install the MSI version of the driver, it will also install odbc help documentation in your
windows start menu.  There is an example of how to connect using vb.  Also if you search the odbc
mailing list archieves you will find many other examples that show how to do this.  There are some
important "lessons learned"  about linking tables via ms-acces also documented in the archieves.

to search odbc list archieves check out the following link:
http://archives.postgresql.org/pgsql-odbc/

one of the many sample ado connection scripts:
http://archives.postgresql.org/pgsql-odbc/2006-11/msg00019.php

Here is one such bit of advice:
http://archives.postgresql.org/pgsql-odbc/2006-10/msg00022.php

Regards,
Richard Broersma Jr. 

-- 

Kelly Close
Hydrosphere Resource Consultants, Inc.
1002 Walnut, Ste 200
Boulder, CO  80302

(303) 443-7839
http://www.hydrosphere.com