ADO and Excel

[MrShickadance]

I'm trying to get ADO to work with Excel tables. the following code represents my connetion open:

Call cnADO.Open("Driver={Microsoft Excel Driver (*.xls)};" & _
"DriverId=790;" & _
"DBQ=" & strDbName & ";")

(Using the 97/2000 ODBC driver)

Now, that all works fine, but when I go to pull the data in like so:

Call rsResult.Open("SELECT * FROM xl_sheet, cnADO)

xl_sheet is a sheet in the xls file

it blows up with this msg:

[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'xl_sheet'. Make sure the object exists and that you spell its name and the path name correctly.

I would be greatful of any insight to this problem.

thanks

[Clunietp]

This does NOT use the ODBC driver, it uses pure ADO

    'uses ADO 2.x, ADO 2.x for DDL and Security
    Dim ax As ADOX.Catalog
    Dim cn As ADODB.Connection
    Dim rs As Recordset
        
    Dim I As Long
    Dim strTemp As String
    
    Dim tbl As ADOX.Table
   
    Set cn = New Connection
    Set ax = New Catalog
    
    'open connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test4.xls;Extended Properties=Excel 8.0"
 
    ax.ActiveConnection = cn
    
    'loop thru tables
    For Each tbl In ax.Tables
    
        Set rs = cn.Execute("Select * from [" & tbl.Name & "]")
        
        'display all table names
        Debug.Print "TABLE:  " & tbl.Name & vbCrLf & "========================" & vbCrLf
        
        'display all field names
        For I = 0 To rs.Fields.Count - 1
            strTemp = strTemp & rs.Fields(I).Name & vbTab
        Next I
        
        Debug.Print vbTab & vbTab & strTemp
        strTemp = ""
        
        'display all values
        Do Until rs.EOF = True
            For I = 0 To rs.Fields.Count - 1
                strTemp = strTemp & rs.Fields(I).Value & vbTab & vbTab
            Next I
            
            strTemp = strTemp & vbCrLf
            rs.MoveNext
        Loop
        
        Debug.Print strTemp
    Next tbl