Error 3464 dao

[Serraset]

Hi,

I'm getting error 3464 while executing this code:

Sub PonerNRefsProveedores()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
sql = "SELECT * from nrefs"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sql)
DoCmd.RunSQL "update proveedores set nrefs=0"
While Not rst.EOF
DoCmd.RunSQL "update proveedores set nrefs=" & CDbl(rst!cuantos) & " where idproveedor='" & rst!Proveedor & "'"

rst.MoveNext
Wend
rst.Close


End Sub

The execution stops in: Set rst = dbs.OpenRecordset(sql)

I've checked the references, but they look ok (attached)

Does anyone know how can i solve this?

Thanks!

 

[arnelgp]

why not use an Update query:

set dbs = Currentdb
dbs.execute "update proveedores set nrefs=0"
dbs.execute "update proveedores As T1 Inner Join nrefs As T2 On T1.idproveedor = T2.Proveedor Set T1. nrefs=T2.cuantos;"

ActiveX data object is for ADODB object (ADODB.Recordset etc, and not DAO)

 

[ebs17]

About technology

... set nrefs=" & CDbl(rst!cuantos) & "...

Why use CDbl? A recordset field is type-safe, so you shouldn't have to convert it afterwards.

If, due to your region settings, the decimal separator is not a period but a comma, this must be handled explicitly because the comma is used within SQL as a separator for field enumerations.

=> Str(rst!cuantos)

 

[Serraset]

Well i don't really know about access and VB... and for what i've been told, this is an old access database... it was working time ago, but i think that with 365 version of Access it doesnt work properly

 

[CJ_London]

What is the error description for error 3464

 

[arnelgp]

add Reference in VBA:

or manually add Reference:

C:\Program Files\Common Files\Microsoft Shared\OFFICE16\ACEDAO.DLL

 

[Serraset]

What is the error description for error 3464

Data type mismatch in criteria expression

But its weird because in another computer with same code and references is working

 

[Serraset]

add Reference in VBA:

View attachment 112455

or manually add Reference:

C:\Program Files\Common Files\Microsoft Shared\OFFICE16\ACEDAO.DLL

Is actually added

 

[ebs17]

? AccessError(3464)

A data type error cannot be explained by a version change; data types have never been changed in 30 years, at most new ones have been added.
So if the programmatic basis remains the same, your data may have changed, or as I said, the set region.

The queries shown above are simpler and better, and they would have been 30 years ago.

 

[arnelgp]

have you tried the prososed query in post #2, rather than using recordset?

 

[Serraset]

have you tried the prososed query in post #2, rather than using recordset?

Do i need to change this:

Set rst = dbs.OpenRecordset(sql)
DoCmd.RunSQL "update proveedores set nrefs=0"
While Not rst.EOF
DoCmd.RunSQL "update proveedores set nrefs=" & CDbl(rst!cuantos) & " where idproveedor='" & rst!Proveedor & "'"

for this?:

set dbs = Currentdb
dbs.execute "update proveedores set nrefs=0"
dbs.execute "update proveedores As T1 Inner Join nrefs As T2 On T1.idproveedor = T2.Proveedor Set T1. nrefs=T2.cuantos;"