VBA - 从 OLEDB 连接字符串中删除密码

使用VBA编程语言时，我们经常需要连接数据库以进行各种数据操作。在连接到数据库时，经常需要使用OLEDB连接字符串，并在字符串中包含用户名和密码等敏感信息以进行身份验证。然而，在某些情况下，我们可能需要从连接字符串中删除密码，以增加代码的安全性。本文将介绍如何使用VBA从OLEDB连接字符串中删除密码，并提供一个实际案例代码。

vba
ConnectionString = "Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"

vba
Sub RemovePasswordFromConnectionString()
    Dim connectionString As String
    Dim password As String
    ' 设置连接字符串
    connectionString = "Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
    ' 提取密码
    password = Mid(connectionString, InStr(connectionString, "Password=") + 9)
    password = Left(password, InStr(password, ";") - 1)
    ' 从连接字符串中删除密码
    connectionString = Replace(connectionString, "Password=" & password & ";", "")
    ' 输出删除密码后的连接字符串
    Debug.Print connectionString
End Sub

vba
Sub InsertDataIntoDatabase()
    Dim connectionString As String
    Dim password As String
    Dim conn As Object
    Dim rs As Object
    Dim sql As String
    Dim rng As Range
    Dim cell As Range
    ' 设置连接字符串
    connectionString = "Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
    ' 提取密码
    password = Mid(connectionString, InStr(connectionString, "Password=") + 9)
    password = Left(password, InStr(password, ";") - 1)
    ' 从连接字符串中删除密码
    connectionString = Replace(connectionString, "Password=" & password & ";", "")
    ' 创建连接对象
    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionString = connectionString
    conn.Open
    ' 创建记录集对象
    Set rs = CreateObject("ADODB.Recordset")
    ' 设置SQL查询语句
    sql = "INSERT INTO TableName (Column1, Column2, Column3) VALUES (?, ?, ?)"
    ' 设置数据范围
    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A2:C10")
    ' 循环插入数据
    For Each cell In rng
        ' 执行SQL查询
        rs.Open sql, conn
        rs.AddNew
        rs("Column1").Value = cell.Offset(0, 0).Value
        rs("Column2").Value = cell.Offset(0, 1).Value
        rs("Column3").Value = cell.Offset(0, 2).Value
        rs.Update
        rs.Close
    Next cell
    ' 关闭连接和记录集
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub