CODEWITHSUNDEEP
sqlms-access
Mike - SMT
Mike - SMT

Reputation: 15226

Cannot view the SQL portion of a query in ACCESS?

I am currently working on a project of replacing our old access database queries, but on one of them I am not able to view the actual SQL View.

Does anyone know a way to force the view or to export it somehow?

Error causing problem:

The SQL statement could not be executed because it contains ambiguous outer joins.

enter image description here

Note that I can view the Design View without issue but when I right click on the tab and select SQL View is when I get the error.

I did attempt what @LeeMac mentioned below but same error occurs:

enter image description here

EDIT:

This question is not like Ambiguous Outer Joins? The OP on that question can actually see and edit their SQL. My issues is that I cannot see or edit the SQL as the SQL View wont open.

Upvotes: 1

Views: 1559

Answers (4)

Jim H
Jim H

Reputation: 1

I had this problem and the issue was that i had a subquery that calculated fields but did not actually have a table in it. for example it would calculate first and last day of last month which is 2 calculated fields, then it was the first query in a series of queries that were built off it and the last one wouldnt resolve sql as original poster indicated also gave the ambiguous join message as well as query needs input table (which was that first subquery). i put a table with 1 record in it but didnt use the record and it worked.... so it just a needs a table in it.

Upvotes: 0

Erik A
Erik A

Reputation: 32642

Access ordinarily doesn't allow you to save invalid queries, so it's strange you somehow got into this situation in the first place.

If you can copy the query, you can easily get to the SQL by changing the query to a passthrough query, either through the GUI or through VBA:

Dim q As DAO.QueryDef
Set q = CurrentDb.QueryDefs!Query1
q.Connect = "ODBC;"
Debug.Print q.SQL

Passthrough queries are not validated, so you can freely read and write anything you want as SQL in it.

Note that this is irreversible when done through VBA. You can only change it back to a normal query once you made the SQL valid again. If you do it through the GUI, you can just not save it, though.

Upvotes: 0

Albert D. Kallal
Albert D. Kallal

Reputation: 49039

Try this: hit ctrl-g, and from immediate window type in this:

saveastext acQuery,"Name of query","c:\test\mysql.txt"

Upvotes: 1

Lee Mac
Lee Mac

Reputation: 16015

Try executing the following VBA code from the Immediate Window (accessible using Ctrl+G) in the VBA IDE (open the IDE using Alt+F11):

?CurrentDb.QueryDefs("YourQuery").SQL

Replace YourQuery with the name of your query.

This should print the SQL code which comprises your query - you can then analyse the SQL to determine the cause of the error.

It's odd this error would arise when merely viewing the SQL content of the query definition.

It makes me think that the query is perhaps referencing a crosstab subquery which is actually the cause of the error, but which needs to be evaluated in order for MS Access to determine the columns available when viewing the design of the query in question.

Upvotes: 1

Related Questions