Pass through queries and Where condition in openForm (1 Viewer)

[Manuel Rivera]

Hi all, this is my first post in this forum.

I have been using Access for a while now, but this is the first time I have created and experimenting with a pass through query. Now I am trying to open a form based on that pt query and using the parameter WhereCondition from the OpenForm command (from another different form). The form always opens with no records when I try to use a criteria as WhereCondition in openForm.

So, my question is: Can I use criteria as WhereCondition parameter in OpenForm, when that form is based on a pass through query? As we can normally do when the form is based on a normal query?

Many thanks for your answers and bye ...

 

[Minty]

A pass-through query that returns records is simply a data source, like any other table or query.

There is no reason to not be able to use a where clause on opening the form, however, it would be more efficient to rebuild the SQL of the pass-through to include the where clause and only return the records you are interested in.

 

[theDBguy]

It might also help if you could show us what the passthrough query and the where condition look like.

 

[sonic8]

A pass-through query that returns records is simply a data source, like any other table or query.

A Pass-Through-Query is very different to other tables or queries as Access is unable to know in advance what data structure (if any!) is returned by a Pass-Through-Query.

Opening a form with a WhereCondition argument should still work. However, it will filter the full result set returned by the Pass-Through-Query locally!

@Manuel Rivera, please show the code with the WhereCondition as well as the Pass-Through-Query.

 

[Manuel Rivera]

Hi guys, many thanks for your answers ...

This is the pt query's sql:

Select r.Referencia as referencia, r.Descripcion as referenciaDescripcion,
r.Producto as producto, r.Talla as talla, r.Color as color, c.Nombre as colorNombre,
r.PrecioRefSinIva as precioRefSinIva, r.TasaIva as tasaIva, r.TasaFecha as tasaFecha, r.PrecioRefConIva as precioRefConIva,
r.TasaCambio as tasaCambio, r.Precio as precio, r.PrecioConIva as precioConIva
From Referencias r
Left Join Colores c on r.Color = c.Color

I have tried with these two where conditions:

• (1 = 1) And (referenciaDescripcion Like '%TRAJE%')
• (1 = 1) And (r.Descripcion Like '%TRAJE%') - This asks for 'r.Descripcion' in a dialog

However, when using the second one, Access prompts me for "r.Descripcion" value in a dialog

This is the command I use to open the form. I initialize the filtro variable with the where condition from above:

DoCmd.OpenForm "f_referencias_list", acNormal, , filtro

So, many thanks again and bye ...

 

[Galaxiom]

The form's Where condition is in Access not SQL Server so use the * not the %.

The Where condition doesn't know about the table names and just works on the column names.

 

[Manuel Rivera]

Hi all,

The form's Where condition is in Access not SQL Server so use the * not the %.
The Where condition doesn't know about the table names and just works on the column names.

Both statements are correct! Thanks ...

However, I definitely am going to change the pt query by a normal query because of this:

Opening a form with a WhereCondition argument should still work. However, it will filter the full result set returned by the Pass-Through-Query locally!

I have been very aware of concepts similar like the one above. But I would have never thought that applying a Where Condition, in an OpenForm command, would cause Access to bring the whole record set to the client and then apply the filter there.

So, I will definitely use a normal query here! (and stop using the pt query as I was initially)

Many thanks for all your help ...

 

[Gasman]

Never had to use a PT query, but why can you not include the WHERE criteria in that query?

 

[Minty]

As per Gasman, apply the filter in the pass-through

Select r.Referencia as referencia, r.Descripcion as referenciaDescripcion,
r.Producto as producto, r.Talla as talla, r.Color as color, c.Nombre as colorNombre,
r.PrecioRefSinIva as precioRefSinIva, r.TasaIva as tasaIva, r.TasaFecha as tasaFecha, r.PrecioRefConIva as precioRefConIva,
r.TasaCambio as tasaCambio, r.Precio as precio, r.PrecioConIva as precioConIva
From Referencias r
Left Join Colores c on r.Color = c.Color
Where Descripcion Like '%TRAJE%'

This is by far the most efficient method.

I use a reusable generic passthrough in a routine to get data from SQL Server. Search my posts for an example.

 

[Pat Hartman]

Access makes every effort to "pass through" every query. That means that it converts your query to T-SQL and sends it off. However, if you don't understand how this works, you can defeat the process. As you were doing in your case. You also have to be aware that SQL server has no knowledge of VBA so if your query includes a UDF or VBA function, Access will need to process the function AFTER the recordset is returned. If the function is in the Select clause, that won't be a problem but if the function is involved in a join or a where clause, then Access will figure out if it can split the criteria and send partial criteria to the server and then process the rest locally. Sometimes, Access must request ALL rows from ALL tables in the join.

Writing a pass through query yourself, removes the overhead of Access having to do the conversion at run time but usually, the time difference is so slim that it is inconsequential and it is just simpler to use querydefs because that means your bound forms will be updatable. Forms bound to pass through queries are not updateable. Also, I think there is some problem that prevents you from using pass through queries for subforms.

 

[Manuel Rivera]

Hi all,
many useful and interesting answers here!
@Gasman, @Minty, @Pat Hartman: points taken.
Many thanks for explaining this so well.

I like the concept of trying to use normal queries more frequently. They are more helpful to the whole developing effort and they are easier and more natural to use. I will, definitely, think about this in the future ...

 

[ebs17]

From Referencias r
Left Join Colores c on r.Color = c.Color

It's not really an exciting task either. Something like this passes Jet-SQL (Access-SQL) through to the SQL Server with index usage.

For more complex tasks, however, it makes strategic sense to let the more powerful SQL Server do the main work and only have the result fetched into the frontend.

 

[Pat Hartman]

I've been using Access with RDBMS back ends since my first Access project in the early 90's. I work as a consultant and so every client has a different environment. In the 90's clients tended to support the RDBMS that was used by their accounting or ERP application so I never knew what RDBMS I might be working with so I just stuck with querydefs. My clients tended to be large companies and so they always had DBA's and the DBA's never wanted to give me permissions even to the test region so I would have to pass all structure changes through them. In fact I might even use different BE's at the same client depending on where related data was stored. What I discovered was as long as the BE was properly normalized and bound forms were severely limited in what rows and columns they returned at one time, I didn't have performance problems. A couple of times I needed to use a view to optimize a frequent join. For some complex searches and reports, I ended up creating stored procedures. Occasionally I had batch updates to run. I did those as pass throughs whenever I could, But I always start with a plain ol' Access querydef. If it works, I'm done. I don't care that the SQL it creates is awful to look at. I just don't look at it because I don't have to

If you've never developed an Access form bound to an RDBMS BE, you cannot use "Access" methods such as filters. You use querydefs with selection criteria that preferably bring back only one main form record at a time. Users can't really update more than one record at a time so there is no reason to bring back thousands in unrestricted queries. It is far more efficient to run 100 specific select queries during the day to bring back one record each than to bring back all the tables and all the rows each time you open a form. Sometimes, the users need complex searches. In this case, I use dynamic SQL rather than a querydef and sometimes make them pass throughs since the resultset will not need to be updateable. Then I open a list type form with the results. The user can further filter if necessary and then double clicks on each record to open the update form to update it.