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!