• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

cn.Execute not generating UNION Query

I have a long UNION query made dynamically of up to 50 queries.  Simplified example to follow:

  ' Access VBA code
  Dim cn as new adodb.connection
  Set cn = currentproject.connection
  cn.execute "CREATE VIEW qryUNION SELECT * FROM qryRegular1 UNION SELECT * FROM qryRegular2"

I get an error with MS Access 2003 saying "Unions not allowed in a subquery."
If I copy and paste just "CREATE VIEW qryUNION SELECT * FROM qryRegular1 UNION SELECT * FROM qryRegular2" into a query I can run the UNION query no problem.

QUESTION:  How do I get my connection object to create a UNION query in MS Access using two MS Access Queries using the .execute call?  The actual SQL Statement attached below:




CREATE VIEW qry_U_BCL AS SELECT * FROM qry_S_BCL_2002_013 UNION SELECT * FROM qry_S_BCL_2003_001 UNION SELECT * FROM qry_S_BCL_2003_002 UNION SELECT * FROM qry_S_BCL_2003_003 UNION SELECT * FROM qry_S_BCL_2003_004 UNION SELECT * FROM qry_S_BCL_2003_005 UNION SELECT * FROM qry_S_BCL_2003_006 UNION SELECT * FROM qry_S_BCL_2003_007 UNION SELECT * FROM qry_S_BCL_2003_008 UNION SELECT * FROM qry_S_BCL_2003_009 UNION SELECT * FROM qry_S_BCL_2003_010 UNION SELECT * FROM qry_S_BCL_2003_011 UNION SELECT * FROM qry_S_BCL_2003_012 UNION SELECT * FROM qry_S_BCL_2003_013 UNION SELECT * FROM qry_S_BCL_2004_001 UNION SELECT * FROM qry_S_BCL_2004_002 UNION SELECT * FROM qry_S_BCL_2004_003 UNION SELECT * FROM qry_S_BCL_2004_004 UNION SELECT * FROM qry_S_BCL_2004_005 UNION SELECT * FROM qry_S_BCL_2004_006 UNION SELECT * FROM qry_S_BCL_2004_007 UNION SELECT * FROM qry_S_BCL_2004_008 UNION SELECT * FROM qry_S_BCL_2004_009 UNION SELECT * FROM qry_S_BCL_2004_010 UNION SELECT * FROM qry_S_BCL_2004_011 UNION SELECT * FROM qry_S_BCL_2004_012 UNION SELECT * FROM qry_S_BCL_2004_013 UNION SELECT * FROM qry_S_BCL_2005_001 UNION SELECT * FROM qry_S_BCL_2005_002 UNION SELECT * FROM qry_S_BCL_2005_003 UNION SELECT * FROM qry_S_BCL_2005_004 UNION SELECT * FROM qry_S_BCL_2005_005 UNION SELECT * FROM qry_S_BCL_2005_006 UNION SELECT * FROM qry_S_BCL_2005_007 UNION SELECT * FROM qry_S_BCL_2005_008 UNION SELECT * FROM qry_S_BCL_2005_009 UNION SELECT * FROM qry_S_BCL_2005_010 UNION SELECT * FROM qry_S_BCL_2005_011 UNION SELECT * FROM qry_S_BCL_2005_012 UNION SELECT * FROM qry_S_BCL_2005_013 UNION SELECT * FROM qry_S_BCL_2006_001 UNION SELECT * FROM qry_S_BCL_2006_002 UNION SELECT * FROM qry_S_BCL_2006_003 UNION SELECT * FROM qry_S_BCL_2006_004 UNION SELECT * FROM qry_S_BCL_2006_005 UNION SELECT * FROM qry_S_BCL_2006_006 UNION SELECT * FROM qry_S_BCL_2006_007 UNION SELECT * FROM qry_S_BCL_2006_008

0
access_dude
Asked:
access_dude
  • 2
1 Solution
 
ch70357Commented:
The .execute command is only for action queries (update,delete,insert, etc), it can not be used for select statements.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
cn.Execute is used to run an action query, such as update, insert, or delete.

If you wish to generate this query, then you'll need to use an adodb.recordset, and

Dim cn as new adodb.connection
Set cn = currentproject.connection

Dim rs as adodb.recordset
Set rs = New adodb.Recordset

Dim sSQL as String
sSQL = "Your query goes here"

rs.Open sSQL, cn
0
 
ch70357Commented:
Sorry, I misread, please disregard last post
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now