I am attempting to use a recordset in MS Access 2000. I have a function that I copied from an Access 97 database, that worked before: Private Function Get_Count() As Integer Dim TempSet As Recordset ' The results to return My_SQL = "SELECT Count(*) AS Record_Count " & _ "FROM DWGPS_Wrk_Hrs_Prj" Set TempSet = CurrentDb.OpenRecordset(My_SQL) Get_Max_ID = TempSet.Fields("Record_Count").Value TempSet.Close End Function ' Get_Count The variable My_SQL is a global string. The problem is that for MS Access 2000, this same code generates the error "Type mismatch". All of the help files indicate I should create a temporary variable of type Database, however the compiler does not understand that data type. What am I doing wrong? Thanks, Orias Tait

Try: Dim cDatabase as DAO.Database Dim Tempset as DAO.Recordset Set cDatabase as Currentdb Then when you try to execute the sql, use: Set Tempset = cDatabase.OpenRecordset(My_SQL) Hope that sorts it out for you Tom

Tom, I attempted to use your example, and I received the following errors: 1. Set cDatabase as Currentdb needed to be re-written as Set cDatabase = CurrentDb for it to be accepted by the code validation 2. When attempting to run the function, execution stopped at Dim cDatabase As DAO.Database with the error message: “User-defined type not defined”. Is it possible I do not have a required library function installed or something? I took all options when I upgraded to Access 2000. My function now looks like this: Private Function Get_Count() As Integer Dim cDatabase As DAO.Database Dim TempSet As DAO.Recordset Set cDatabase = CurrentDb My_SQL = "SELECT Count(*) AS Record_Count " & _ "FROM DWGPS_Wrk_Hrs_Prj" Set TempSet = cDatabase.OpenRecordset(My_SQL) Get_Max_ID = TempSet.Fields("Record_Count").Value ' Cleanup Environment TempSet.Close End Function ' Get_Count Thanks, Orias Tait

What references do you have ticked? The above works for me, and I have: 1) VBA 2) MS Access 9.0 Object Library 3) OLE Automation 4) MS ActiveX Data Objects 2.1 Library Tom

Taking your suggestion, I examined the difference in References between the 97 database and the 2000 database. What I found was the following was not referenced in the 2000 database: Microsoft Visual Basic for Applications Extensibility 5.3 After adding that, I received the same error with the code. I then re-examined the “help” entry for OpenRecordset. The first parameter, source, is supposed to be a string specifying the source of the records for the recordset. The source can be a table name, a query name or an SQL Statement that returns records. Seeing the word “String”, I decided to use the standard VB String identifier of quotation marks. Thus my call was as follows: Set TempSet = CurrentDb.OpenRecordset("My_SQL") The error returned was: The Microsoft Jet database engine cannot find the input table or query ‘My_SQL’. Make sure it exists and that its name is spelled correctly. To verify my suspicions, I hard coded the SQL into the function call: Set TempSet = CurrentDb.OpenRecordset("SELECT Count(*) AS Serial_Count FROM DWGPS_Wrk_Hrs_Prj") The error returned was: Type mismatch In 97, I was using this to dynamically create my SQL, then passing it onto OpenRecordset. In 2000, it appears that you can no longer do this. I have not attempted to dynamically create a query using the dynamically created SQL, I wanted to find out the following: 1. Am I looking at this correctly? Has the way Access is utilizing OpenRecordset changed into another undocumented feature not allowing for direct SQL? 2. Is there something I am missing, or will I have to create either a temporary query or table to use with OpenRecordset? Thanks, Orias Tait

Try Use the DAO Object Library reference instead of ADO Object Library. The Database object is a DAO object, so you have to reference the correct object library because Access 2000 uses by default the ADO object Library. Hope this will be usefull, Enrique

I added the References (individually, as they conflicted with each other): Microsoft DAO 2.5/3.51 Compatibility Library Microsoft DAO 3.5 Object Library Microsoft DAO 3.6 Object Library and recieved the same error: Type mismatch Thanks, Orias Tait