vba access recordset field inside SQL where clause

Question

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.

What are the field types for TABLE1.A and TABLE2.A ? Are they both strings? — braX
– braX, Commented Sep 8, 2017 at 16:25
you are not using the recordset values .... you are using the strings "rs!A" and "rs!B" .... you need to insert the values of rs!A and rs!B into the SQL query string ...... look up string concatenation on the internet — jsotola
– jsotola, Commented Sep 8, 2017 at 16:34
Good lord. Just write this as a single query. Doing this by looping through and building a SQL statement is crazy! — JohnFx
– JohnFx, Commented Sep 8, 2017 at 18:09

JohnFx · Accepted Answer · 2017-09-17 21:21:11Z

3

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

edited Sep 17, 2017 at 21:21

answered Sep 8, 2017 at 18:13

JohnFx

35k19 gold badges108 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JohnFx Over a year ago

fixed. Thanks for the assist.

braX · Accepted Answer · 2017-09-08 16:46:43Z

1

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 & "'"

edited Sep 8, 2017 at 16:46

answered Sep 8, 2017 at 16:27

braX

11.9k5 gold badges24 silver badges38 bronze badges

Collectives™ on Stack Overflow

vba access recordset field inside SQL where clause

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related