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 = ""):<blockquote>
Run time error '1004'
Application defined or object-defined error</blockquote>
Attempting to do this by the interface doesn't work either, it's a little funky:
<a href="https://i.stack.imgur.com/tfPUw.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/tfPUw.png" data-original="https://i.stack.imgur.com/tfPUw.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>
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.
<em>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 <a href="https://stackoverflow.com/questions/53092897/vba-remove-password-from-oledb-connection-string" rel="nofollow">link here</a></em>Answer1:
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