vba 来自 excel 宏的 DB2 连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9872829/
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
DB2 connection from excel macro
提问by user1271146
I want to connect to DB2 from excel macro...This is my code, but it not working, Its giving error as 'Run-time Error'...Can anyone help me...
我想从 excel 宏连接到 DB2...这是我的代码，但它不起作用，它给出的错误是“运行时错误”...谁能帮我...
Option Explicit
Dim DBCONSRT, QRYSTR As String
Dim DBCON, DBRS As Object
Private Sub query()
DBCONSRT = "Driver=jdbc:db2://my_host;Database=PRTHD;hostname=NZ1;port=5355;protocol=TCPIP; uid=my_user;pwd=my_pass"
'CHANGE THE BELOW QUERY STRING ACCORDING TO YOUR NEED
QRYSTR = "select * from PRTHD.STRSK_OH_EOO"
Set DBCON = CreateObject("ADODB.Connection")
DBCON.ConnectionString = DBCONSRT
DBCON.Open
'BELOW CODE USED TO GET THE DATABASE CONECTION AND EXECUTE THE QUERY CHANGE ACCORDIGN TO YOUR NEED
Set DBRS = CreateObject("ADODB.Recordset")
With DBRS
.Source = QRYSTR
Set .ActiveConnection = DBCON
.Open
End With
End Sub
Edit:I have changed my code to the following, but I'm still getting an error. The Error is "cant create Object"..Can ayone help me..
编辑：我已将代码更改为以下内容，但仍然出现错误。错误是“无法创建对象”..谁能帮我..
Dim DBCONSRT, QRYSTR As String
Dim DBCON As Object
Sub query()
DBCONSRT = "Provider=MSDASQL.1;Persist Security Info=False;User ID=user;Data Source=NZ1;DSN=NZ1;UID=user;SDSN=;HST=ibslnpb1.sysplex.homedepot.com;PRT=4101;Initial Catalog=PRTHD;"
DBCON = CreateObject("OLEDB.Connection")
DBCON.ConnectionString = DBCONSRT
DBCON.Open()
End Sub
采纳答案by em3ricasforsale
The JDBC functionality I am pretty sure is not supported through vba and I think you need to use ODBC connectors to connect to DB2 if you are trying to integrate it into excel.
我很确定 vba 不支持 JDBC 功能，如果您尝试将其集成到 excel 中，我认为您需要使用 ODBC 连接器连接到 DB2。
Private Sub query()
DBCONSRT = "Provider=MSDASQL.1;Persist Security Info=False;User ID=user;Data Source=NZ1;DSN=NZ1;UID=user;SDSN=;HST=ibslnpb1.sysplex.homedepot.com;PRT=4101;In??itial Catalog=PRTHD;"
Using connection = New OleDbConnection(DBCONSRT )
connection.Open()
Dim cmd = connection.CreateCommand()
cmd.CommandText = QRYSTR //This is where your sql statement should go, or the variable that is equal to the query.
Using dr = cmd.ExecuteReader()
//Process your query results here
End Using
End Using
End Sub
回答by transistor1
Start with changing
从改变开始
DBCON = CreateObject("OLEDB.Connection")
to
到
Set DBCON = CreateObject("ADODB.Connection")
If you still get an error, double-check your connection string.
如果仍然出现错误，请仔细检查您的连接字符串。
