open mdb and excel with ADO

[efrat]

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

Thanks
Efrat

[Clunietp]

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

    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

[efrat]

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

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