作者Eming (eming)
看板Database
标题[心得]Excel VBA接Oracle
时间Mon Jun 4 08:14:46 2012
以下是我从Excel接上Oracle的过程
花了不少时间走冤枉路 所以想说纪录下来跟有需要的版友分享
(另外我是社会组毕业的 有些观念不太清楚 请见谅)
系统资讯
作业系统:win7 pro 64 bits
资料库:Oracle 10g
1.Driver选用
64-bit Oracle Data Access Components (ODAC)
http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
下载解压後 找到setup.exe 选run as administrator安装
2.sqlnet.ora & tnsnames.ora设定
到Driver的资料夹的Network->Admin
(我的路径如下C:->app->User->product->11.2.0->client_1->Network->Admin)
这边我不懂为什麽Driver没有带我走一遍
印象中安装32bit的Driver就有的样子
以下是我的设定
tnsnames.ora
$DB_NAME =
(DESCRIPTION =
(ADDRESSLIST =
(ADDRESS = PROTOCOL = TCP)(HOST = $HOSTNAME)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = $DB_NAME)
)
)
sqlnet.ora
SQLNET.AUTHENTICATION_SEFVICES=NTS)
NAMES.DIRECTORY_PATH=(TNSNAMES)
3.ODBC设定
到我的电脑->控制台->system and security->Administrative Tools
->Data Sources(ODBC)
点开选User DSN
->Add 选Driver "Oracle in OraClient11g_home1"
Data Source Name:(自己取)
Desription:(自己取)
TNS Service Name:$DB_Name
User ID:$DB_Username
输入後按"Test Connection"测试一下
4.Excel VBA
我觉得重要的是connect string 应该是ADO吧
DataSource要填之前在ODBC填的Data Source Name
strConOracle = "Provider=MSDASQL.1;Persist Security Info=False;" & _
"Password=XXXXXX;User ID=$DB_Username;Data Source=$DB_NAME"
还有在Command加上CommandTimeOut的property
避免复杂Query时间过长造成ORA-01013 Error
我这边只用300秒
Sub OracelQuery()
Dim strConOracle As String
Dim oConOracle, oRsOracle, OracleCommand As Object
Dim strSQL As String
Dim lngCount As Long
Dim strCount As String
Dim x As String
Dim total_field_count, i As Integer
x = Workbooks("oracle").Worksheets("QUERY").Range("A1").Value
strConOracle = "Provider=MSDASQL.1;Persist Security Info=False;" & _
"Password=XXXXXX;User ID=$DB_Username;Data Source=$DB_Name"
Set oConOracle = CreateObject("ADODB.Connection")
Set oRsOracle = CreateObject("ADODB.RecordSet")
Set OracleCommand = CreateObject("ADODB.Command")
oConOracle.Open strConOracle
strSQL = x
With OracleCommand
.ActiveConnection = oConOracle
.CommandType = 1
.CommandText = strSQL
.CommandTimeout = 300
End With
Set oRsOracle = OracleCommand.Execute
total_field_count = oRsOracle.Fields.Count
Sheets("Unconfirmed Details").Select
Range("A2").Select
ActiveCell.CopyFromRecordset oRsOracle
For i = 1 To total_field_count
Workbooks("oracle").Worksheets("Unconfirmed
Details").Cells(1, i).Value = oRsOracle.Fields(i - 1).Name
Next
oConOracle.Close
Set oRsOracle = Nothing
Set oConOracle = Nothing
End Sub
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 24.6.30.127
※ 编辑: Eming 来自: 24.6.30.127 (06/04 08:18)
※ 编辑: Eming 来自: 24.6.30.127 (06/04 08:18)