1

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

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

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:

enter image description here

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...

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.

The obvious question I'll receive on this is, why would I want to do this, I'll will open another ticket to address that, and add a link here

Community
  • 1
  • 1
David Rogers
  • 2,601
  • 4
  • 39
  • 84

1 Answers1

0

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
HackSlash
  • 4,944
  • 2
  • 18
  • 44
  • Hmm won't that break my pivot tables? Can you link a broken cube with a newly (re)created connection string? – David Rogers Nov 09 '18 at 18:01
  • You can assign a new data source to a pivot table at any time. I don't have a cube to test it with but I assume it would work the same as other data sources. Do some testing. – HackSlash Nov 09 '18 at 18:11