Using ADO, loop through an Access database noting Table Names and Record Counts

I can do this using DAO but not ADO, and I prefer doing it with ADO.  I want to go through an existing Access database, grab each table name and a count of its records, and then export that data to an Excel sheet.

I've included the incomplete code that I'm using.  I'm having trouble with:

1) Actually looping through each table, the code there is very incomplete
2) Properly writing the data to Excel using ADO
3) I'm not sure I've even created the Excel sheet correctly using ADO    

Any feedback, help, and/or corrections would be greatly appreciated.

  Dim cnn As ADODB.Connection
  Dim rst As ADODB.Recordset
  Dim sSQL As String
  Dim iTable as Integer
 
 Excel Variables
  Dim xlApp As Object
  Dim xlWb As Object
  Dim xlWs As Object
 
 Create an instance of Excel and add a workbook
  Set xlApp = CreateObject("Excel.Application")
  Set xlWb = xlApp.Workbooks.Add
  Set xlWs = xlWb.Worksheets("Sheet1")
 
 Display Excel and give the user control of Excels lifetime
  xlApp.Visible = True
  xlApp.UserControl = True
 
  Set cnn = CurrentProject.Connection
  Set rst = New ADODB.Recordset
  iTable = 1
 
  For Each Table in the Database that doest start with "~" or "MSYS"
    sSQL = "SELECT Count(*) As TotalRecords FROM " & DatabaseTable
    rst.Open sSQL, cnn, adOpenStatic, adLockOptimistic
    xlWs.Cells(iTable, 1).Value = the Table Name (in Column A)
    xlWs.Cells(iTable, 2).Value = the the Number of Records (rst!TotalRecords, in Column B)
    iTable = iTable + 1
  Next Table
 
  rst.Close
  cnn.Close
  Set rst = Nothing
  Set cnn = Nothing
  
  Set xlWs = Nothing
  Set xlWb = Nothing
  Set xlApp = Nothing

Select allOpen in new window