title: How to loop through ADODB Connections and list Table Name, Field Name, and Field Type?
body: <p>After doing a bit of Googling, I was able to I put together the following scrip, which lists Field Names and Data Types, but not Table Names.  I really want to list Table Name, Field Name, and Field Type (running down one row at a time).  Also, rather than hard-coding a solution, I would really prefer to loop through connection strings, as I have a bunch of ADODB connections pointing to SQL Server.</p>
  
  <p>This was setup by clicking From Other Sources > From SQL Server > Server Name > Next . . . 
  This is how I setup all my connection strings.</p>
  
  <pre><code>Sub DataExtract()
  
  ' Create a connection object.
  Dim cnPubs As ADODB.Connection
  Set cnPubs = New ADODB.Connection
  
  ' Provide the connection string.
  Dim strConn As String
  
  'Connect to the Pubs database on the local server.
  strConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=TABLE;Data Source=SERVER"
  
  'Now open the connection.
  cnPubs.Open strConn
  
  ' Create a recordset object.
  Dim rsPubs As ADODB.Recordset
  Set rsPubs = New ADODB.Recordset
  
  i = 2
  With rsPubs
      .ActiveConnection = cnPubs
      .Open "SELECT * FROM TABLE"
       For i = 1 To .Fields.Count
           Sheet1.Cells(i, 2) = .Fields(i - 1).Name
           Sheet1.Cells(i, 3) = .Fields(i - 1).Type
       Next i
  End With
  
  cnPubs.Close
  Set rsPubs = Nothing
  Set cnPubs = Nothing
  
  End Sub
  </code></pre>
  
  <p>I'll post my feeble script, in hope that it helps.</p>