kate_y
asked on
Query builder vs query string and pass variables from a form to the query builder!
Hi, i have some comfusing questions. Let me try to explain. Sorry about the length of question. I am trying to make it clear. Please help!
--------------------------
I have one final query say called QueryF that is built upon the other two queries. I built these queries in the query builder.
QueryF’s SQL view:
Select Query1.Empl_ID, Query1.Wkhrs, Query1.Date, Query2, XX from Query2 Inner Join Query1 on Query1.X=Query2.Y
These Query1 and Query2 in the final query are based upon other queries and tables. I have 3 questions:
1. Do I just use DoCmd.RunSQL(Query1) to run the sub queries first? Cause when I tested those queries in the query builder, I had to run the basic(sub) queries first so the final query can use the result.
2. If I just use the query tables not the SQL statements, how do I pass the variables from a form to the queries’ criteria field? Say where Query1.Date Between strStartDate and strEndDate(variable names)
3. If I want to use query string instead of the query result table(Query1) for the sub queries, how can I refer those sub queries in the final query string as showed above?
--------------------------
I have one final query say called QueryF that is built upon the other two queries. I built these queries in the query builder.
QueryF’s SQL view:
Select Query1.Empl_ID, Query1.Wkhrs, Query1.Date, Query2, XX from Query2 Inner Join Query1 on Query1.X=Query2.Y
These Query1 and Query2 in the final query are based upon other queries and tables. I have 3 questions:
1. Do I just use DoCmd.RunSQL(Query1) to run the sub queries first? Cause when I tested those queries in the query builder, I had to run the basic(sub) queries first so the final query can use the result.
2. If I just use the query tables not the SQL statements, how do I pass the variables from a form to the queries’ criteria field? Say where Query1.Date Between strStartDate and strEndDate(variable names)
3. If I want to use query string instead of the query result table(Query1) for the sub queries, how can I refer those sub queries in the final query string as showed above?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry Jim...refreshed too late.
J
J
np
ASKER
Jim,
Questions #3, i meant that if i use SQL statements for thoes sub queries say Query1 and Query2, then they won't be in the Query module or Query builder. Then where do i store the results of these SQL statements? How can i use the results in the final query since i referred the query results in the final query?
Select Query1.Empl_ID, Query1.Wkhrs, Query1.Date, Query2, XX from Query2 Inner Join Query1 on Query1.X=Query2.Y
People told me it is more secure to use SQL statements in the VBA code instead of run queries. Is it true?
thanks a lot.
kate
Questions #3, i meant that if i use SQL statements for thoes sub queries say Query1 and Query2, then they won't be in the Query module or Query builder. Then where do i store the results of these SQL statements? How can i use the results in the final query since i referred the query results in the final query?
Select Query1.Empl_ID, Query1.Wkhrs, Query1.Date, Query2, XX from Query2 Inner Join Query1 on Query1.X=Query2.Y
People told me it is more secure to use SQL statements in the VBA code instead of run queries. Is it true?
thanks a lot.
kate
> if i use SQL statements for thoes sub queries say Query1 and Query2, then they won't be in the Query module or Query builder.
That would not be possible, as every object a pre-compiled (read: saved) query uses must also be a saved object.
>People told me it is more secure to use SQL statements in the VBA code instead of run queries. Is it true?
Pre-compied (saved) queries will run faster, but you can't feed it variables and it can't participate in VBA-code-like logic.
VBA coded queries will be slower, as Access has to compile them first before running them, but you can use variables, decision structures, anything your VBA skills can throw at it.
Secure? Saved queries can be edited-deleted by users, whereas code in an .mde file cannot be accessed, so in that respect yes.
You'll have to expand on your needs for security, however, because Access is one of the least secure databases on the market, and your needs may be more conducive to a more 'industrial' database such as SQL Server that would have tools specifically geared towards data security.
Hope this helps.
-Jim
That would not be possible, as every object a pre-compiled (read: saved) query uses must also be a saved object.
>People told me it is more secure to use SQL statements in the VBA code instead of run queries. Is it true?
Pre-compied (saved) queries will run faster, but you can't feed it variables and it can't participate in VBA-code-like logic.
VBA coded queries will be slower, as Access has to compile them first before running them, but you can use variables, decision structures, anything your VBA skills can throw at it.
Secure? Saved queries can be edited-deleted by users, whereas code in an .mde file cannot be accessed, so in that respect yes.
You'll have to expand on your needs for security, however, because Access is one of the least secure databases on the market, and your needs may be more conducive to a more 'industrial' database such as SQL Server that would have tools specifically geared towards data security.
Hope this helps.
-Jim
ASKER
> if i use SQL statements for thoes sub queries say Query1 and Query2, then they won't be in the Query module or Query builder.
That would not be possible, as every object a pre-compiled (read: saved) query uses must also be a saved object.
Let me try to explain again.
say Query1's SQL view is Select * from table 1. In the final query, i used Select Query1.Date......
But if i didn't build queries Query1 and QueryF in the query builder, i had a SQL strings in the VBA code
Dim x as string (old Query1)
Dim y as string (od QueryF)
x = "Select * from table 1"
DoCmd.RunSQL("x")
y = the final query, but i don't know how to refer to the Query1's result since it is now x.
That would not be possible, as every object a pre-compiled (read: saved) query uses must also be a saved object.
Let me try to explain again.
say Query1's SQL view is Select * from table 1. In the final query, i used Select Query1.Date......
But if i didn't build queries Query1 and QueryF in the query builder, i had a SQL strings in the VBA code
Dim x as string (old Query1)
Dim y as string (od QueryF)
x = "Select * from table 1"
DoCmd.RunSQL("x")
y = the final query, but i don't know how to refer to the Query1's result since it is now x.
I'm not following you, but perhaps this can be helpful...
x = "Select * from table 1"
DoCmd.RunSQL("x")
In the above case, why not just refer to table1?
Also, DoCmd.RunSQL is used to execute action (insert, update, delete) queries.
Doing this with a select query has no benefit, as you can't use the resulting recordset.
>But if i didn't build queries Query1 and QueryF in the query builder, i had a SQL strings in the VBA code
You can't string two query SQL in VBA as separate identities, but you can string together a SQL query that refers to more than one table, in any relationship you want.
Perhaps if you could explain what you want as a final query, in simple English, we can piece together a solution.
x = "Select * from table 1"
DoCmd.RunSQL("x")
In the above case, why not just refer to table1?
Also, DoCmd.RunSQL is used to execute action (insert, update, delete) queries.
Doing this with a select query has no benefit, as you can't use the resulting recordset.
>But if i didn't build queries Query1 and QueryF in the query builder, i had a SQL strings in the VBA code
You can't string two query SQL in VBA as separate identities, but you can string together a SQL query that refers to more than one table, in any relationship you want.
Perhaps if you could explain what you want as a final query, in simple English, we can piece together a solution.
ASKER
x = Select * from table 1
it is probably a bad example. i just tried to make it simple. As you said, i want to do this way because i want to use variables and be more secure. The user won't be able to delete the query.
I guess i see what you meant. I have to either use a recordset or dump the result to a new table so i can use it later or i just use one big complicated SQL statement to get the final result.
I think the simplest way is still to use query builder and put the form variable in the design view. And then run the final query since it will run the subqueries together.
i am going to try that.
So i can put [Forms]![YourFormName]![Yo
thanks
it is probably a bad example. i just tried to make it simple. As you said, i want to do this way because i want to use variables and be more secure. The user won't be able to delete the query.
I guess i see what you meant. I have to either use a recordset or dump the result to a new table so i can use it later or i just use one big complicated SQL statement to get the final result.
I think the simplest way is still to use query builder and put the form variable in the design view. And then run the final query since it will run the subqueries together.
i am going to try that.
So i can put [Forms]![YourFormName]![Yo
thanks
ASKER
Jim,
I really appreciate your help. You can get back to me whenever you have a chance. i'll let you know if it worked for me.
:)
k
I really appreciate your help. You can get back to me whenever you have a chance. i'll let you know if it worked for me.
:)
k
>So i can put [Forms]![YourFormName]![Yo
Yes. e.g. [Forms]![frmCustomers]![tx
Also, once you define what you want in a single query, post it as a new question.
You'll be surprised how good the experts here are with SQL statments, and the can probably take your requirements and bang it out as a single query.
Thanks for the grade. Good luck with your project. -Jim
Yes. e.g. [Forms]![frmCustomers]![tx
Also, once you define what you want in a single query, post it as a new question.
You'll be surprised how good the experts here are with SQL statments, and the can probably take your requirements and bang it out as a single query.
Thanks for the grade. Good luck with your project. -Jim
ASKER
thanks a lot. I'll do that. I am convinced long time ago here is the most helpful place for a beginner like me. it is my best investment. :-P
I agree. Ten bucks a month is pretty damn cheap for all the advise you get, and when you start answering questions yourself, it's free.
Do a search here on EE for using dates in Queries. tons of good answers.