Class doesn't support Automation...
 

Hi,

I am trying to connect to an access 2000 db using Excel 97
and query a table in the database and paste the recordset
into a worksheet. Here is the code I have so far...

However, an error occurs at the line below saying that the
Class doesn't support automation:

Sheets(2).Range(Cells(1, 2), Cells(myCnt,
3)).CopyFromRecordset Rs

Any ideas on how to either fix or get around this
problem???

Class doesn't support Automation...
 

Nathan,

I think CopyFromRecordest was not in XL97, introduced in XL2000.

--

HTH

Bob Phillips

"Nathan Stevens" wrote in message
...
Hi,

I am trying to connect to an access 2000 db using Excel 97
and query a table in the database and paste the recordset
into a worksheet. Here is the code I have so far...

Code:

---

Sub checkup()
Dim Cn As Object, Rs As Object, Status As String
Dim mySql As String, dbFullname As String, myCnt As Long
dbFullname = "l:\Cpe\Shape\Interim\Interim.mdb"
Status = Sheets("ridc checkup").Range("G7").Value  'SQL
Variable
mySql = "SELECT ordernumber, mobilenumber " & _
    "FROM bookings WHERE " & _
    "status =" & Status & ";" 'Stack your SQL string
Status = Empty 'Clear SQL variable string

Set Cn = CreateObject("ADODB.Connection")
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
        & dbFullname & ";"  'Create DB connection

Set Rs = CreateObject("ADODB.Recordset")

With Rs
    Set .ActiveConnection = Cn
    .Source = mySql 'Pass your SQL
    .Open , , 3, 3
    myCnt = .RecordCount
    If myCnt 0 Then
        .MoveLast: .MoveFirst
          'Pull data to first sheet, cells
a1:RecordestCountRow & column 3 _
            3 fields in the sql pass
        Sheets(2).Range(Cells(1, 2), Cells(myCnt,
3)).CopyFromRecordset Rs
    End If
    Set myRng = Nothing
    .Close
End With
Cn.Close
Set Rs = Nothing: Set Cn = Nothing
End Sub


---

However, an error occurs at the line below saying that the
Class doesn't support automation:

Sheets(2).Range(Cells(1, 2), Cells(myCnt,
3)).CopyFromRecordset Rs

Any ideas on how to either fix or get around this
problem???