[分享]一个连接oracle数据库的VBA

weiguoyin868

<p>使用前安装客户端程序，设置简单，可以联系SQL语句</p><p>Public Sub myors()<br/>&nbsp;&nbsp;&nbsp; Dim cnn As New ADODB.Connection<br/>&nbsp;&nbsp;&nbsp; Dim rs As New ADODB.Recordset<br/>&nbsp;&nbsp;&nbsp; Dim cnnStr As String, myTable As String, SQL As String, i As Integer<br/>&nbsp;&nbsp;&nbsp; 'myTable = "BOOK_STOCK"&nbsp;&nbsp;&nbsp; '指定数据表<br/>&nbsp;&nbsp;&nbsp; '设置建立与Oracle数据库服务器中连接的字符串<br/>&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp; Range("A3:G2000").Select '以下两行是清除数据Contents<br/>&nbsp;&nbsp;&nbsp; Range("A3:G2000").ClearContents<br/>&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp; Lbname1 = Trim(Cells(2, 1))<br/>&nbsp;&nbsp;&nbsp; Lbname2 = Trim(Cells(2, 2))<br/>&nbsp;&nbsp;&nbsp; Lbname3 = Trim(Cells(2, 3))<br/>&nbsp;&nbsp;&nbsp; SQLname = Trim(Cells(2, 4))<br/>&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp; cnnStr = "Provider=MSDAORA;" _<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp; "Data Source=" &amp; Lbname1 &amp; ";" _<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp; "User ID=" &amp; Lbname2 &amp; ";" _<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp; "Password=" &amp; Lbname3 &amp; ";"<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'oadb是oracle服务器名；<br/>&nbsp;&nbsp;&nbsp; cnn.ConnectionString = cnnStr<br/>&nbsp;&nbsp;&nbsp; cnn.Open<br/>&nbsp;&nbsp;&nbsp; '创建查询记录集<br/>&nbsp;&nbsp;&nbsp; SQL = SQLname<br/>&nbsp;&nbsp;&nbsp; 'SQL = "select * from " &amp; myTable<br/>&nbsp;&nbsp;&nbsp; 'SQL = "select * from " &amp; myTable &amp; " where BOOK_PUBLISHER='中国电力出版社'"<br/>&nbsp;&nbsp;&nbsp; MsgBox SQL<br/>&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp; rs.CursorLocation = adUseClient '游标改为客户端游标<br/>&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp; rs.Open Source:=SQL, ActiveConnection:=cnn<br/>&nbsp; <br/>&nbsp;&nbsp;&nbsp; With rs<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; For i = 1 To .Fields.Count&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '复制字段名<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(3, i) = .Fields(i - 1).Name<br/>&nbsp; <br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Next i<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MsgBox "纪录数:" &amp; rs.RecordCount<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Range("A4").CopyFromRecordset rs&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '复制记录数据<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Close&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '关闭记录集<br/>&nbsp;&nbsp;&nbsp; End With<br/>&nbsp;&nbsp;&nbsp; cnn.Close<br/>&nbsp;&nbsp;&nbsp; Set rs = Nothing<br/>&nbsp;&nbsp;&nbsp; Set cnn = Nothing<br/>&nbsp;&nbsp;&nbsp; Range("A3").Select<br/>End Sub</p><p>&nbsp;注：参考了《EXCEL VBA整合数据库应用》一书，但发现：</p><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; For i = 1 To .Fields.Count&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '复制字段名<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(3, i) = .Fields(i - 1).Name<br/>&nbsp; <br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Next i<br/>这段代码有错误，原来：&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(3, i) = .Fields(i - 1)</p><p>更改为：&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(3, i) = .Fields(i - 1).Name<br/><br/></p> aYKj1Bvz.rar (23 KB, 下载次数: 509)

2008-4-2 09:46 上传

点击文件名下载附件
[分享]一个连接oracle数据库的VBA

<br/>感觉调用oracle数据库数据速度很快，可以作为人力资源统计数据来用。

[此贴子已经被作者于2008-4-2 9:55:26编辑过]

hzxiaoy

谢谢,好东西.收藏一下.

bhtqz

谢谢，你这我会用到的

hzy888

找了很久，还是EH藏龙卧虎

sipherdu

非常感谢！正在学习中，有问题还要请教！

zhangjimfu

谢谢楼主分享！！

camino

感謝分享  ～～～～～～～

masterexcel

收藏。感谢楼主分享！

wfwhw

kkkkkk看看

juzai

谢谢分享!!!