Runtime 3131 Error On Insert Into + Union

[stildawn]

Hi All

I have the below which is throwing a 3131 - Syntax error in FROM clause:

CurrentDb.Execute "INSERT INTO FRT_Table SELECT dbo_Archive_FRT_Table.* FROM dbo_Archive_FRT_Table UNION SELECT Current_FRT_Table.* FROM Current_FRT_Table;", dbFailOnError

It is simply combining an archive data table with the current data table, into a combined working data table, but I can't seem to get it to work. (all tables are literally a copy and paste structure wise from each other)

Any ideas?

[June7]

Save the UNION SQL as a query object then reference that object in the Execute.

Or

run two INSERT actions

[stildawn]

Save the UNION SQL as a query object then reference that object in the Execute.

What would the code be for that? I know how to create the UNION query object, I just have never combined a execute action with a query object before.

run two INSERT actions

This I assume would be as per below:

CurrentDb.Execute "INSERT INTO FRT_Table SELECT dbo_Archive_FRT_Table.* FROM dbo_Archive_FRT_Table;", dbFailOnError
CurrentDb.Execute "INSERT INTO FRT_Table SELECT Current_FRT_Table.* FROM Current_FRT_Table;", dbFailOnError

Which of the two options would be the most efficient in processing time? Note the dbo table is up in our Azure SQL server, the other two are local within access, they are also quite large the dbo is around 9000 records, and the current one is sitting at 7500 records and both obviously will only get bigger.

[June7]

Reference query object same as table.

Some say saved query objects are faster.

Guess you will have to test.

9000 records is really quite small.

[stildawn]

Thanks, I have made the query objects version and it seems to be working.