INSERT Multiuple Rows Not Working

[dbfestivalopera]

I have a query (executed from a macro) as follows:

INSERT INTO PatronsWork (PatronID)
SELECT DISTINCT EventAttendance.PatronID
FROM EventAttendance;

I get nothing inserted.  If I remove the first line in the query and switch to Datasheet view (executing the SELECT statement only), the results are exactly as expected.  So, the SELECT part is returning a non-empty set of values but nothing is inserted.  PatronsWork is an existing table having a PatronID column.  Can anyone show me my mistake here?  Many thanks if so.

[#2]

@dbfestivalopera 

Here is a list of reasons queries are non-updateable. Yours is on the list.

[#3]

CREATE TABLE EventAttendance (`PatronID` long);
INSERT INTO `EventAttendance` VALUES ('12');
INSERT INTO `EventAttendance` VALUES ('12');
select * FROM EventAttendance;

CREATE TABLE PatronsWork (`PatronID` long);

INSERT INTO PatronsWork (PatronID)
SELECT DISTINCT EventAttendance.PatronID
FROM EventAttendance;
select * from PatronsWork;

On problem on myside.

online test
http://anyoupin.cn/bsbm/stu60/sqlDao/sqlEditor_dao.html


select * FROM EventAttendance;
PatronID
12
12

INSERT INTO PatronsWork (PatronID)SELECT DISTINCT EventAttendance.PatronID FROM EventAttendance;
select * from PatronsWork;
PatronID
12

[#4]

https://www.bilibili.com/video/BV1P34y1c7Pk/?buvid=XX85E838539534AD650323D79CEE8DA100517&from_spmid=...

[#5]

modify PatronsWork table and add a Unique index (no duplicate) on PatronID field. Then you can simply use a simple select query to add to the table:

Insert Into PatronsWork (PatronID) Select PatronID From EventAttendance;

[#6]

Thanks to all who responded. The SQL I posted is actually a fairly minimal reduction of what I'm really trying to do (which involves multiple subqueries, joins, temp variables and more). The example posted was paired down to a minimum statement to illustrate the problem. I was hoping to avoid having to use VBA (which I'm not very experienced in), but maybe that's the only workable solution.

By the way, I also go no joy from the following:

SELECT EventAttendance.PatronID
INTO BrandNewTable
FROM EventAttendance;

I've never used a SELECT.INTO statement before, but it looks simple enough. However, I get no new table produced in executing the above. Could this be related?

[#7]

please provide some DDL(Data Definition Language) or some data and your expected result.
Both sqls you metioned all work well on my side.

[#8]

I would guess that the insert violates validation requirements of the destination table and you have warnings turned off. Therefore, you are not seeing any warning messages.

[#9]

Once again, thanks for the responses.

I have discovered that the following SQL works just fine if executed via RunSQL in a macro:

INSERT INTO PatronsWork (PatronID) SELECT DISTINCT EventAttendance.PatronID FROM EventAttendance

The exact same SQL does nothing (and produces no diagnostic) when executed in the query (executed via an OpenQuery in a macro). The same is true of a SELECT.INTO statement, by the way. So, the problem would seem to be associated with the query. The SQL, table properties, etc. all seem fine if used with RunSQL.

I cannot see any query properties that would suspect, but maybe there's something there that should be examined?

I'm not sure if I could just switch to RunSQL as the actual statement is a bit longer and would be hard to understand if all on one line (and may be longer than Access would even allow - haven't tried that yet but will shortly).

[#10]

SOLVED ... well not really, but now working.

I tried a Hail Mary: I deleted the query containing the problem SQL and created a new one with the same name and containing the same SQL. It worked this time. I have no idea what the earlier problem was (and almost surely never will).

Thanks to all.