SQL in an Access module public sub (1 Viewer)


New member
Local time
Today, 03:08
Jan 31, 2022
I would like to create an SQL statement in one public sub, to be called from another public sub, all of this in an Access module. I have 40,000 records in table "A" and approximately 2,500 of those records have a wrong value in one of the fields of its record.

In table "B", the correct value exists. In table "B" the key field is the part number field.

In table "A" the 2,500 records also have the same part number data that is in table "B". Table "B" has the correct value needed for the field in table "A". I want this correct value in table "B" to be used to update the wrong value in table "A" by joining on part number in table "B".

How do I write the SQL that joins table "A" and "B" for updating the value field in table "A" from the value field in table "B"? In narrative form:

Update value field in table "A" with value field in table B where part number in table "A" equals the part number in table "B".

All of this in MS Access modules/public subs.


I’m here to help
Staff member
Local time
Today, 03:08
Oct 29, 2018
Hi. Why do you need to store the same value twice in separate tables? Just curious...

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:08
Feb 19, 2002
When you don't know how to write the SQL, start with the QBE and draw it. If you insist on having embedded SQL rather than using a querydef, then switch the querydef to SQL view and copy the SQL and paste it into your VBA.

Don't forget, querydefs can accept variables. In my opinion, static SQL belongs in a querydef. Dynamic SQL where the select or where clause changes depending on logic is built in VBA. For example most of my forms use queries with one or two arguments as the RecordSource for a form. These are stored as querydefs and they refer to form controls to obtain the value needed to pull up the record the user wants to see.

Select .. From ... Where SomeField = Forms!myform!txtSomeField;

However, my search forms which can be very complex since there might be 20 options are build on the fly depending on what options the user chooses. So, if he is looking for males between 40 and 60 who live in New Haven and are divorced, that where clause is built on the fly so I don't have to have 20 optional options in a querydef. In this case, I generally save everything except the Where clause as a querydef and in my VBA the sql string starts with
strSQL = "Select qryBasicSearch.* from qryBasicSearch Where " & strWhere


Registered User.
Local time
Today, 20:08
Aug 7, 2017
Assuming tables are essentially as shown:

Then SQL is this
UPDATE TableA INNER JOIN TableB ON TableA.PartNo = TableB.PartNo SET TableA.IncorrectValue = [CorrectValue];
That will correct your errors.

Users who are viewing this thread

Top Bottom