在 VBA 中使用 ADO 连接到 PostgreSQL

Using ADO in VBA to connect to PostgreSQL

提问by n8gard

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

我无法找到使用 VBA ADO 从 Excel 连接到 PostgreSQL 数据库的清晰可靠示例。诚然，我是 VBA 新手，大多数示例和教程都非常以 Access 或 MSSQL 为中心。（我主要使用 Ruby、Rails、Perl 和 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.

我正在寻找代码来连接并返回一个简单的查询 (SELECT * FROM customers;) 到 Excel 工作表。连接参数（服务器 ip、用户、密码、数据库）位于单独工作表中的单元格内。

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

它似乎在这里中断：cmd.ActiveConnection = cnn

EDIT: added sample code.

编辑：添加了示例代码。

EDIT: sConnString gets set to:

编辑：sConnString 设置为：

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:

更新 2/7：我更改了连接字符串中的“DRIVER”参数：

    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'

...我得到一个不同的错误：'运行时错误 91：对象变量或块变量未设置'

Hm. Ideas?

嗯。想法？

回答by n8gard

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.

我不想使用 DSN，因为我使用的是 ODBC 驱动程序而不是 OLE DB。通过引用 DSN，上面的代码只需很少的更改即可工作。

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?

请参阅此问题，了解一旦我开始怀疑 OLE DB/ODBC 的问题，我是如何找到答案的。 ADO 是与 ODBC 驱动程序一起工作还是仅与 OLE DB 提供程序一起工作？

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.

系统 DSN 配置为使用 PostgreSQL Unicode 驱动程序。即使有可用的提供程序，我也选择不使用 OLE DB。如果你查看PGFoundry，你会发现它有很多问题，而且几年没有更新。

回答by Colin Chiang

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:

在原始代码中，“PostgreSQL35W”是一个包含默认主机和端口的 DSN 名称。当您更改为“PostgreSQL Unicode”时，它是一个驱动程序并且您的连接字符串缺少端口值。记住直接从驱动访问PostgreSQL，至少需要5个参数：

• host
• port
• userid
• password
• database

• 主持人
• 港口
• 用户身份
• 密码
• 数据库

If you are using DSN, some parameters may be defined as default.

如果您使用的是 DSN，某些参数可能会被定义为默认值。

回答by Tom

Not sure about the details of the actual DB connection, but there is a simple although common mistake with your statement: you need to use 'set' when working with objects:

不确定实际数据库连接的详细信息，但是您的语句有一个简单但常见的错误：在处理对象时需要使用“set”：

set cmd.ActiveConnection = cnn