way to loop through all ADODB connections in a workbook

[mbpmbp]

Hi everyone,

I'm trying to operate a macro that loops through all connections strings in a workbook and defines them to their base form as defined in "DBPath" range below. Is there any way to do this? I get a run time error '13' type mismatch with what I'm currently trying below. Any help would be much appreciated!

Sub ADODBconnectionreset()


Dim cn As ADODB.Connection
Dim DBpath As String


'updates connection path to path defined in DBPath to keep workbook compatible across multiple versions of Excel



DBpath = ThisWorkbook.Worksheets("ProjectDetails").Range("DBPath").Value
For Each cn In ThisWorkbook.Connections

cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Mode=Read; Data Source=" & DBpath & ";"

Next cn




End Sub

 

[RoryA]

The Workbook.Connections collection contains WorkbookConnection objects, not ADODB.Connection objects.

 

[anvg]

Hi
Try

    Dim cn As WorkbookConnection
    Dim olecn As OLEDBConnection
    For Each cn In ThisWorkbook.Connections
        If cn.Type = xlConnectionTypeOLEDB Then
            Set olecn = cn.OLEDBConnection
            olecn.Connection = "Provider=Microsoft.ACE.OLEDB.12.0; Mode=Read; Data Source=" & DBpath & ";"
        End If
    Next

Regards,

 

[mbpmbp]

Thanks so much for your help!

I'm getting a run time error 1004 application-defined or object-defined error on the line:

olecn.Connection = "Provider=Microsoft.ACE.OLEDB.12.0; Mode=Read; Data Source=" & DBpath & ";"

Any ideas around that?

 

[anvg]

Sorry, I tested my code and received some mistake. It is needed to add "OLEDB;" instruction to a connection string. Try with

olecn.Connection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0; Mode=Read; Data Source=" & DBpath & ";"

It works
Regards,

 

[mbpmbp]

Yes, it works great now, thanks so much!

The next issue I'm running into is that it still wants to rewrite the connections when I run another macro saying that the the data file is not found and asking me to connect to the exact same file in the exact same path instead. Any ideas around this one?

 

[mbpmbp]

Sorry, my mistake! Everything actually works great now.

Thanks again!