By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 2,439 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,678 IT Pros & Developers. It's quick & easy.

ADO and creating Union queries

P: n/a

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 :)

Dec 14 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
How about something like this?

Set cmd = New ADODB.Command
cmd.CommandType = adCmdText
cmd.CommandText = "Insert Into tblx "
cmd.CommandText = "Select * from tbla Union All "
cmd.CommandText = "Select * from tblb Union All "
cmd.CommandText = "Select * from tblc Union All "
cmd.CommandText = "Select * from tbld"
cmd.Execute

This assumes that tbla,b,d,d,x all have the same number of
fields/datatypes... --just an example, but this should run fine.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Dec 14 '06 #2

P: n/a
Whoops! I forgot something:

Set cmd = New ADODB.Command
cmd.CommandType = adCmdText
cmd.CommandText = "Insert Into tblx "
cmd.CommandText = cmd.CommandText & "Select * from tbla Union All "
cmd.CommandText = cmd.CommandText & "Select * from tblb Union All "
cmd.CommandText = cmd.CommandText & "Select * from tblc Union All "
cmd.CommandText = cmd.CommandText & "Select * from tbld"
cmd.Execute
I meant to use += but that only works in DotNet.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Dec 14 '06 #3

P: n/a
Thanks Rich,
>
I meant to use += but that only works in DotNet.
I learned my basic programming skills in Java and the lack of a +=
function still hurts after 2 years of vba!

I think you may have missed my point a little, the idea isn't so much
to execute the query as it is to save it as part of the MS access (A2K)
query collection. In DAO you would use

Dim qry As New DAO.QueryDef
qry.SQL = rst!SQL
qry.Name = rst!QueryName
CurrentDb.QueryDefs.Append qry

rst here is an ADO recordset with settings for queries to be applied to
a local database. rst!SQL here contains the full SQL text to be applied
and rst!QueryName has the name. The equivalent code in ADO is:

Set cmd = New ADODB.Command
cmd.CommandType = adCmdText
cmd.CommandText = rst!SQL
cat.Views.Append rst!QueryName, cmd

But this only creates a query object in Access if the text of the SQL
forms a Select Query. ADO seems to regard Union queries as stored
procedures and will save the query in the catelog.procedures
collection. Meaning it will not be visible in the database window.

I've toyed with the idea of modifying the MSysObjects table post update
to rectify the entry for the query, but would rather not go down that
road if at all possible.

Any ideas?

Dec 15 '06 #4

P: n/a
"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
Dec 15 '06 #5

P: n/a

Cheers Roy,

Yeah - feared that was the case... I've plugged up the programming hole
with a DAO bit, but I was hoping to the keep the app strictly ADO.
shame.

Thanks for all your help and advice

Bill

On Dec 15, 10:01 am, RoyVidar <roy_vidarNOS...@yahoo.nowrote:
"BillCo" <coleman.b...@gmail.comwrote in message<1166110842.422540.169...@n67g2000cwd.googl egroups.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 herehttp://msdn2.microsoft.com/en-us/library/aa140021(office.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
Dec 15 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.