Excel VBA 链接 Oracle数据库

前言

        由于自己的工作需要（减少自己的工作量），需要通过excel 的 vba 链接oracle 数据库更新数据。查阅了大量的资料不是不全就是你抄我的我抄你的，更本就没有进行验证，导致很多地方不明不白的误人啊。
        目前经验证的两种方式：
        1、免安装客户端+ odbc
        2、安装客户端模式
        相比较第二种方式，第一种方式没有那么繁琐不怕配置数据库错误导致需要卸载重装。本文选择第一种方式，方便简单。
        说明下：本文中使用的是 oracle 11g + odbc + dsn + excel vba
        重点：不建议通过这种方式去链接数据库，会导致你配置的其他 oracle 数据库无法链接，请看最后边的 第五步

准备

        1、远程数据库的准备：oracle 11g + windows10

        2、本地免安装客户端下载：两个包 instantclient-basic 和 instantclient-odbc

        3、安装odbc 驱动、配置DSN数据源

        4、链接数据库测试

第一步 远程数据库的准备

        一般来说，你要链接了肯定都是有相应的数据库已经存在才会去链接啊， 本文中链接的远程数据库是在 vm 虚拟机中模拟的，在windows 10上安装的oracle 11g 数据库.
        

第二步 本地免安装客户端下载

1、下载基本包

        本地需要准备两个包 instantclient-basic 和 instantclient-odbc
        这两个包都来自于 oracle 官网，选择 12-1 这个版本的，11-2 这个版本不知道为何在测试的时候配置DSN数据源的时候始终无法使用。 下载地址在下面
        

1.1 包下载地址

下载地址：https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html

1.2 网盘下载地址

        由于是国外的网站需要特殊访问才行，很多人又在CSDN上传了但是需要付费才能下，附上网盘地址：
链接：https://pan.baidu.com/s/1yFhdrkYy7w9qYQQzxfsZVw
提取码：1234
        下载后将两个包解压到同一个目录下，我是解压到
C:\other\instantclient_12_1，记住这个位置

2、配置oracle 网络文件

        需要配置一个网络文件 tnsnames.ora ，这个文件的作用是让本地客户端知道需要访问的数据库地址和相关信息
        在刚才解压的那两个包的目录下新建 “network\admin” 这个目录，将tnsnames.ora 这个文件放在这下面

2.1tnsnames.ora 文件内容

        这个内容拷过去将下面图片的地方改为你自己的内容就行了

# tnsnames.ora Network Configuration File: D:\app\zico\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
 
ORCL_win10 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.129)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

第三步、安装odbc 驱动、配置DSN数据源

1 安装odbc 驱动

         打开我们电脑的数据源【我是win10 64位的】，可以看到目前的驱动程序没得和oracle 相关的

        将刚刚解压的两个包的文件目录【C:\other\instantclient_12_1】下的 odbc_install.exe 使用管理员权限安装

        再次查看我们的数据源的时候已经有 oracle 的驱动了

2 配置DSN数据源

为啥要配置这玩意？其实是因为我们没有安装相应的客户端，excel 只有先从windows 的环境中查找是否有相应的驱动
         同样的在 ODBC数据源管理程序（64位）这里，点击 用户 DSN ⇒ 添加


Data Source Name 这里随便填写
TNS Service Name： 必须填写你 oracle -》 network\admin 目录下的 tnsnames.ora 文件中为远程数据库设置别名
C:\other\instantclient_12_1\network\admin\tnsnames.ora


        点击 test connection 进行数据库链接测试,输入用户名密码，点击ok 进行测试，在填写正确的情况下就会 弹出 connection successful.
        如果弹出其他的请自行百度，一般是上面哪些信息填写错误了
User name :远程数据库用户登录的密码
Password ：登录密码

第四步 VBA链接数据库测试

4.1 设置相应的 Windows 环境变量

1、path 环境变量后边加上客户端文件地址：C:\other\instantclient_12_1

2、TNS_ADMIN 新建这个变量，值根据自己的情况按照图示填写

3、新建 NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK 这个变量是为了防止 oracle 中文变成乱码

4.2 新建 vba 文件进行测试

        经过上面的步骤就可以在vba 中进行数据库信息查询测试
        新建一个excel 文件打开顶部的 开发工具 ==》插入 ==》按钮 ==》新建相应的 宏
         如果没得这个选项就是你的 excel 的 开发工具 功能未开启，请自行百度

