Solved Access stops responding when I open a query in design view

[StevenSkub]

I've been trying to edit an old query that I did not design but Access stops responding when I right click it and select "design view." SQL view is not listed as an option.

I have seen some people say to retrieve the SQL code using something like this in a standard module:

Function qSQL() As String
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("NameOfYourQuery")
qSQL = qdf.SQL
End Function

but I am new to Access and am not sure how to do this.

Any help would be very much appreciated!

 

[pbaldy]

You're trying to get the SQL? If you've got that in a standard module, type this into the Immediate window (in the VBA editor) and hit enter:

?qSQL

 

[StevenSkub]

You're trying to get the SQL? If you've got that in a standard module, type this into the Immediate window (in the VBA editor) and hit enter:

?qSQL

Yeah I'd like to get the SQL. This is what I'm looking at. Forgive me, I only started using Access recently, but what does the "Immediate window" refer to? Thank you.

 

[pbaldy]

Nothing to forgive, we all start at the same place.

It's here:

Debugging

If you're not seeing it, try View/Immediate Window or Ctl-G.

 

[StevenSkub]

I think that returned what I was looking for! Thank you so much!

 

[pbaldy]

Happy to help! It is odd that Access is locking up when you try to open it. Is there anything odd in the SQL? Or is it perhaps a passthrough that can't connect to a server or something?

 

[StevenSkub]

I'm not sure. It's a crosstab query and it only uses "SELECT" "INTO" and "FROM"

Access locked up again when I tried to open the query it pulls from in design view. It also only pulls from one query and one table.

I wanted to make a new query based on this old one just with changed criteria in a few of the queries that I know are the ones that affect it. For now, I'm just going to change the criteria in those queries and save making a new query (and ones it would have to pull from) for a later day. It's kind of annoying since it would be nice to have this old query for the one purpose it had and new one for this slightly different purpose but it's good enough for now.

 

[Isaac]

Thoughts:
- post that query's SQL you've discovered, here, in case anyone can spot anything diagnostic

 

[StevenSkub]

Thoughts:
- post that query's SQL you've discovered, here, in case anyone can spot anything diagnostic

Yeah here it is (with some changed names). And sorry, the original query was a make table query, the one it pulls from is a crosstab query:

SELECT Query_Name.* INTO Table_Name
FROM Query_Name;

 

[Isaac]

What's the sql for Query_Name?

 

[StevenSkub]

This is the sql for Query_Name, definitely more complicated:

TRANSFORM Count(Query_Name2.Field1) AS CountOfField1
SELECT Query_Name2.Field2
FROM Query_Name2
GROUP BY Query_Name2.Field2
PIVOT Query_Name2.Field3;

 

[Isaac]

I see ... So there is one more underlying query! Query_Name2

I guess the main thing I wanted to check was if there was any VBA functions being performed in any of the underlying queries/layers of this, to check if that is what's slowing it down. It's one quick way to check "why is this query which I'm unfamiliar with, performing so slowly?" Normally this problem would present when actually running the query, but I seem to remember experiencing it at least once in cases where all I wanted to do was view the Design of the query which contained some VBA function which was problematic - couldn't compile, bad parameter type, can't remember what it was.

If the slowing occurs precisely when trying to go to design view, it's also possible that there is some naming issue..i.e., something got renamed (column, query, etc) that one of the objects depends on, and the QBE grid (query by example) is struggling to render that. There is a short list of things that the QBE can't actually render but memory fails on what they are at the moment. Usually it just tells you right away.

I don't use crosstab queries, so I guess that's the extent of my thoughts. Glad you found a workaround for the moment. Good luck with everything!

 

[StevenSkub]

Thank you!

Maybe one day I'll find the time to go through it all. I quick looked at the SQL for Query_Name2 (which I can view in design view) and it only has "SELECT" "FROM" "INNER JOIN" and "WHERE"

There are a lot of WHERE conditions but no more queries! Only tables now.

 

[Isaac]

and it only has "SELECT" "FROM" "INNER JOIN" and "WHERE"

Well, if there were any VBA functions, they would likely be in the part you left out, like this:

select
    VBAFunction([ColumnName]) as [ColumnAlias],
    VBAFunction2([ColumnName2]) as [ColumnAlias2],
     VBAFunction as [ColumnAlias3]
from
    TableName
where
........

Anyway, I'm probably barking up the wrong tree. It's like so many things right? Someday when there's more time! I hear ya.