display query results into a simple table with VBA

[inoxo]

Hi,

I would like to display the result of a sql query into a simple table. My sql sentence is embedded in a VBA module.

DoCmd.OpenTable "myTable" works well, but it displays the whole table myTable. However, I would like to get the result filtered and sorted.

I have thought to DoCmd.OpenQuery but it needs the name of a stored query and does not allow a SQL sentence as a parameter.
I have also thought to DoCmd.RunSQL but it does not give the result in a table.

Could you tell me if there is something like
DoCmd.zzzzz "SELECT ... FROM myTable WHERE ... ORDER BY ..."
that displays the result the same way as with DoCmd.OpenTable "mytable"

Thanks.

 

[ajetrumpet]

Could you tell me if there is something like
DoCmd.zzzzz "SELECT ... FROM myTable WHERE ... ORDER BY ..."
that displays the result the same way as with DoCmd.OpenTable "mytable"

No, but there is...

CurrentDb().CreateQueryDef "NameOfNewQuery", "SQL statement"

 

[inoxo]

Thanks Adam. Nice idea. But then I could store a query for once and use it with parameters.

I wonder VBA cannot provide something to execute a SQL select and simply display the result on the screen, the same way as with QBE ...

 

[ajetrumpet]

Thanks Adam. Nice idea. But then I could store a query for once and use it with parameters.

You can do this method with parameters as well.

I wonder VBA cannot provide something to execute a SQL select and simply display the result on the screen, the same way as with QBE ...

No, I don't think there is. I have used DB.execute and DoCmd.RunSQL for running query commands in procedures, but they can only be action queries (Delete, Update, Append, etc...). If you are going to want to view a recordset when it is finished, I think you'll have to use CreateQueryDef.

You can't execute a query command in VBA using the SELECT statement because there would be no place to store the data retrieved from the command.

 

[inoxo]

I shall add an INTO clause in my query to save the result in a temporary table and then display the data by opening that table.

Thanks Adam.