CODEWITHSUNDEEP
sqlvbarecordsetmismatch
Nina
Nina

Reputation: 151

vba access recordset field inside SQL where clause

I have a DAO recordset that loops through a recordset and runs a SQL statement on another table, updating records of that table. For some reason, it doesn't like using the recordset value in the where clause of the SQL statement. I've tried everything I can think of.

strSQL = "Select A, B FROM TABLE1"
Set rs = CurrentDb.OpenRecordset(strSQL)
If Not rs.BOF and Not rs.EOF Then
 rs.MoveFirst
 While (Not rs.EOF)
  DoCmd.RunSQL "UPDATE TABLE2 SET TABLE2.B = rs!B WHERE TABLE2.A = rs!A;" 
  rs.MoveNext
 Wend
End If
rs.Close

The only trouble is the where clause. It has no problem with the set clause, even though it's accessing the value the same way. I get a datatype mismatch error on the where clause.

Upvotes: 1

Views: 1479

Answers (2)

JohnFx
JohnFx

Reputation: 34907

Just write a single query to do the whole thing and ditch the VBA code loop. The current version has security vulnerabilities, will perform badly, and is overly complicated for maintenance.

update Table2 
inner join Table1 on (Table1.A = Table2.A)
SET Table2.B = Table1.B

Upvotes: 3

braX
braX

Reputation: 11755

Try it like this:

"UPDATE TABLE2 SET TABLE2.B = " & rs!B & " WHERE TABLE2.A = " & rs!A

And if they are strings, then you need to put them in single quotes like this:

"UPDATE TABLE2 SET TABLE2.B = '" & rs!B & "' WHERE TABLE2.A = '" & rs!A & "'"

Upvotes: 1

Related Questions