type mismatch error on recordset object

[Sam63]

I keep getting a type mismatch error on the following code. The problem is with the set rst statement.

Dim db As Database
Dim strTime As String
Dim rst As Recordset
Dim strMySql As String

Set db = DBEngine.Workspaces(0).OpenDatabase("P:\Pension\CheckLOG\BD Check Log.mdb&quot

strTime = Time()
If strTime > &quot;6:00:00 PM&quot; And strTime < &quot;6:30:00 PM&quot; Then
db.Execute (&quot;UPDATE tblUsers SET fldFlag = 3&quot
End If

strMySql = &quot;SELECT * FROM tblUsers WHERE fldEmployeeID = '&quot; & strBPS & &quot;'&quot;
Set rst = db.OpenRecordset(strMySql)
intMsgFlag = rst.Fields(&quot;fldFlag&quot.Value


Any ideas??? Thanks.

Sam

 

[GHolden]

Is fldEmployeeID definitely a string??

 

[Sam63]

yes, fldEmployeeID is a string. I use UPDATE queries all the time using this field and criteria. Problem is I can't create a recordset object. I keep getting the type mismatch error, even if I remove the WHERE clause.

 

[robctech]

I don't see any intialization for &quot;strBPS&quot; string variable....have you checked to see what its value is within your query string

 

[GHolden]

I've just run this and it works fine, it's exactly the same as what you've done, as far as opening the recordset goes??!

Dim db As Database
Dim strTime As String
Dim rst As Recordset
Dim strMySql As String
Dim strBPS As String

strBPS = &quot;A2&quot;

Set db = DBEngine.Workspaces(0).OpenDatabase(&quot;Z:\grahamh\db1.mdb&quot

strMySql = &quot;SELECT * FROM tblEmployee WHERE EmployeeID = '&quot; & strBPS & &quot;'&quot;

Set rst = db.OpenRecordset(strMySql)

 

[GHolden]

As robctech pointed out that's the only difference between what I tested.

 

[Sam63]

strBPS is a global variable used throughout my program and does not present an issue with any other code. Even if I assign the variable value explicitly I still get the type mismatch error.

Sam

 

[robctech]

My suggestion at this point would be the following.

strMySql = &quot;SELECT * FROM tblEmployee&quot;
set rst = db.OpenRecordSet(strMySql)

see if the error still occurs...if not then

strMySql = &quot;SELECT * FROM tblEmployee WHERE EmployeeID = '1'&quot;

Set rst = db.OpenRecordset(strMySql)

see if the error still occurs...if not then it is definatley a problem with the strBPS variable...hope it helps

 

[Sam63]

I have tried these suggestions and still receive the error. Any other suggestions?

Sam

 

[robctech]

do you recieve the error trying it all 3 ways?

 

[Sam63]

yes, all three ways. I'm thinking the type mismatch has something to do with the recordset variable.

 

[robctech]

yes...definatly it has something to do with the init of the rst, what version are you using and what data access components....try to help if I can

 

[Sam63]

I did some research on MSDN. Apparently, when you use ADO and DAO recordset objects in the same application you get this error. You have to specify in the declaration of the recordset variable which type you are using.

i.e.

Dim adoRS as ADODB.Recordset
Dim daoRS as DAO.Recordset

*** see article ID Q181542


I declared the recordset variable as DAO.Recordset and I don't get the error.

Everyone thanks for your advice. The feedback helped me narrow the issue.

Sam