(.08.02更新)本文内容在Windows10企业版64bit下测试通过
本文主要内容:
Oracle环境配置
ODBC驱动设置、第三方驱动下载
VBA连接Oracle连接方法
Oracle10g官方免账号下载地址
(1月10日更新)关于连接数据库的问题
系统环境:
Windows 7 64bit
Excel 64bit
Oracle_win64_11gR2_client完整客户端(64bit)
1. VBA连接Oracle前的环境配置
如果想连接本地数据库,必须先安装Oracle服务器端。
我这里使用的是内网其他电脑上的Oracle数据库,这里只安装Oracle客户端就可以。
1.1 安装Oracle完整版客户端
网上应该有各种关于VBA连接Oracle的帖子了,不过之前我按他们说的尝试,都无法连接成功,可能是操作系统版本的问题。以下介绍我自己尝试成功的两种办法:
从Oracle官网的数据库下载列表中,选择对应自己操作系统版本的完整版客户端下载,需要注册Oracle网站的用户(这里我使用的是11G64bit版本)
下载好以后,解压,然后点击client\setup.exe 安装程序。在步骤1/6中,选择“管理员”,在步骤4/7中,可能会提示你Environment variable:"PATH" 项目 状态失败,不用管,选择忽略即可。
安装完毕后进入安装目录的\Administrator\product\11.2.0\client_1\network\admin\tnsnames.ora中设置服务,也可以通过Net Manager工具设置
1.2 使用连接工具测试连接
数据库配置好之后,使用PLSQL Developer 或者Navicat等连接工具,测试一下数据库是否可以正常登陆,可以正常使用的话,再进行下面这一步。
如果无法正常登陆,请返回去重新检查连接服务设置。
PS:数据连接工具推荐使用Navicat,可以同时连接不同的数据库,非常方便。
我常用的是11.0.16版本 下载地址 提取码: s5kt
1.3 配置ODBC驱动
进入“控制面板”-》“管理工具”-》“数据源”。点击“添加”,选择和1.1中对应版本的Oracle ODBC驱动(我这里是11.02.00.01),点击“完成”,然后会弹出设置界面如下:
DataSourceName: 该ODBC连接的名称
TNS Service Name:在1.1中你设置的连接名称,如果为空,则说明客户端设置出错,应重新检查是否正确设置了tnsname.ora
User ID:登录数据库的用户名
填写完以上三个选项后,可以使用Test Connection测试一下连接是否正常,测试正常的话进入下一步。
特别提醒:
可能你的本地有其他依赖Oracle的软件,如果产生冲突,可以同时安装32bit和64bit的客户端解决。
我这里的情况是本地软件必须使用32bit的,那么我就先安装了64bit的完整客户端,然后安装了32bit的简化版客户端,这样就互不影响了
友情提醒:
如果你安装的客户端位数和操作系统不同,是没法正常配置ODBC数据源的。如果你不想装两个版本的客户端,也可以使用第三方驱动。
第三方驱动我测试成功的是 devart odbc driver for oracle不过这玩意要149刀,不花钱的话只能试用30天。
1.4 配置VBE环境
在Excel这边,需要先在VBE中启动数据库连接支持。按下Alt+F11打开VBE,在菜单栏选择“工具”-“引用”,在弹出的引用窗口中,找到"Microsoft ActiveX Data Objects 6.1 Library"和"Microsoft ActiveX Data Objects Recordset 2.8 Library",把前面的框勾选上,点击确定即可。 (如果不是这两个版本,则选择一个版本号最高的勾选即可)
2. VBA连接Oracle
在按照上述步骤配置了环境支持后,就可以在VBA中使用代码连接Oracle了。
首先需定义连接对象:
Dim conn asADODB.ConnectionSet conn = new ADODB.Connection
这里也可以简写为:
Dim conn As New ADODB.Connection
连接数据库
strConn = "Driver={Oracle in OraClient11g_home1};Dbq=TNS服务名;Uid=数据库登录账户;Pwd=数据库登录密码;"conn.Open strConn'打开数据库
连接字符串ConnectionString中的各个参数应该很明了,就不一一解释了。注意这里的driver 设置中必须正确填写驱动的版本号,在ODBC界面可以明显看到版本号及名称。
上一段代码也可以简写为
conn.Open "Driver={Oracle in OraClient11g_home1};Dbq=TNS服务名;Uid=数据库登录账户;Pwd=数据库登录密码;" '打开数据库
再次提醒,Driver变量的值是必须要和数据源中添加的新数据源一致的,否则会提示找不到数据源。
至此,数据库连接成功!
可以使用连接对象的State属性和Version属性查看数据库状态和版本(检查是否连接成功)
MsgBox("连接成功!" & vbCrLf & "数据库状态:" & conn.State & vbCrLf & "数据库版本:" & conn.Version)
最后关闭数据库连接
conn.CloseSet conn = Nothing
整个过程的完整代码如下:
Sub连接Oracle数据库()'1. 引用ADO工具'2. 创建连接对象
Dim conn As NewADODB.Connection'3. 建立数据库的连接
strConn ="Driver={Oracle in OraClient11g_home1};Dbq=TNS服务名;Uid=数据库登录账户;Pwd=数据库登录密码;"conn.Open strConnMsgBox ("连接成功!" & vbCrLf & "数据库状态:" & conn.State & vbCrLf & "数据库版本:" &conn.Version)
conn.CloseSet conn = Nothing
End Sub
至此,Oracle的连接方法就结束了,至于使用VBA操作数据库,可以参考我的上一篇文章
(1月10日更新)——————————————————
如果出现无法连接数据库的情况,很有可能是因为没有配置ODBC数据源,VBA要求每个数据库都需要单独配置一个数据源。
3. Oracle10g下载资源分享
如果要连接Oracle,需要安装完整版的客户端
从官网下载这些软件,你需要一个OTN免费帐号,不过如果通过迅雷进行下载,就不用登陆OTN了:(直接复制下载链接即可)
Oracle Database 10g Release 2 (10.2.0.1.0) Enterprise/Standard Edition for Microsoft Windows (32-bit)
/otn/nt/oracle10g/10201/10201_database_win32.zip
/otn/nt/oracle10g/10201/10201_client_win32.zip
/otn/nt/oracle10g/10201/10201_clusterware_win32.zip
/otn/nt/oracle10g/10201/10201_gateways_win32.zip
Oracle Database 10g Release 2 (10.2.0.1.0) Enterprise/Standard Edition for Microsoft Windows (x64)
/otn/nt/oracle10g/10201/10_win64_x64_database.zip
/otn/nt/oracle10g/10201/10_win64_x64_client.zip
/otn/nt/oracle10g/10201/10_win64_x64_clusterware.zip
Oracle Database 10g Release 2 (10.2.0.1.0) Enterprise/Standard Edition for Linux x86
/otn/linux/oracle10g/10201/10201_database_linux32.zip
/otn/linux/oracle10g/10201/10201_client_linux32.zip
/otn/linux/oracle10g/10201/10201_gateways_linux32.zip
Oracle Database 10g Release 2 (10.2.0.1.0) Enterprise/Standard Edition for Linux x86-64
/otn/linux/oracle10g/10201/10201_database_linux_x86_64.cpio.gz
/otn/linux/oracle10g/10201/10201_client_linux_x86_64.cpio.gz
/otn/linux/oracle10g/10201/10201_clusterware_linux_x86_64.cpio.gz
/otn/linux/oracle10g/10201/10201_gateways_linux_x86_64.cpio.gz
Oracle Database 10g Release 2 (10.2.0.1.0) Enterprise/Standard Edition for AIX5L
/otn/aix/oracle10g/10201/10gr2_aix5l64_database.cpio.gz
/otn/aix/oracle10g/10201/10gr2_aix5l64_client.cpio.gz
/otn/aix/oracle10g/10201/10gr2_aix5l64_cluster.cpio.gz
/otn/aix/oracle10g/10201/10gr2_aix5l64_gateways.cpio.gz
Oracle Database 10g Release 2 (10.2.0.2) Enterprise/Standard Edition for Solaris Operating System (x86)
/otn/solaris/oracle10g/10202/10202_database_solx86.zip
/otn/solaris/oracle10g/10202/10202_client_solx86.zip
/otn/solaris/oracle10g/10202/10202_clusterware_solx86.zip
Oracle Database 10g Release 2 (10.2.0.1.0) Enterprise/Standard Edition for Solaris Operating System (x86-64)
/otn/solaris/oracle10g/10201/x8664/10201_database_solx86_64.zip
/otn/solaris/oracle10g/10201/x8664/10201_client_solx86_64.zip
/otn/solaris/oracle10g/10201/x8664/10201_clusterware_solx86_64.zip