Sub TestADO()
    Dim con As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset
    
    Dim tbl As Table
    Dim cat As ADOX.Catalog
    
    Dim strConnection As String
    Dim strSQL As String
    Dim dDate As Date
    Dim dDateAdd As Date
    Dim i As Integer
    Dim n As Integer
    
    'create a connection string for the database
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\filepath\filename.mdb;"

    'set up the catalog
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = strConnection
    
    
    'loop through the tables and if it exists delete it
    For Each tbl In cat.Tables
        If tbl.Name = "tblDates" Then
            cat.Tables.Delete tbl.Name
        End If
    Next tbl
    
    'define and append the new table
    Set tbl = New Table
    tbl.Name = "tblDates"
    tbl.Columns.Append "dDates", adDate
    cat.Tables.Append tbl
    
    'set up the connection to the DB
    Set con = New ADODB.Connection
    con.ConnectionString = strConnection
    con.Open
    
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = con
    
    'set up value of dDate
    If Range("A1").Value < Range("A2").Value Then
        dDate = Range("A1").Value
    Else
        dDate = Range("A2").Value
    End If
    
    For i = 1 To Abs(DateDiff("d", Range("A1").Value, Range("A2").Value)) + 1
        'get right date
        dDateAdd = DateAdd("d", i - 1, dDate)
        'add string to add dates into new table
        strSQL = "INSERT INTO tblDates (dDates)" & _
                "SELECT #" & Format(dDateAdd, "mm/dd/yyyy") & "#;"
        cmd.CommandText = strSQL
        cmd.Execute
    Next i
    
    'fill a recordset as required
    strSQL = "SELECT tblDates.dDates, tblStock.stockGathered " & _
                "FROM tblDates LEFT OUTER JOIN tblStock " & _
                "ON tblDates.dDates = tblStock.ChangeDate;"
    
    Set rst = New Recordset
    cmd.CommandText = strSQL
    Set rst = cmd.Execute
    
    'write the recordset to worksheet2
    rst.MoveFirst
    n = 1
    With Worksheets(2)
        Do While Not rst.EOF
            For i = 0 To rst.Fields.Count - 1
                .Cells(n, i + 1).Value = rst.Fields(i)
                .Cells(n, i + 1).NumberFormat = "dd/mm/yyyy"
            Next i
            rst.MoveNext
            n = n + 1
        Loop
    End With
    
    
    rst.Close
    Set rst = Nothing
    'delete the temp table
    cat.Tables.Delete tbl.Name
    
    'close and empty object variables
    con.Close
    Set con = Nothing
    Set cmd = Nothing
    
    Set cat = Nothing
    Set tbl = Nothing
End Sub