SQL Query - WHERE clause (1 Viewer)

[Juett]

Hi everyone,

I have a basic query that references a form's value in the WHERE clause. I'd like to try and turn this query into a pass-through query as I'm looking at moving my tables to an online SQL server. I'm aware that pass-through queries cannot utilise form values in the traditional way but I can't seem to figure out the way around this issue.

The current query is:

SELECT Company.[Company Name], Contacts.[First Name], Contacts.[Last Name], Contacts.[E-mail Address], Contacts.[Job Title], Contacts.Telephone
FROM Company INNER JOIN Contacts ON Company.ID = Contacts.[Company ID]
WHERE (((TbleCompany.[ID])=[Forms]![FrmCompany]![ID]));

I then have a sub from populate based off of this query to display associated contacts with the individual company (parent form).

My question: How do I get a pass-through query to work like this? How do I replace (((TbleCompany.[ID])=[Forms]![FrmCompany]![ID])); with something that the pass-through SQL query can work with once the tables are server side?

Thanks very much

 

[theDBguy]

One way is to manipulate the SQL statement of the query using code before running it.

 

[Minty]

I have a generic pass through query routine that I pass any SQL string I need to, it has a Boolean flag to indicate if it returns records or simply is an action event.

You set your subform recordsource to the generic query.
If you need to do something with multiple pass throughs, very simply store another pass-through and set the querydef.Sql property of it as required.
( I normally have 3 in my databases, one for on the fly queries, one for reports , and one for generic exports to Excel, in case they might cross over in purpose/use)

 

[Juett]

Is there an example of some of this that I can see? I'm entirely new to server SQL and these types of query, which is why I think I've found the solution to this so evasive.

 

[Minty]

Have a read here

Dynamic Parameters on Stored Procedures in MS Access query

I read that we cannot reference a field from a form to enter a parameter within a pass-through query that references a stored procedure. However, could one of those parameters be a dynamic date? For example, below is what I have {call dbo.RPT_storedproc1 ( 'code1;code2', NULL, NULL...

www.access-programmers.co.uk

 

[cheekybuddha]

Be aware that pass-through queries are read-only.

 

[Juett]

Yes - I hadn't realised that pass-through queries were read only. I'll need to read up on server side tables I think!

 

[cheekybuddha]

For most purposes, using linked tables is fine - as long as you are careful to use appropriate WHERE clauses or filters when binding to forms.

If you need something based on a query with complicated joins (eg report) then create a view on the server and link that as a linked table.