79183

VBA - How to Clear a Connection String From a Connection

Question:

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

Recommend

  • android google plush intregation in my android app gives android.content.ActivityNotFoundException
  • reflect.Set slice-of-structs value to a struct, without type assertion (because it's unknown)
  • How can I merge several hashes into one hash in Perl?
  • Savon create matched XML pattern
  • mongodb DAO sets all attributes to null before save()
  • Compare different ranges with bracket notation
  • laravel view with a variable
  • Macro to convert equations into images in Powerpoint 2010
  • Script for finding scheduled task wizards task info
  • VB Net - How do I use the Dim value of a code to Abbreviate it small?
  • Issues-parsing data to the php and jsons
  • vba excel how to check if cell not empty and color the entire row?
  • lessen loading or processing Time in VBA
  • Compiler agnostic runtime type information?
  • VBA - How to Clear a Connection String From a Connection
  • If Image contains specific color then
  • Is it possible to raise events on a subform's form, when that subform is bound to a table?
  • Searching IMAP inbox for messages from a specefic sender and use of wild cards?
  • How to pass objects to methods in other classes in VB6
  • jQuery on select show div
  • dbExpress how to show result from query?
  • Insert multiple row using the same MySQL connection?
  • How to get all forms in a VB.net (VS08) project in an array?
  • ms project vba associate task with row
  • CSV parse with IFS bash : choose “;” as the separator
  • log4net adding full stack trace to database table via exception parameter
  • Does Go allow specification of an interface for a map with particular key type?
  • XML file with DTD displays fine in web browser after invalid change
  • VBA Select from dropdown in Internet Explorer
  • Suppress comparison always true warning for Macros?
  • VBA, TRIM part of a Path
  • Muliple upload php script and store in mysql database
  • VBA: How can I keep only the date values from a string?
  • Add apostrophe character to Excel export with OleDB connection
  • Get highest value from a file using mSL and mIRC
  • ExecuteNonQuery not working in C#
  • Excel 2007: Format of email address from Outlook 2007
  • How to create a custom Powershell operator?
  • Timeout a query
  • Julia: How to give multiple workers access to functions that are 'include(…)' into a modul