I have a workbook with dozens of tables that I'm currently using VBA to query and gather information and thought a SQL based query setup would be alot more efficient in gather the data I need for the worksheets. After searching I found ADO examples, is ADO the best/only solution? If so I could use a little guidance understanding some of this as it looks like ADO is used to query alot more than just excel so the examples I found differ quite a bit. I have Office 2010 Pro Plus. In the examples it looks like I need to add a reference to Microsoft ActiveX Data Objects Library. I would assume to pick the latest listed on my computer which is 6.1 but I have several 2.x versions listed. Also will this affect users that may still be running an older version of Excel.

I currently have this exampled plugged into a test sheet to see if it will at least run but fails at the rs.Open point so I figure something probably isn't correct on the Connection string info.
Public Function SQL() As Variant
    Dim wb As Workbook
    Dim cd As Worksheet
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strRangeAddress As String
    Dim strFile As String
    Dim strCon As String
    Dim strSQL As String
    
    Set wb = ThisWorkbook
    Set cd = wb.Sheets("ConfigurationData")
    
    strFile = ThisWorkbook.FullName
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
    
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    cn.Open strCon
    'strRangeAddress = Mid(ActiveWorkbook.Names.Item("cnfTableSystems").RefersToLocal, 2)
    strRangeAddress = cd.Name & cd.Range("cnfTableSystems").Address
    strSQL = "SELECT * FROM [" & strRangeAddress & "]"
    
    rs.Open strSQL, cn
    
    Debug.Print rs.GetString
    SQL = rs.GetString
End Function