Using PostgreSQL on Windows with ADO and VB

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!

About these ads

3 thoughts on “Using PostgreSQL on Windows with ADO and VB

  1. 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

  2. Sorry to burst your bubble, but PROVIDER only works if you have an OLEDB Provider for PostgreSQL installed, it doesn’t work for ODBC.. The following seems to work for ODBC (when the drivers are installed correctly) (mind you, the drivername depends on which version you want to use, ANSI or Unicode):

    Driver={PostgreSQL ANSI};Server=127.0.0.1;Port=5432;Database=mydatabase;UID=myuser;PWD=mypassword;

    also a good site for connectionstrings is the following:

    http://www.connectionstrings.com/postgre-sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s