Using PostgreSQL on Windows with ADO and VB

2007_01_16-postgres.vb

' The problem with PostgreSQL is lack of documentation for Windows interfaces. Visual Basic uses
' the ADO library to connect to the PostgreSQL ODBC driver, which in turns connects to the server.
'
' This example covers a unique requirement - the network 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). Unfortunately, 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