CODEWITHSUNDEEP
ms-accessvba
Ankesh Kushwah
Ankesh Kushwah

Reputation: 161

MS Access Query will not open in design mode

I am currently supporting a MS Access project, which has a lot of queries, designed in MS Access Query design. I want to open them to see the SQL. But every time I try to open them in Design view I am getting the following error:

Invalid use of vertical bars in query expression.

Is there any way to open these queries? I need to do some enhancements in the project and for that I need to see the SQL behind these queries.

Upvotes: 0

Views: 3512

Answers (1)

HansUp
HansUp

Reputation: 97101

When a saved query will not open in Design View, you can examine its .SQL property via the DAO object model.

Here is an example from the Access Immediate window which displays the SQL statement behind my saved query, qryAddLogEntry. (Ctrl+g will take you to the Immediate window.)

? CurrentDb.QueryDefs("qryAddLogEntry").SQL
PARAMETERS some_text Text ( 255 );
INSERT INTO log_table ( log_text )
VALUES ([some_text]);

If necessary, you can also modify the query's SQL statement by changing that property.

strInsert = "PARAMETERS some_text Text ( 255 );" & vbCrLF & _
    "INSERT INTO log_table ( log_text, junk_field )" & vbCrLF & _
    "VALUES ( [some_text], 'Hello World');"
CurrentDb.QueryDefs("qryAddLogEntry").SQL = strInsert

Upvotes: 3

Related Questions