Solved Access stops responding when I open a query in design view (1 Viewer)

StevenSkub

New member
Local time
Today, 06:48
Joined
Sep 17, 2020
Messages
8
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

Wino Moderator
Staff member
Local time
Today, 03:48
Joined
Aug 30, 2003
Messages
36,118
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

New member
Local time
Today, 06:48
Joined
Sep 17, 2020
Messages
8
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.

1600370784107.png
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:48
Joined
Aug 30, 2003
Messages
36,118
Nothing to forgive, we all start at the same place. ;)

It's here:


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

StevenSkub

New member
Local time
Today, 06:48
Joined
Sep 17, 2020
Messages
8
I think that returned what I was looking for! Thank you so much!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:48
Joined
Aug 30, 2003
Messages
36,118
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

New member
Local time
Today, 06:48
Joined
Sep 17, 2020
Messages
8
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

Lifelong Learner
Local time
Today, 03:48
Joined
Mar 14, 2017
Messages
8,738
Thoughts:
- post that query's SQL you've discovered, here, in case anyone can spot anything diagnostic
 

StevenSkub

New member
Local time
Today, 06:48
Joined
Sep 17, 2020
Messages
8
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

Lifelong Learner
Local time
Today, 03:48
Joined
Mar 14, 2017
Messages
8,738
What's the sql for Query_Name?
 

StevenSkub

New member
Local time
Today, 06:48
Joined
Sep 17, 2020
Messages
8
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

Lifelong Learner
Local time
Today, 03:48
Joined
Mar 14, 2017
Messages
8,738
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

New member
Local time
Today, 06:48
Joined
Sep 17, 2020
Messages
8
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

Lifelong Learner
Local time
Today, 03:48
Joined
Mar 14, 2017
Messages
8,738
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:

Code:
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.
 

Users who are viewing this thread

Top Bottom