ddl for union queries

[Aaron Haspel]

Greetings Access gurus.

I have clients with Access databases in the field that I need to update
-- new tables, new indices, new queries, the works. Since these clients
may have only the Access runtime, automation isn't an option for me; I
need to rely on DDL (through Oledb at the moment but if necessary I
could use something else). My DDL works fine with the exception of
views (queries) that have union clauses. Any DDL of the form

CREATE VIEW ... SELECT ... UNION SELECT ...

produces a "union operation is not supported in subqueries" error. The
queries themselves are valid; I can create them in the query designer,
or programmaticall y in an Access module, and they save and run just
fine. It's only DDL that's giving me the problem.

Any suggestions or workarounds?

Regards,
Aaron

[Chris2]

Re: ddl for union queries


"Aaron Haspel" <ahaspel@gmail. com> wrote in message
news:1107477839 .463762.84940@f 14g2000cwb.goog legroups.com...[color=blue]
> Greetings Access gurus.
>
> I have clients with Access databases in the field that I need to[/color]
update[color=blue]
> -- new tables, new indices, new queries, the works. Since these[/color]
clients[color=blue]
> may have only the Access runtime, automation isn't an option for me;[/color]
I[color=blue]
> need to rely on DDL (through Oledb at the moment but if necessary I
> could use something else). My DDL works fine with the exception of
> views (queries) that have union clauses. Any DDL of the form[/color]

Just the CREATE VIEWs with queries that have UNION clauses? Really?
That's pretty wild. :/ I'd have thought you'd have trouble with *any*
CREATE VIEW.

[color=blue]
>
> CREATE VIEW ... SELECT ... UNION SELECT ...[/color]

Aaron Haspel,

That's becasue CREATE VIEW is not supported by JET.

Check JETSQL40.CHM

Type "view" in the index field. Select "View keyworld"

"Note The Microsoft Jet database engine does not support the use of
CREATE VIEW, [...]."

[color=blue]
>
> Any suggestions or workarounds?[/color]

The closest is to just use a QueryDef.


Sincerely,

Chris O.

[Aaron Haspel]

Re: ddl for union queries

Chris--

Thanks for the reply. I know the docs say that Jet does not support
"CREATE VIEW" and I was surprised myself. But nevertheless a simple
"CREATE VIEW" statement will execute correctly through OleDb against at
least an Access 2000 db or higher.

As I was saying, I can't use a QueryDef, much as I would like to,
because that requires automating the Access object, right? Since some
of my clients have only the runtime I can't take that approach, if I
understand this correctly.

Aaron