VBA 实践指南 -- VBA连接各种数据库

硬核分析仙人

数据治理/商业分析/供应链 - 尼尔森iq

ADO 简介

ADO (ActiveX Data Objects，ActiveX数据对象）是Microsoft提出的应用程序接口（API）用以实现访问关系或非关系数据库中的数据。例如，如果您希望编写应用程序从DB2或Oracle数据库中向网页提供数据，可以将ADO程序包括在作为活动服务器页（ASP）的HTML文件中。当用户从网站请求网页时，返回的网页也包括了数据中的相应数据，这些是由于使用了ADO代码的结果。

VBA与数据库的连接需要通过ADO组件来实现，以下是微软官方文档提供的ADO明细：

ADO 连接字符串

参数说明
Provider 指定用于连接的提供程序的名称。
File Name 指定包含预置连接信息的、特定于提供程序的文件（例如，持久化的数据源对象）的名称。
URL 将连接字符串指定为标识资源的绝对 URL，例如，文件或目录。
Remote Provider 指定在打开客户端连接时要使用的提供程序的名称 (仅限远程数据服务)。
Remote Server 指定在打开客户端连接时要使用的服务器的路径名称 (仅限远程数据服务)。

例如，我们要创建一个MYSQL连接，首先要实现ADO Connection，其次配置连接参数，最后通过连接对数据库做各类操作。

Step 1: Set objConnection = CreateObject("ADODB.CONNECTION")
Step 2: strConnectionString = "Driver={Microsoft ODBC for Oracle};Server=" & strDBAlias & ";Uid="& strUID & ";Pwd=" & strPWD & ";" 
Step 3: objConnection.Open strConnectionString
Step 4: objConnection.cnn.CursorLocation = adUseClient
Step 5: objConnection.Execute(SQL)

封装的代码实现 -- 实现ORACEL；MYSQL；SQL；ACCESS四种数据库的连接,大家可以直接食用

Dim objConnection                          'CONNECTION对象实例 
Dim objRecordSet                                   'RECORDSET对象实例        
Dim objCommand                                '命令对象实例 
Dim strConnectionString                        '连接字符串 
' ******************************************************************** 
' 函数说明：连接数据库; 
' 参数说明：(1)strDBType（数据库类型：如ORACEL；MYSQL；SQL；ACCESS） 
'           (2)strDBAlias（数据库别名） 
'           (3)strUID（用户名） 
'           (4)strPWD（密码） 
'           (5)strIP（数据库IP地址） 
'           (6)strDataSource（数据源：仅ACCESS使用；如d:\yysc.mdb） 
' 返回结果：无 
' 调用方法: ConnectDatabase(strDBType, strDBAlias, strUID, strPWD, strIP, strDataSource) 
' ******************************************************************** 
Sub ConnectDatabase(strDBType, strDBAlias, strUID, strPWD, strIP, strDataSource) 
    Set objConnection = CreateObject("ADODB.CONNECTION")
    Select Case UCase(Trim(strDBType)) 
        Case "ORACLE" 
            strConnectionString = "Driver={Microsoft ODBC for Oracle};Server=" & strDBAlias & ";Uid="_ 
                & strUID & ";Pwd=" & strPWD & ";"                               
            objConnection.Open strConnectionString                                
        Case "MYSQL" 
            strConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & strIP & ";DB="_ 
			& strDBAlias &";Uid=" & strUID & ";Pwd=" & strPWD & ";OPTION=3;" 
            objConnection.Open strConnectionString                                                                          
        Case "SQLSERVER" 
             strConnectionString = "Provider=SQLOLEDB; SERVER=" & strIP & "; UID=" & strUID & "; PWD="_ 
                 & strPWD & "; DATABASE=" & strDBAlias & ";" 
            objConnection.Open strConnectionString                                            
        Case "ACCESS" 
            strConnectionString = "provider=Microsoft.Ace.OLEDB.12.0;data source=" & strDataSource &_ 
                ";Jet OLEDBatabase Password=" & strPWD & ";"  
            objConnection.Open strConnectionString                                                  
        Case Else 
            MsgBox "DB Format Error " & vbCrLf & "Support DB Type：ORACLE；MYSQL；SQL；ACCESS；EXCEL" 
    End Select 
    objConnection.CursorLocation = adUseClient
    If (objConnection.State = 0) Then 
        MsgBox "DB Connect Fail!" 
    End If 
End Sub 
' ******************************************************************** 
' 函数说明：关闭数据库连接; 
' 参数说明：无 
' 返回结果：无 
' 调用方法: CloseDatabase() 
' ******************************************************************** 
Sub CloseDatabase() 
    objRecordSet.Close