如何将 VBA 连接到 postgreSQL 并运行查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21895629/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to connect VBA to postgreSQL and run query
提问by jbooker
I am trying to run a query from an Microsoft excel application and have not been able to connect successfully. I have PostgreSQL 9.3 on my local machine, and am running 64 bit windows 7. I have a sample database name dvdrental which is a demo database. I simply need to connect to the database, run a query, and view the output in my worksheet(or immediate window, either one resolves the connection issue). Here is what I have so far which is not working.
我正在尝试从 Microsoft excel 应用程序运行查询，但未能成功连接。我的本地机器上有 PostgreSQL 9.3，并且正在运行 64 位 Windows 7。我有一个示例数据库名称 dvdrental，它是一个演示数据库。我只需要连接到数据库，运行查询，并在我的工作表（或即时窗口，任何一个都可以解决连接问题）中查看输出。这是我到目前为止无法正常工作的内容。
Option Explicit
Public objConnection As ADODB.Connection
Public strConnection As String
Public Sub TestPostgresConnection()
Dim strConnection As String
strConnection = "Driver={PostgreSQL Unicode};Server=localhost;Port=5432; Database=dvdrental;UID=sa;PWD=wrox;"
Set objConnection = New ADODB.Connection
Set objRecordSet = New ADODB.Recordset
objConnection.Open strConnection
With objRecordSet
.ActiveConnection = objConnection
.Open "SELECT * FROM actor"
End With
Do While Not objRecordSet.EOF
Debug.Print objRecordSet.Fields(0).Value
objRecordSet.MoveNext
Loop
objRecordSet.Close
objConnection.Close
Set objRecordSet = Nothing
Set objConnection = Nothing
End Sub
Here is a list of my references;
这是我的参考资料清单；
Visual Basic For Applications Microsoft Excel 14.0 Object Library OLE Automation Microsoft Office 14.0 Object Library Microsoft Forms 2.0 Object Library Microsoft Access 14.0 Object Library Microsoft ADO Ext. 6.0 for DOL and Security Microsoft ActiveX Data Objects 2.8 Library Microsoft Windows Common Confrols 6.0 (SP6)
Visual Basic 应用程序 Microsoft Excel 14.0 对象库 OLE 自动化 Microsoft Office 14.0 对象库 Microsoft Forms 2.0 对象库 Microsoft Access 14.0 对象库 Microsoft ADO Ext。6.0 for DOL and Security Microsoft ActiveX Data Objects 2.8 Library Microsoft Windows Common Confrols 6.0 (SP6)
When I execute this test method TestPostgresConnection, I get "[Miscrosoft][ODBC Driver Manager] Data source name not found and no default driver specified"
当我执行这个测试方法 TestPostgresConnection 时，我得到“[Miscrosoft][ODBC 驱动程序管理器] 未找到数据源名称且未指定默认驱动程序”
My setup of postgres has been standard and I have simply followed the directions on their website for creating a local RDBMS for testing.
我的 postgres 设置是标准的，我只是按照他们网站上的说明创建本地 RDBMS 进行测试。
Can anyone tell me why I am not able to connect and run a query? None of the solutions have worked so far. Thanks.
谁能告诉我为什么我无法连接并运行查询？到目前为止，所有解决方案都没有奏效。谢谢。
回答by mountainclimber
My answer is a general answer. Giving back to the community. So be nice. I had a similar question and I used the following to set it up.
我的回答是笼统的回答。回馈社会。所以要乖一点。我有一个类似的问题，我使用以下内容进行设置。
Note: I suggest using DSN instead of the driver then you can use a named connection that has the password already, rather than having the password in your code.
注意：我建议使用 DSN 而不是驱动程序，然后您可以使用已经有密码的命名连接，而不是在您的代码中使用密码。
These instructions were a huge help generally: http://www.dashbay.com/2011/03/working-with-postgres-on-windows-via-odbc/
这些说明通常是一个巨大的帮助：http: //www.dashbay.com/2011/03/working-with-postgres-on-windows-via-odbc/
The download link in the link above didn't work for me. I found the ODBC downloads here: https://www.postgresql.org/ftp/odbc/versions/msi/I think I downloaded this one: psqlodbc_09_05_0400-x86.zip
上面链接中的下载链接对我不起作用。我在这里找到了 ODBC 下载： https://www.postgresql.org/ftp/odbc/versions/msi/ 我想我下载了这个：psqlodbc_09_05_0400-x86.zip
I used Konstantin's answer to get %WINDIR%\SysWOW64\odbcad32.exe from this link: PostgresSQL ODBC Drivers on Windows 7 not showing up
我使用康斯坦丁的回答从这个链接中获取 %WINDIR%\SysWOW64\odbcad32.exe： Windows 7 上的 PostgresSQL ODBC 驱动程序没有出现
I also downloaded MS Power Query here which I found helpful: https://www.microsoft.com/en-us/download/details.aspx?id=39379
我还在这里下载了 MS Power Query，我发现它很有帮助：https: //www.microsoft.com/en-us/download/details.aspx?id=39379
I am happy to edit my answer if I should add clarification.
如果我应该添加说明，我很乐意编辑我的答案。
Below is the sub for the query and below that is a sub that demonstrates how you use it.
下面是查询的子程序，下面是演示如何使用它的子程序。
Sub CcQueryPg(sSql As String, Optional sOdbcName As String = "ConnectionNameHere")
'Declare a Connection object
Dim cnDB As New ADODB.Connection
'Declare a Recordset Object
Dim rsRecords As New ADODB.Recordset
'Open the ODBC Connection using this statement
cnDB.Open sOdbcName
rsRecords.Open sSql, cnDB
'Close everything and set the references to nothing
rsRecords.Close
Set rsRecords = Nothing
cnDB.Close
Set cnDB = Nothing
End Sub
Sub SendQuery()
Call CcQueryPg("COPY sometablenamehere FROM '/mnt/somepathhere/somefilename.csv' DELIMITER ',' CSV HEADER;")
End Sub
The above file reference is to a file on a Linux machine. Your path format may differ.
上面的文件引用的是Linux机器上的一个文件。您的路径格式可能不同。
