Solved: ADO Error - 'Data Type mismatch in criteria expression' - Access '97

[phendrena]

Hi There,

I have the following code in an excel workbook that doesn't work - I get an error 'Data Type mismatch in criteria expression'.

Private Sub UpdateData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oConn As Object
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

On Error GoTo ErrHandler

sConnect = glob_sConnect
sSQL = "SELECT * From [ExtranetDetails]"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If oRS.EOF Then
MsgBox "No records returned.", vbCritical
Else
sSQL = "UPDATE [ExtranetDetails] " & _
"SET [ExtranetDetails].Comments = '" & Me.txtComments.Value & "' " & _
"WHERE [ExtranetDetails].UniqueID = '" & Me.txtUniqueID.Value & "' AND [ExtranetDetails].RecordID = '" & Me.txtRecordID.Value & "'"
' MsgBox sSQL
oRS.ActiveConnection.Execute sSQL

sSQL = "SELECT * From [ExtranetDetails]"
oRS.ActiveConnection.Execute sSQL
ary = oRS.GetRows
'MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
MsgBox "Record Updated", vbInformation
End If

oRS.Close
Set oRS = Nothing

Exit Sub
ErrHandler:
MsgBox "Error : " & Err.Description & " " & sSQL, vbExclamation

End Sub

As far as I can see there are no errors with the code and there shouldn't be anything wrong with the Access datatable as ALL fields, with the exception of the autonumber are TEXT fields. None of the fields within the excel userform are formatted in any specific way.

The only field that I am trying to update if the comments field, this is limited to 250 characters with the excel workbook and also the access database.

Would anyone be able to provide any suggestions?

As a side note, a version of above code does work when it references a different datatable/workbook.
Private Sub UpdateData
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oConn As Object
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

On Error GoTo ErrHandler

sConnect = glob_sConnect
sSQL = "SELECT * From 7DaySupport"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If oRS.EOF Then
MsgBox "No records returned.", vbCritical
Else
sSQL = "UPDATE [7DaySupport] " & _
"SET [7DaySupport].[7DayTeamActioned] = '" & Me.cboActioned.Value & "' " & _
"WHERE [7DaySupport].[CustomerManager] = '" & Me.txtCM.Value & "' AND [7DaySupport].[PolicyRegNo] = '" & Me.txtPolReg.Value & "'"
oRS.ActiveConnection.Execute sSQL

sSQL = "SELECT * From [7DaySupport]"
oRS.ActiveConnection.Execute sSQL
ary = oRS.GetRows
'MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
MsgBox "Record Updated", vbInformation
End If

oRS.Close
Set oRS = Nothing

Exit Sub

ErrHandler:
MsgBox "ERROR : " & sSQL, vbCritical

End Sub

Thanks,

[stanl]

Maybe I'm seeing things wrong. You have 2 Execute commands to the same Recordset, without ever dealing with the original command. Of course you are going to get an error. Ever hear of oRS Close

.02

Stan

[CreganTur]

Well... you have string data qualifiers surrounding all of your Me.Object references... are all of the fields in your table of the string data type?

[stanl]

and maybe

oRS.ActiveConnection.Execute sSQL

should be

oRS = ActiveConnection.Execute sSQL

Stan

[CreganTur]

Phendrena... why are you using late beinding to create your recordset, instead of early binding? I've seen this done with external applications when version differences could cause a problem, but never with something internal like ADO before.

Just curious about your reasoning for this.

[phendrena]

Phendrena... why are you using late beinding to create your recordset, instead of early binding? I've seen this done with external applications when version differences could cause a problem, but never with something internal like ADO before.

Just curious about your reasoning for this.Well, that went over my head tbh!

The original code was provided by xld, i've amended it to reference my database. What really gets me is that the code does work and it works fine but doesn't work for this datatable.
Now, after some messing around it now works - i've changed the following line :-

FROM :
"WHERE [ExtranetDetails].UniqueID = '" & Me.txtUniqueID.Value & "' AND [ExtranetDetails].RecordID = '" & Me.txtRecordID.Value & "'"

TO :
"WHERE [ExtranetDetails].Name LIKE '%" & Me.txtDCSearch.Value & "%' "I'd prefer to able to reference two unique fields for the update check but as the field it now references is a 16-digit random number i'm reasonably happy I won't get duplication.


Well... you have string data qualifiers surrounding all of your Me.Object references... are all of the fields in your table of the string data type?Errrm.... Well, they are all text fields in the userform and text fields in the database.

[CreganTur]

Well, that went over my head tbh!

Heh... don't worry about it. It took me a while to learn about Early/Late binding.

Binding refers to how you create, or instantiate, an object. Early binding means that when you dimension the variable, you dimension it for the specific object you want, like this:
Dim oRS As ADODB.Recordset

Late binding means that you dimension the variable as a generic object data type, and then specify the type of object as part of a Set statement later in your code, which is what you're doing. Looks like this:
Dim oRS As Object
Set oRS = CreateObject("ADODB.Recordset")


Generally, the best reason for using late binding is because you are using an external application, the version of which could differ on different computers. A common example is Excel. You could do Early Binding and reference Excel9... but if someone has a different version of Excel the code could fail, so most Devs will use Late Binding to create a generic Excel object.

Hope that makes sense:thumb

[phendrena]

Thanks Randy that explained the early/late binding nicely. :)

[phendrena]

Anyone able to shed an light on this problem please?
The database is setup as text fields and i'm not passing anything other than text to the database from the excel userform.

I've attached an example of a userform and also the database for you to look at. Currently only the code for the first tab is there as I want to make sure it all works first.

Thanks,

[hansup]

Anyone able to shed an light on this problem please?
The database is setup as text fields and i'm not passing anything other than text to the database from the excel userform.
In your database, UID is not a text field; it is autonumber.

However, in your DealerUpdate subroutine, you're building a SQL UPDATE statement which treats UID as text data type. When your code attempts to execute that UPDATE statement, Jet responds with error number -2147217913, "Data type mismatch in criteria expression".

Discard the single quotes before and after txtDealerUID.Text in your WHERE clause:

"WHERE [Dealer List Query].UID = " & Me.txtDealerUID.Text & ";"

However, I don't know if that's the only change you need to make. I'm not familiar with Excel userforms. And I can't find a control named txtDealerUID.

Good luck,
Hans

[phendrena]

In your database, UID is not a text field; it is autonumber.

However, in your DealerUpdate subroutine, you're building a SQL UPDATE statement which treats UID as text data type. When your code attempts to execute that UPDATE statement, Jet responds with error number -2147217913, "Data type mismatch in criteria expression".

Discard the single quotes before and after txtDealerUID.Text in your WHERE clause:

"WHERE [Dealer List Query].UID = " & Me.txtDealerUID.Text & ";"

However, I don't know if that's the only change you need to make. I'm not familiar with Excel userforms. And I can't find a control named txtDealerUID.

Good luck,
Hans

Many thanks for the solution Hans.
Removing the ' has indeed worked.
The control 'txtDealerUID' is a hidden field on the userform and can only be seen if you view the for in VBA.

Cheers,

Dave