Class doesn't support Automation...

  • Thread starter Thread starter Nathan Stevens
  • Start date Start date
N

Nathan Stevens

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???
 
Back
Top