open mdb and excel with ADO

Hi All
How can i open mdb files and excel
with ADO (i dont want to pen excel with
Automation)

Thanks
Efrat

Shalom Efrat! :)

Here is how to connect to an Excel file via ADO:

Uses ADO 2.1 and ADO EXT 2.1 for DDL & Security

Code:

---

    Dim cn As Connection
    Dim ax As ADOX.Catalog
    Dim tb As ADOX.Table
   
    Set cn = New Connection
    Set ax = New Catalog
   
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test4.xls;Extended Properties=Excel 8.0;"
   
    ax.ActiveConnection = cn
   
    For Each tb In ax.Tables
        Debug.Print tb.Name
    Next tb

---

To open a MS Access MDB, change your connection object to:

Access 2000 & 97 (fast with 2000, slow with 97)
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Nwind2k.mdb"

Access 97 (fast, but 97 only)
cn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=Nwind.mdb"

Lehitraot!!

Tom

Neshikot ,Tomchuk, Toda
NO one will understand..
Its new driver.. :)

------------------