在VBA中使用ADO连接到PostgreSQL [英] Using ADO in VBA to connect to PostgreSQL

问题描述

使用VBA ADO，我无法找到从Excel中连接到PostgreSQL数据库的清晰可靠的示例。诚然，我是VBA的新手，大多数示例和教程都是以Access或MSSQL为中心。 （我主要工作在Ruby，Rails，Perl和PostgreSQL。）

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

感谢您的帮助和耐心。

代码：

  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 
 
'连接参数
 Dim strUsername As String 
 Dim strPassword As String 
 Dim strServerAddress As String 
 Dim strDatabase As String 
'用户：
 strUsername =表（CONFIG）。范围（B4）值
'密码：
 strPassword =表（CONFIG）。范围（B5）。值
'服务器地址：
 strServerAddress = Sheets（CONFIG）。Range（B6）。value 
'数据库
 strDatabase = Sheets（CONFIG）。 （B3）值
 
设置xlSheet =表（TEST）
 xlSheet.Activate 
范围（A3）。激活
 Selection.CurrentRegion.Select 
 Selection.ClearContents 
 Range（A1）。选择
 
设置cnn =新建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 
 ... 
  / pre> 
 
 
似乎在这里打破：cmd.ActiveConnection = cnn 
 
 
 
编辑：添加示例代码
 
 
 
编辑：sConnString设置为：
  DRIVER = {PostgreSQL35W} ; DATABASE = my_database; SERVER = 1.2.3.4; UID =分析师; PWD = sekrit 
  
更新2 / 7：我更改了连接字符串中的DRIVER参数：
  sConnString =DRIVER = {PostgreSQL Unicode}; DATABASE = & strDatabase& ; SERVER =& strServerAddress& _ 
; UID =& strUsername& ; PWD =& strPassword& ; 
  
 ...我收到一个不同的错误：'运行时错误91：对象变量或与块变量未设置'
 
 
 
 Hm。想法？
解决方案
我不使用DSN，因为我使用ODBC驱动而不是OLE DB。通过引用DSN，上面的代码可以很少的修改。
 
 
 
看到这个问题，一旦我开始怀疑OLE DB / ODBC的问题，我发现答案。 
  ADO是否与ODBC驱动程序配合使用或只有OLE DB提供商？ 
 
 
 
新代码在这里：
  Sub GetCustomers（）
 Dim oConn As New ADODB.connection 
 Dim cmd As New ADODB.Command 
'连接参数
 Dim strUsername As String 
 Dim strPassword As String 
 Dim strServerAddress As String 
 Dim strDatabase As String 
'User：
 strUsername = Sheets（CONFIG）。Range（B4）。Value 
 '
 strPassword = Sheets（CONFIG）。范围（B5）值
'服务器地址：
 strServerAddress = ）.Value 
'Database 
 strDatabase = Sheets（CONFIG）。Range（B3）。value 
 
 
 oConn.OpenDSN = my_system_dsn;  &安培; _ 
Database =& strDatabase& ; &安培; _ 
Uid =& strUsername& ; &安培; _ 
Pwd =& strPassword 
 
设置xlSheet =表（CUSTOMERS）
 xlSheet.Activate 
范围（A3）。激活
 Selection.CurrentRegion.Select 
 Selection.ClearContents 
 Range（A1）。选择
 
 Dim strSQL As String 
 strSQL =SELECT * FROM customers
 
 cmd.CommandType = ADODB.CommandTypeEnum.adCmdText 
 cmd.ActiveConnection = oConn 
 cmd.CommandText = strSQL 
 
设置rs =新建ADODB.Recordset 
设置rs = cmd.Execute 
 
对于i = 1到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 
范围（A3）。选择
 Selection.CurrentRegion.Select 
选择。 Columns.AutoFit 
范围（A1）。选择
 
 rs.Close 
 oConn.Close 
 
设置cmd = Nothing 
设置param = Nothing 
设置rs = Nothing 
设置cnn = Nothing 
设置xlSheet = Nothing 
 End Sub 
  
系统DSN是配置为使用PostgreSQL Unicode驱动程序。即使有可用的提供程序，我也选择不使用OLE DB。如果你看看PGFoundry，你会看到它有很多问题，几年来还没有更新。
 
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?
解决方案
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.

                        
这篇关于在VBA中使用ADO连接到PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！