Using ADO in VBA to connect to PostgreSQL
Solution 1
I wan't using a DSN as I am using an ODBC driver as opposed to OLE DB. By referencing a DSN, the above code works with very few changes.
See this question for how I found the answer once I began to suspect OLE DB/ODBC to the issue. Does ADO work with ODBC drivers or only OLE DB providers?
New Code here:
Sub GetCustomers()
Dim oConn As New ADODB.connection
Dim cmd As New ADODB.Command
' Connection Parameters
Dim strUsername As String
Dim strPassword As String
Dim strServerAddress As String
Dim strDatabase As String
' User:
strUsername = Sheets("CONFIG").Range("B4").Value
' Password:
strPassword = Sheets("CONFIG").Range("B5").Value
' Server Address:
strServerAddress = Sheets("CONFIG").Range("B6").Value
' Database
strDatabase = Sheets("CONFIG").Range("B3").Value
oConn.Open "DSN=my_system_dsn;" & _
"Database=" & strDatabase & ";" & _
"Uid=" & strUsername & ";" & _
"Pwd=" & strPassword
Set xlSheet = Sheets("CUSTOMERS")
xlSheet.Activate
Range("A3").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select
Dim strSQL As String
strSQL = "SELECT * FROM customers"
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText
cmd.ActiveConnection = oConn
cmd.CommandText = strSQL
Set rs = New ADODB.Recordset
Set rs = cmd.Execute
For i = 1 To rs.Fields.Count
ActiveSheet.Cells(3, i).Value = rs.Fields(i - 1).Name
Next i
xlSheet.Range(xlSheet.Cells(3, 1), _
xlSheet.Cells(3, rs.Fields.Count)).Font.Bold = True
ActiveSheet.Range("A4").CopyFromRecordset rs
xlSheet.Select
Range("A3").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A1").Select
rs.Close
oConn.Close
Set cmd = Nothing
Set param = Nothing
Set rs = Nothing
Set cnn = Nothing
Set xlSheet = Nothing
End Sub
The System DSN is configured to use the PostgreSQL Unicode driver. I chose not to use OLE DB even though there is a provider available. If you look at PGFoundry, you will see it has many problems and has not been updated in several years.
Solution 2
In the original Code, "PostgreSQL35W" is a DSN name which included the default host and port. When you changed to "PostgreSQL Unicode", it is a driver and your connection string is lacking the value for the port. Remember to access PostgreSQL directly from driver, you need at least 5 parameters:
- host
- port
- userid
- password
- database
If you are using DSN, some parameters may be defined as default.
n8gard
Updated on August 31, 2021Comments
-
n8gard over 2 years
I am having trouble finding clear and reliable examples of connecting to a PostgreSQL database from Excel using VBA ADO. Admittedly, I am new to VBA and most examples and tutorials are very Access or MSSQL centered. (I work mostly in Ruby, Rails, Perl and PostgreSQL.)
I am looking for code to connect and return a simple query (SELECT * FROM customers;) to an Excel sheet. Connection parameters (server ip, user, pass, database) are located within cells in a separate worksheet.
I appreciate your help and patience.
Code:
Sub ConnectDatabaseTest() Dim cnn As ADODB.connection Dim cmd As ADODB.Command Dim param As ADODB.Parameter Dim xlSheet As Worksheet Dim rs As ADODB.Recordset Dim sConnString As String Dim i As Integer ' Connection Parameters Dim strUsername As String Dim strPassword As String Dim strServerAddress As String Dim strDatabase As String ' User: strUsername = Sheets("CONFIG").Range("B4").Value ' Password: strPassword = Sheets("CONFIG").Range("B5").Value ' Server Address: strServerAddress = Sheets("CONFIG").Range("B6").Value ' Database strDatabase = Sheets("CONFIG").Range("B3").Value Set xlSheet = Sheets("TEST") xlSheet.Activate Range("A3").Activate Selection.CurrentRegion.Select Selection.ClearContents Range("A1").Select Set cnn = New ADODB.connection sConnString = "DRIVER={PostgreSQL Unicode};DATABASE=" & strDatabase & ";SERVER=" & strServerAddress & _ ";UID=" & strUsername & ";PWD=" & strPassword cnn.Open sConnString cmd.ActiveConnection = cnn Dim strSQL As String strSQL = "SELECT * FROM customers" cmd.CommandType = ADODB.CommandTypeEnum.adCmdText cmd.ActiveConnection = cnn cmd.CommandText = strSQL ...
It seems to break here: cmd.ActiveConnection = cnn
EDIT: added sample code.
EDIT: sConnString gets set to:
DRIVER={PostgreSQL35W};DATABASE=my_database;SERVER=1.2.3.4;UID=analyst;PWD=sekrit
UPDATE 2/7: I changed the 'DRIVER' parameter in the connection string:
sConnString = "DRIVER={PostgreSQL Unicode};DATABASE=" & strDatabase & ";SERVER=" & strServerAddress & _ ";UID=" & strUsername & ";PWD=" & strPassword & ";"
...and I get a different error: 'Run-time error 91: Object variable or With block variable not set'
Hm. Ideas?
-
Tim Williams about 12 yearsAny examples you find for other databases should be useable almost as-is, with the proviso that you'll need a different connection string. Which one you'll need will depend on which driver you have installed. connectionstrings.com/postgre-sql
-
n8gard about 12 yearsYeah, you'd think. I just haven't had any success so far. I'll keep trying in the meantime.
-
Tim Williams about 12 yearsIf you have code that you've tried, then edit your question to include that, and include details of any error messages you got.
-
n8gard about 12 yearsDone. See above. Thanks.
-
datatoo about 12 yearsWhat the values in B4:B7? Have you checked what the sConnString looks like after it is built? Maybe write it all back to a cell so you can check the whole thing for a syntax error?
-
Fionnuala about 12 yearsHave a look here connectionstrings.com/postgre-sql. I have not checked very carefully, but your connection string does not seem quite right.
-
n8gard about 12 yearsReally, Remou? Which part? Should I not be using the ODBC example format? I am missing it...
-
-
Kamil over 3 yearsa little bit of context for your code would be more helpful for n8gard, also please put code snippets within `` marks