The Problem (Q-score 13, ranked #9th of 67 in the Access VBA archive)
The scenario as originally posted in 2010
Hi to all you mighty SQLsuperheros out there..
Can anyone rescue me from imminent disaster and ruin?
I’m working with Microsoft Access SQL. I’d like to select records in one table (table1) that don’t appear in another (table2) .. and then insert new records into table2 that are based on records in table1, as follows:
[table1]
file_index : filename
[table2]
file_index : celeb_name
I want to:
Select all records from table1 where [filename] is like aud
and whose corresponding [file_index] value does not
exist in table2 with with field [celeb_name] = ‘Audrey Hepburn’
With that selection I then want to insert a new record into [table2]
[file_index] = [table1].[file_index]
[celeb_name] = ‘Audrey Hepburn’
There is a one to many relationship between [file_index] in [table1] and [table2]
One record in [table1], to many in [table2].
Many thanks
Why community consensus is tight on this one
Across 67 Access VBA entries in the archive, the accepted answer here holds strong answer (top 25 %%) status — meaning voters are unusually aligned on the right fix.
The Verified Solution — strong answer (top 25 %%) (+13)
8-line Access VBA pattern (copy-ready)
Will this do? Obviously add some square brackets and stuff. Not too into Access myself.
INSERT INTO table2 (file_index, celeb_name)
SELECT file_index, 'Audrey Hepburn'
FROM table1
WHERE filename = 'aud'
AND file_index NOT IN (SELECT DISTINCT file_index
FROM table2
WHERE celeb_name = 'Audrey Hepburn')
When to Use It — vintage (14+ years old, pre-2013)
A top-10 Access VBA pattern — why it still holds up
Ranks #9th of 67 in the Access VBA archive. The only pattern ranked immediately above it is “is there any replacement of Access?” — compare both if you’re choosing between approaches.
What changed between 2010 and 2026
The answer is 16 years old. The Access VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.