473,320 Members | 1,884 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

ADO and creating Union queries


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
5 5022
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
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
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
"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

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
by: chudson007 | last post by:
I have a table, TableA with amongst other fields, a field for Qty. Qty can range from 0 to 100. How do I count the number of rows with a qty between 1 and 10, 11 and 20, 21 and 30, and so on using...
6
by: _link98 | last post by:
Problem: getting SQL0181N for queries on nicknames to remote Union-All-View. Can't see what I'm doing wrong yet, maybe someone has seen this before. Environment: UDB ESE 8.1 + FIXPAK 9A, on...
5
by: Alicia | last post by:
Hello everyone based on the data, I created a union query which produces this. SELECT ,,, 0 As ClosedCount FROM UNION SELECT ,, 0 AS OpenedCount, FROM ORDER BY , ;
1
by: CrystalDBA | last post by:
I usually design applications in SQL Server and Crystal Reports. I now need to create a crystal report on an MS Access database. I have two tables: Services: Date datetime Entry text...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.