Access question #407

Message Bookmarked

Bookmark Removed

I have a query in access, and I need to change the properties so it doesn't return any records. What code does this? Google doesn't seem to know.

― Johnny B Was Quizzical (Johnney B), Thursday, 19 January 2006 12:55 (twenty years ago)

What do you want a query that doesn't return any records? I don't think I understand the question.

― filled the fjords of my brain (kate), Thursday, 19 January 2006 13:13 (twenty years ago)

if it's a query, just add a bit somewhere in the WHERE clause "AND 1=0" or something equally absurd and they won't return any records.

― ken c (ken c), Thursday, 19 January 2006 13:18 (twenty years ago)

ooh, simply "WHERE false" works too it seems!! it doesn't complain

― ken c (ken c), Thursday, 19 January 2006 13:19 (twenty years ago)

It's a pass-thru query that makes a temp table so that ANOTHER pass-thru query can return the records. I don't want the 1st one to return records, even tho by default, when I create the query, it tells it to return records.

For query "qryD86" I was hoping it would be something like this:

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs("qryD86")
qd.ReturnsRecords = False
qd.Close

But not too much like this, obviously, because this doesn't work.

Ken, I can't do that cos the query is a make-table:

SELECT stuff
INTO temp1
FROM somewhere

so I want the query to find records, just not display anything.

― Johnny B Was Quizzical (Johnney B), Thursday, 19 January 2006 13:19 (twenty years ago)

Oh, you want it to return the records, but not display them.

― filled the fjords of my brain (kate), Thursday, 19 January 2006 13:20 (twenty years ago)

Ah, I see your problem. It won't let you just RunQuery, it wants you to OpenQuery, and then asks you what View you want. If only you could set the View to be NotVisible or something. Or else immediately run a command to Close afterwards, and set it to close your Query?

― filled the fjords of my brain (kate), Thursday, 19 January 2006 13:31 (twenty years ago)

No, cos it's a make-table, so it won't return any records or open the query anyway, just run it, and it comes back with error:

"Pass-through query with ReturnsRecords property set to True did not return any records."

― Johnny B Was Quizzical (Johnney B), Thursday, 19 January 2006 14:15 (twenty years ago)

I have spent too long on Crystal - I can't remember how to do anything in Access any more. :-(

― filled the fjords of my brain (kate), Thursday, 19 January 2006 14:16 (twenty years ago)

from the helpfile:
Note You must set the Connect property before you set the ReturnsRecords property.

does that help?

― ken c (ken c), Thursday, 19 January 2006 14:28 (twenty years ago)

actually that won't help

― ken c (ken c), Thursday, 19 January 2006 14:30 (twenty years ago)

Hmm, this is tricky . . . I'll work on it tomorrow. I might just have to ignore it all and use errorcatching, but it's a rum old job when I can't alter properties on a query programatically.

― Johnny B Was Quizzical (Johnney B), Thursday, 19 January 2006 15:58 (twenty years ago)

Can you use the TOP command in Access?

E.g. select top 0 *
into table
from other_table

― Colonel Poo (Colonel Poo), Thursday, 19 January 2006 16:15 (twenty years ago)

Yeh, but that would select nothing. I want it to select lots but display nothing, a subtle but important difference, since I want the make-table to actually, you know, make a table with records in.

― Johnny B Was Quizzical (Johnney B), Thursday, 19 January 2006 16:17 (twenty years ago)

OK, I have absolutely no idea what you mean, sorry. If you select into another table it doesn't display any records anyway. I'm confused!

― Colonel Poo (Colonel Poo), Thursday, 19 January 2006 16:20 (twenty years ago)

Well, you know make-table queries don't actually display anything, just make a table? Well, I've got one of those, but in a pass-through query. I want to tell the query to not display records, since at the moment it's expecting records, getting none, and throwing up an error message.

― Johnny B Was Quizzical (Johnney B), Thursday, 19 January 2006 16:24 (twenty years ago)

OK is this some kind of Visual Basic thing then? Cos if so, I'm no help at all, sorry.

― Colonel Poo (Colonel Poo), Thursday, 19 January 2006 16:27 (twenty years ago)

You must be logged in to post. Please either login here, or if you are not registered, you may register here.