链接字符串说明：

cnn.Open "Driver={Oracle in instantclient__12_1};Dbq=tnsnames.ora中数据库别名或者tnsnames.ora中整个串;User Id=数据库用户名;Password=密码;"

{Oracle in instantclient__12_1} ： 就是你配置DSN的时候添加的数据源驱动的名字；
Dbq：tnsnames.ora中数据库别名或者tnsnames.ora中整个串；
User Id：数据库用户名；
Password：密码

Sub ORACLE测试()
   
   Dim strConn As String  '链接字符串'
   
   Dim dbConn As Object  '链接对象'
   
   Dim resSet As Object '查询结果集'
   
   '设置自己的链接信息'
   Dim db_sid, db_user, db_pass As String 'sid，用户名，密码'
   db_sid = "win10_Orcl_DNS"
   db_user = "system"
   db_pass = "123456"
   
   '创建对象'
   Set dbConn = CreateObject("ADODB.Connection")
   Set resSet = CreateObject("ADODB.Recordset")

    '拼接链接字符串
    ' '
    strConn = "Driver={Oracle in instantclient_12_1};Dbq=ORCL_win10;User Id=system;Password=123456;"
   '-----打开数据库------'
   dbConn.Open strConn
   
   '执行查询'
   Set resSet = dbConn.Execute("select count(1) from prd_part t")
   
   '粘贴结果
    Range("A2").CopyFromRecordset resSet
    
    '-----关闭连接----
    dbConn.Close '关闭数据库

   
   
   
   
   
End Sub

第五步 坑货问题

        配置第四步的环境变量后，会导致无法监听到其他数据库。
注意事项：
不能在已经安装了clinet 的上面在设置环境变量，使用 plsql 链接其他数据库的时候就会出现 直接去找 我们配置的DSN 数据源导致 无法链接其他数据库

配置数据源后，如果你安装了数据库的话，你的数据库配置就会依照你配置 TNS_ADMIN 的环境变量 来进行替换，比如由于没得 lisenter 文件，导致net manager 下就没有监听信息， 即使使用 lsnrctl status
1、“实例 “CLRExtProc”, 状态 UNKNOWN, 包含此服务的 1 个处理程序…”
2、"ORA-01034: ORACLE not available
3、ORA-27101: shared memory realm does not exist
"

删除环境变量就好了

六 奇异现场

        由于我的电脑由于工作需要在本机上安装了 oracle 11g (完整版)和 client(客户端)， 经过上面配置基本文件、增加环境变量、安装驱动、配置DSN 数据源 后，我怕会影响到自己的数据库就将环境变量删除、dsn数据源删除 ，但是我的 vba 还是能通过之前配置的环境变量（客户端设置）进行链接，下面记录下相关环境情况说明

        
        1、本地client
        我的电脑上安装了两个client ,一个用于 plsql 链接 、一个用于测试这次的vba 链接，目录分别为：D:\APS\client\instantclient_11_2、 D:\APS\client\other\instantclient_12_1

        2、环境变量：
环境变量设置的是

ORACL_HOME =  D:\APS\client\other\instantclient_12_1
PATH 新增 ： D:\APS\client\other\instantclient_12_1
TNS_ADMIN =  D:\APS\client\other\instantclient_12_1\network\admin

        3、数据源和驱动
        安装的是 11.2 的驱动并配置了DSN 数据源

        4、奇异问题
        设置这后测试vba 链接数据库没得问题，为了安全我删除了以上配置，没有删除odbc 驱动。 但是我的 vba 还是能进行链接数据库。

        我再次操作 vba 发下还是能链接数据库，这里就很让人疑惑了。 我将 tnsnames.ora 下的配置文件进行修改，vba 就无法链接，只有 vba 中数据库名和 tnsnames.ora 文件中的 别名对应的时候又是可以链接的

        说明系统已经将之前设置的环境变量记住了，但是我在系统已经删除了啊，我甚至在另一台虚拟机上进行测试，唯独没有 配置 DSN 数据源，就会报错 未发现数据源

        对于这个问题，特地在这里记录下，实在是想不明白为甚么了，还希望大家给与解答下