VBA-如何从连接中清除连接字符串 [英] VBA - How to Clear a Connection String From a Connection

问题描述

我有一个函数试图从每个连接中清除每个连接字符串，它的工作方式如下:

I have a function that attempts to clear every connection string from every connection, it works as follows:

Public Sub RemovePasswordByNamePrefix()
    Dim w As Worksheet
    Dim qt As QueryTable
    Dim cn As Object
    Dim odbcCn As ODBCConnection
    Dim oledbCn As OLEDBConnection

    For Each cn In ThisWorkbook.connections
        If cn.Type = xlConnectionTypeODBC Then
            Set odbcCn = cn.ODBCConnection
            odbcCn.SavePassword = False

            odbcCn.connection = ""
            odbcCn.CommandText = ""

        ElseIf cn.Type = xlConnectionTypeOLEDB Then

            Set oledbCn = cn.OLEDBConnection
            oledbCn.SavePassword = False

            oledbCn.connection = ""
            oledbCn.CommandText = ""

        End If
    Next
End Sub

问题在于此操作失败，并出现以下错误(在OLEDB部分中，连接="):

The issue is that this fails, with the following error(at the OLEDB section, connection = ""):

运行时错误'1004'

Run time error '1004'

应用程序定义或对象定义的错误

Application defined or object-defined error

尝试通过界面执行此操作也不起作用，这有点时髦:

Attempting to do this by the interface doesn't work either, it's a little funky:

当您单击确定"按钮时，它什么也没做...就像窗口没有关闭，没有更改一样，这很奇怪.实际上，除非您在命令文本和命令字符串部分中放置某些内容(有效或无效)，否则这种相同的行为将持续存在.

When you click the "OK" button, it just doesn't do anything... like the window doesn't close, no change, it's very strange. Actually this same behavior will persist unless you put something(valid or invalid) in the command text and command string sections...

为什么会这样?有办法解决吗?就像MS将此作为隐藏"要求，并且在用户/开发人员尝试绕过它时没有记录或提供反馈.

Why is this the case? Is there a way to get around it? It's almost like MS put this as a "hidden" requirement and didn't document or provide feedback when the user/developer attempts to route around it.

我将收到的一个显而易见的问题是，为什么要这样做，我将打开另一张纸条以解决该问题，并添加一个推荐答案

试一试；删除连接，并在以后需要时重新创建.

Try instead; delete the connection and recreate later when you need it.

Public Sub RemovePasswordByNamePrefix()
    Dim w As Worksheet
    Dim qt As QueryTable
    Dim cn As Object
    Dim odbcCn As ODBCConnection
    Dim oledbCn As OLEDBConnection

    For Each cn In ThisWorkbook.Connections
        If cn.Type = xlConnectionTypeODBC Then
            cn.Delete
        ElseIf cn.Type = xlConnectionTypeOLEDB Then
            cn.Delete
        End If
    Next
End Sub

这篇关于VBA-如何从连接中清除连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！