SQL Recordset Converting to Access 2002 from Access 97

  • Thread starter Thread starter Angela Z.
  • Start date Start date
A

Angela Z.

Does anyone know what's wrong with the following, worked
fine in Access 97?

I'm getting a runtime error 13 Type Mismatch when I tried
to run this SQL Statement

strSQL = "SELECT * FROM Employee WHERE empid = '" &
strCurrentUser & "'"

set rs = currentdb.OpenRecordset(strSQL)

When I try to run this sql as query it runs fine but from
code it doesn't work.

I'm stumped. Thanks for any help ahead of time.

--Angela
 
I'm getting a runtime error 13 Type Mismatch when I tried
to run this SQL Statement

strSQL = "SELECT * FROM Employee WHERE empid = '" &
strCurrentUser & "'"
Click to expand...

If EmpID is a text value, then it needs proper quotes around it:

strSQL = "SELECT * FROM Employee " & vbNewLine & _
"WHERE empid = """ & strCurrentUser & """;"

If it's a numeric, then you don't:

strSQL = "SELECT * FROM Employee " & vbNewLine & _
"WHERE empid = " & strCurrentUser & ";"


The best way to debug this is to put a line like this before the
OpenRecordset call:

MsgBox strSQL, "Debugging SQL command..."

Hope that helps


Tim F
 
By default, Access 2000 and 2002 use ADO, and your code is DAO. Assuming you
added a reference to DAO, did you uncheck the reference to Microsoft ActiveX
Data Objects 2.x Library?

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset
 
Back
Top