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.. :)

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