My employer is finally beginning to look at Access2000 and later, but in trying out a few things from A-97 databases, I've run into a strange error for something quite basic. On the following statement, I am getting the "Data type conversion" error:
Dim dbs as Database
Dim rcd as Recordset
Set dbs = CurrentDb
Set rcd = dbs.OpenRecordset("TABLE_NAME")
The error occurs at the "Set rcd" statement. Same thing happens if I spell out a SELECT sql statement. What gives?
You are using the code from Access 97 which used DAO by default.
Access 2000 uses ADO by default.
Add a reference to Microsoft DAO by opening a module and using Tools-->References
Change your code to reflect that you are using DAO objects.
Dim dbs as DAO.Database
Dim rcd as DAO.Recordset
You could remove the reference to ADO and leave your code as is - but I would suggest stepping up and learning ADO so that you have more tools in the box. You could also set the reference to DAO 'higher' than ADO and leave your code as is. Then you would need to explicitly reference ADO objects that you use.