vba如何连接db2数据库
1、首先在office电脑上安装db2驱动。
2、然后,新建一个excel,按alt+F11进入vba的编辑窗口。
3、鼠标在左侧框中,点击右键,选择插入模块。
4、双击该模块,并把以下代码复制到该模块中。'该模块主要功能是定义数据库连接,并操纵S兑恶铼融QL语句''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Public Function ConnectString() _ As String ConnectString = "Provider=IBMDADB2.1;Password=test;Persist Security Info=True;User ID=test;" & _ "Data Source=mydb;Location=192.168.1.200:50000;Extended Properties="""""End FunctionPublic Function executeSql(ByVal SQL _ As String, MsgString As String) _ As ADODB.Recordset Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim sTokens() As String On Error GoTo ExecuteSQL_Error sTokens = Split(SQL) Set cnn = New ADODB.Connection cnn.Open ConnectString cnn.CommandTimeout = 600000 If InStr("INSERT,DELETE,UPDATE", _ UCase$(sTokens(0))) Then cnn.Execute SQL MsgString = sTokens(0) & _ " query successful" Else Set rst = New ADODB.Recordset rst.Open Trim$(SQL), cnn, _ adOpenKeyset, _ adLockOptimistic Set executeSql = rst MsgString = "查询到" & rst.RecordCount & _ " 条记录 successful" resultRows = rst.RecordCount End IfExecuteSQL_Exit: Set rst = Nothing Set cnn = Nothing Exit Function ExecuteSQL_Error: MsgString = "查询错误: " & _ Err.Description Resume ExecuteSQL_ExitEnd Function
5、图中标注的部分,需要根据自己的信息来填写。代码的具体内容我就不再一一解释。下面看如何连接数据库。双击s茑霁酌绡heet1,在右边把以下代码复制进去。Sub connect() Dim MsgText As String txtSQL = "select name from table_test " Set mrc = executeSql(txtSQL, MsgText) While Not mrc.EOF branchList = Trim(mrc.Fields(0)) & "," & branchList mrc.MoveNext Wend mrc.CloseEnd Sub
6、按F8进行测试,运行的语句会显示黄色:
7、执行到Set mrc = executeSql(txtSQL, MsgText)烂瘀佐栾这个语句时,会调用刚才的模块。语句块中的变量,会显示当前的值。如图,我的操作结果是:
8、上图显示,已经从数据库成功提取了18条记录。
9、注意事项:1、要确保数据库正常。2、要保证用户名和密码无误。3、如果出现“用户类型未定义”的错误时,请参考下面的解决方法。
10、点确定后就可以了。