"BillCo" <co**********@gmail.comwrote in message
<11**********************@n67g2000cwd.googlegroups .com>:
I've encountered a problem while using ADO to save query objects.
Union queries created normally (via the interface) appear in adox
catelog.procedures rather than catelog.views. This is reasonably well
documented and I can live with it.
The below seems to be the only way to add queries to a database using
ADO:
Set cmd = New ADODB.Command
cmd.CommandType = adCmdText
cmd.CommandText = rst!SQL
cat.Views.Append rst!QueryName, cmd
It does a fine job! But, the problem occurs when it comes to a union
query. The union query appears to be created as a stored procedure
only - i.e. not visible from the database window. Unfortunitely it
needs to be a fully formed access query object.
I've come across one or two cases of people with the same problem,
but no solution - any takers? I'd hate to have to introduce DAO to
the application.
Any suggestions welcome :)
I've only played a little with this, but in my (very little)
experience, this is what happens in the 2000 version. The "queries"
created through ADO/ADOX aren't visible, neither are they accessible
through the user interface. They are only avaiable using ADO. In
later versions, I've found the different views/procedures I've
created this way to be both visible and available for usage through
the interface.
For instance, I ran your code, with a simple union in Acccess 2003,
and it became an ordinary, visible union query, but in the 2000
version, the information in the first paragraph here
http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx
applies.
BTW - if you wish to create "queries" through ADO, the following
would usually also do the trick, though, with the same limitations
currentproject.connection.execute _
"CREATE PROC myUnion AS " & _
"SELECT * FROM A UNION " & _
"SELECT * FROM B"
(both procedures and views can be created like this)
So my guess is that you're using Access 2000, and if so, then you
should probably switch to DAO if you have to create "queries" through
code that should be available through the user interface.
My *guess,* is that this is one of the issues that wasn't completely
"finished" when Access 2000 was released.
--
Roy-Vidar