Need to run recursive SQL statement to populate table

[MentalClinicBound]

I'm trying to run the following SQL statement (in VBA since building a query will not accept the with as clause) to populate a table in MS Access 2003.

insert into bomexp (parent, component, qty)
with data (parent, component, qty) as (
select root.no_parte, root.no_compnt,
cast(root.cantidad as float)
from mnt27 root where root.no_parte in (
select distinct exp02.no_parte from exp02 where no_packing = '1257TEBI') and root.fecha_fin is null
union all select parent.parent, component.no_compnt, cast(component.cantidad*parent.qty as float)
from data parent, mnt27 component
where parent.component = component.no_parte and component.fecha_fin is null)
select parent, component, sum(qty) as qtyPerExtended from data group by parent, component order by parent, component

This is a recursive bill of material sql statment that works in SQL server 2005, but will not work in MS Access 2003. I keep getting the error message "Run-time error '3134' Syntax error in INSERT INTO statement." Is it impossible to run an insert into statement on a with clause in MS ACCESS?

 

[JoeAtWork]

Is it impossible to run an insert into statement on a with clause in MS ACCESS

According to Access Help, the WITH clause is only used in these two cases:

CREATE INDEX Statement
WITH OWNERACCESS OPTION Declaration

So the answer is you can't use it. The Cast functions won't work either. There are many SQL options in SQL Server that are not available in Access or are different.

You should design your queries in the Query Designer, then you will know if they will work or not. If they don't work there, there isn't much point in trying to run them through VBA.