Forum Discussion
INSERT Multiuple Rows Not Working
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...
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.
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).
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).
- 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.