Query V SQL Statement

[CharlesWhiteman]

If I had a Form 'FrmForecastReview' Based on a Query 'QryForecastReview' I am wondering whether i can dispense with having a query and just use SQL statement in the properties of the form to query and retrieve the data i am looking for?

If yes would this be a technically and/or faster way for Access to produce datasets?

Thanks in anticipation of your pearls of wisdom

 

[pbaldy]

Performance-wise, I don't think it would matter. The SQL in the properties would be saved as a hidden query anyway.

 

[dcobau]

I haven't tested it myself but, apparently, the query is quicker then SQL because SQL is compiled every time that it is called while the query is compiled only the first time that it is opened. So, I think, while the first time the SQL and the query will (probably) take the same time to manipulate your data, in the long run (presuming you will use it more then once) the query will be quicker.

Dave

 

[CharlesWhiteman]

Cheers guys & Thanks for the info.

 

[pbaldy]

SQL is compiled every time that it is called

Even SQL entered in the properties window, which is saved as a hidden query?

 

[The_Doc_Man]

SQL saved in properties is not necessarily compiled the same way as a query generated through the Query Pane of the Access Window.

Further, a query IS an SQL statement. The difference is that when you store the SQL statement as a query, Access looks at it and decides how best to optimize performance. Doubt me? Click onto the Query Pane and open any query in design mode. Now use the toolbar to change to SQL mode.

 

[dcobau]

I never had to find out about this, so I didn't. I offered opinions given by developers that are much better qualified then myself. So, if you need to, it is up to you to dig deeper

Dave

 

[pbaldy]

Doc Man, I'm not sure who you were directing that towards, so not sure if I need to clarify my point. I'm well aware that a query is an SQL statement. My only point was that the SQL in the properties IS saved as a query, and as such is different than SQL run from VBA. Whether the "properties" query is compiled in the same way a "regular" saved query, I don't know but would be interested to learn.

 

[The_Doc_Man]

In general, an SQL string executed via some Execute-SQL method is not subject to pre-compilation analysis. An SQL string or a query grid, if saved before use, however, will "know" how to make the query more efficient. Both stored and "on-the-fly" SQL statements are queries.

pbaldy, you weren't the target of the comment. I was fairly sure you knew the difference. But then, on a bad day, I'm not fairly sure I'm still living until the coffee kicks in.