Type mismatch error

[BoroLee]

Private Sub cmdImport_Click()

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEMP", "t:\shared\budmon.xls", Yes

Dim db As Database
Dim sqlCC As String
Dim sqlAC As String
Dim recCC As Recordset
Dim recAC As Recordset

sqlCC = "SELECT TEMP.F1, TEMP.F2 FROM TEMP LEFT JOIN tblCostCentre ON TEMP.F1 = tblCostCentre.ObjCode WHERE (((TEMP.F1) Is Not Null) AND ((tblCostCentre.ObjCode) Is Null))"
sqlAC = "SELECT TEMP.F3, TEMP.F4 FROM TEMP LEFT JOIN tblAccountCode ON TEMP.F3 = tblAccountCode.SubjCode WHERE (((TEMP.F3) Is Not Null) AND ((tblAccountCode.SubjCode) Is Null))"

Set db = CurrentDb()
Set recCC = db.OpenRecordset(sqlCC)
Set recAC = db.OpenRecordset(sqlAC)

Can anyone tell me why i'm getting a type mismatch error as the line set recCC = db.openrecordset(sqlCC)

Thanks.

 

[Mile-O]

Can anyone tell me why i'm getting a type mismatch error as the line set recCC = db.openrecordset(sqlCC)

Yes. You are using DAO code on an Access 2000 or 2002 database that has a reference set (by default) for the use of ADO.

Open a module, goto Tools -> References, Select Microsoft Data Access Objects 3.x Library and move its priority above ActiveX Data Objects (or just uncheck it).


That'll be fine.

And be more explicit:

    Dim db As DAO.Database
    Dim DAO.recCC As Recordset
    Dim DAO.recAC As Recordset

 

[BoroLee]

Thanks. it works great now.

can't beliveve i forgot to do that