Personal Website of Yakov Shafranovich

« Webmin, Rackspace and Securing Servers Much Ado about DabbleDB »

Using PostgreSQL on Windows with ADO and VB

Posted January 16, 2007 – 11:02 pm by Yakov Shafranovich in Programming

My wife’s workplace is a through and through Windows shop running on Microsoft Access. As they have expanded, they have begun to bump against the inherent limits of a file-based database like Access. Currently, they are considered switching to a commercial server-based database such as MS SQL Server, but I was able to suggest that they should give open source a try – PostgreSQL which I use at work (I don’t recommend MySQL anymore due to their aggresive interpretation of the GPL).

The problem with PostgreSQL is lack of documentation for Windows interfaces. My wife is using Visual Basic 6.0 with ADO in conjunction with the PostgreSQL ODBC driver. Visual Basic uses the ADO library to connect to the PostgreSQL ODBC driver, which in turns connects to the server.

In my wife’s case, she has a unique requirement – her workplace has over 300 individual desktop machines, all of which must be able to access the planned PostgreSQL server via Access, VBA or VB6. However, they do not want to go and setup a data source name (DSN) on each machine separately (installing ODBC is easier via the Windows deployment tools). Unfortunatly, the ODBC driver has absolutely zero documentation as to how to setup an ADO connection WITHOUT a DSN. After some prolonged tries and failures, we both were finally able to come up with a solution which I am posting here for others to benefit from.

Normally, an ADO connection requires a “DSN=xxx” in its ConnectionString property of the Connection object. However, for PostgreSQL it is possible to set it up without a DSN as follows:

Dim conNew as New ADODB.Connection()
conNew.ConnectionString = "PROVIDER=PostgreSQL;" & _
    "DATA SOURCE=127.0.0.1;" & _
    "LOCATION=testdb;" & _
    "USER ID=someuser;" & _
    "PASSWORD=pass;" 

conNew.Open

That’s all folks!

Tags: microsoft, postgresql, visual basic, windows —

Permalink | Trackback URL | This post has

2 Responses to “Using PostgreSQL on Windows with ADO and VB”
OK, I know it’s work. But are you developing software for 300 network-stations using localhost ip? surprising! You have a lot of fun while developing client server application in realtime with this nasty PostgreSQL using VB.

Any how best of luck for choosing PostgreSQL

By nayudu on May 12, 2009
The localhost security was not my choice. I am still pushing them to use ActiveDirectory authentication and it looks like that in a few month I may get my wish.

By Yakov Shafranovich on Jun 4, 2009