Question Possible to have a 'linked' query? (1 Viewer)

[evanscamman]

Is there any possible way to link to a query in the same way that you link to table in your back-end?

I ran into a scenario where I have to update the SQL statement of a stored query on the fly - basically a temporary stored query instead of a temporary table. (Had to do this because 6 other queries use the Temporary query as a data source.)

I won't be able to store the query in the front-end because it's gonna be read-only after deployment, and I can't find a way to link to a query in the backend.

Anybody know any tricks to accomplish this?
Am I going to need to put every one of the 6 related queries in the back-end, and open the back end in VBA to run them? Could this cause big problems in a multi-user environment?

If this isn't possible, the other alternative that I see is to use a temporary table - but this could have problems in a multi-user environment... if a 2nd user repopulated the table while the 1st was mid-task.

All ideas welcome!
Thank you,
Evan

 

[Atomic Shrimp]

How about...

Store the SQL in a memo field of a table in the back end
Some VBA in the front end reads that value, creates a query object (in memory) and does with it whatever you had planned to do if it was a hard-coded query.

Is that possible if the front end is set to read-only? - if it is, then to update the query, you'd just paste the SQL into the field in the table in the back end.

 

[evanscamman]

Mike - what you suggest is possible in a read-only frontend, but I don't think it would work for me. I am not able to use a query in memory, because I have half a dozen other queries that need to use it as part of their data source - great idea though to use a memo field... I'll have to remember that.

What about putting all related queries in the back end - and opening a connection to that database through vba? Has anybody done this?

Thank you,
Evan

 

[Banana]

Are you talking about linking to temporary table in tempdb?

If it's global temporary table, I suppose you could link to it using IN clause.

SELECT *
FROM TheTableName IN ''ODBC; blah blah";

 

[evanscamman]

Are you talking about linking to temporary table in tempdb?

No, I don't have a tempdb. I'm just trying to update the contents of a saved query - but I'm having problems because the front end is read-only, so it doesn't allow changes. And If I put the query in the backend I can't link to it like you can with a table.

 

[Banana]

Can I ask why front-end will be read-only?

As for opening database and executing, queries, this could potentially amount to having a unspilt database, essentially, along with all pains that comes with multi-user upon a unspilt database.

Can I also ask what your SQL looks like? It may be possible to not even need to change SQL on fly...

 

[evanscamman]

It's going to be a MDE. Wait.... I was assuming that meant read-only.
AHA!!!! Maybe it doesn't!??!! Banana, you may have just saved me a lot of time!

Here's why my query has to be so special: The user has a button to perform a "global edit" on all records listed on my subform. I want to allow filters on the subform, so I have to create a query that looks at the filter on the form and recreates this in a saved query. It has to be saved, because all the "global edit" update queries use it to get their data.

But... this may be all irrelevant now, if a MDE is not read-only.

Evan

 

[evanscamman]

Yes, I can create stored queries in an MDE file. I've actually never created one before (MDE), but always assumed when my database was ready for deployment as MDE is would be in a read-only state.

Banana - Thank you very much, I had everything working and was just about to trash it all and start over. You saved me many hours!

Thanks again,
Evan

 

[Banana]

Indeed. While you can't do any code or design changes to the forms and modules, MDE allows edits to the queries and tables object. (though it should go without saying that if the design changes in tables and queries make it incompatible with the forms and modules, the file will break)

Without knowing your SQL, I just want to point out that while there are legitimate reasons to alter SQL on the fly, dynamic SQL isn't always the first answer.

If you'll allow me -


Suppose we had a SQL statement where we wanted to vary filter's criteria. Let's say the filter applies to the column foo and bar:

SELECT * FROM t WHERE foo = 'somevalue' AND bar = 1;

and we want to be able to change the SQL to this:

SELECT * FROM t WHERE foo = 'othervalue' AND bar = 2;

This is a case where we don't even need to change SQL on the fly. A parameter query can be used instead.

PARAMETERS pText TEXT(255), pCode INTEGER;
SELECT * FROM t WHERE foo = [pText] AND bar = [pCode];

To avoid prompting the users with that little parameter query, we can just make a bit use of VBA:

With CurrentDb.QueryDefs("MySELECTParameteQuery")
    .Parameters("pText") = fooValue
    .Parameters("pCode") = barValue
    Set rs = .OpenRecordset dbOpenDynaset
End With

and for action queries:

With CurrentDb.QueryDefs("MyINSERTParameteQuery")
    .Parameters("pText") = fooValue
    .Parameters("pCode") = barValue
    .Execute dbFailOnError
End With

or perhaps by referencing a form control (very common for managing a report's query for instance but can be used just as well with subforms or such) instead:

SELECT * FROM t WHERE foo = [Forms]![MyCriteriaForm]![fooValue] AND bar = [Forms]![MyCriteriaForm]![barValue];

This is simpler than using parameters and requires no VBA, though it does assume that the form will be open when it is run else we get that annoying parameter box asking us to fill in value.

Probably the best example when we really need a dynamic query is when we don't know which columns the user will want to filter their criteria upon. There is no support for optional parameters as available in other RDBMS which would have made this moot, but to wit:

SELECT * FROM t WHERE [p1] = [p1Value] AND [p2] = [p2Value];

Will not work, it'd interpret the input as variables and not as a object. Suppose we entered:

p1 = foo
p1Value = 'somevalue'
p2 = bar
p2Value = 1

The resultant SQL is now:

SELECT * FROM t WHERE 'foo' = 'somevalue' AND 'bar' = 1;

Whoops. We have two strings where we wanted columns.

However, it does not necessarily mean that whenever we get stuck with parameter queries, we should run over to dynamic SQL to fix the problem- it's quite easy fix but not always the best idea. Few reasons for that:

1) It will create bloat.
2) It requires to be recompiled. Usually it's only small amount of time but can increase when SQL becomes complex and when we deal with remote tables. The penalty is significant when we have to run the same queries again and again with different criteria every time.

In such cases, we may want to look at alternative statements that we can parameterize without requiring dynamic SQLs. I should haste to add that the above does not apply for passthrough queries as well.


Anyway, the ultimate moral of this is- if you find the query to be sluggish and your front-end inexplicably bloating, it's time to look for a different way to parameterize the statements.

And I'm glad you got it all worked out, Evan.

 

[evanscamman]

Banana,

Thanks for taking the time to put together such an informative post.

I do indeed use parameter queries 99% of the time. In this case, as you said, I don't know what fields the user will choose to filter in the datasheet, and I must create a query that replicates this AND this query must be able to be included in the FROM statement for my update queries.

Unless you know of a way to access a form's filter from a parameter query, I believe this is one of the cases where dynamic SQL is necessary.
(I did try: MyForm.Filter = True as an expression in the query, but it always evaluated to True.... It was like it needed an Eval statement because it could find the text of the filter but would not process it.)

Thanks again,
Evan

 

[Banana]

If your database is split, then I would actually not bother with form filter. It's a great idea but poorly implemented and using form filter on a networked back end could bring everything to crawl, so yes, you're better off with a dynamic query in that case.

 

[evanscamman]

So, do you set AllowFilters to False on all your forms?