Does a pass-through query accept Forms!Update.. statements?

[Betacon]

Is a pass-through query (to an SQL Server) supposed to accept statements like Forms!Update!Name = Orders.Name like regular queries do to find records in the Orders table that match data on an Update form? If not, what is the best way to write an equivalent WHERE clause

Thanks.

 

[John Vinson]

Is a pass-through query (to an SQL Server) supposed to accept statements like Forms!Update!Name = Orders.Name like regular queries do to find records in the Orders table that match data on an Update form?
No.

If not, what is the best way to write an equivalent WHERE clause?

Construct the entire SQL string in code, concatenating the value in
the forms control (in quotes as necessary for text fields).

 

[John Vinson]

1. The record source is really for a subform and how do you specify a record source for a subform in code?

A Subform isn't open in its own right. You need to get to it via the
mainform, using the Name property of the Subform Control (which may
not be the same as the name of the form within that control!) E.g.

Me!subformname.Form.Recordsource = strSQL

2. It is my understanding that a pass-through query must be constructed using the query design wizard, remove the table and using the Query menu item to select pass-through query and then the query will have a link icon associated with it. How is this "pass-through" feature accomplished when you specify the query in code? My goal is, of course, to have the query (searching a large table for duplicates) execute as fast as possible.

The query design wizard is simply one of many ways to construct a SQL
string. No matter how you build it, a Query (local or passthrough) is
just a SQL string. If the query is stored in the Queries collection
(either using the user interface, or the CreateQueryDef method in
code) it will be in the collection with an icon.

 

[Betacon]

I appreciate your response. I used a statement like

Set DupQuery = dbs.CreateQueryDef("NewQueryDef", strQuery

where strQuery represents teh SQL query as a string

It did create NewQueryDef as a query among the query objects. However, it did not label it (with a green icon) as a pass-through query which the use of the wizard does and which was part of my original question. In other words, is it a pass-through query or not? I do like to get the fastest execution. Am I leaving something out

Also, while it executed my Set command listed above, it did not accept statements like

"Dim DupQuery As QueryDef" or even statements like "Dim dbs As Database" even though the examples in Help are full of these (even with Access 2000)

Any idea why

Thanks.

 

[Betacon]

I did discover the Connect statement and was able to produce the green icon for a pass-through query. The only trouble now is that I keep getting the "ODBC connection failed" error and I have about tried all permutations of DATABASE and DSN labels.