Solved

Query builder vs query string and pass variables from a form to the query builder!

Posted on 2006-06-29
13
354 Views
Last Modified: 2010-08-05
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?
0
Comment
Question by:kate_y
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 17013109
1.     Do I just use DoCmd.RunSQL(Query1) to run the sub queries first?
No.  Executing QueryF will cause all queries within it (Query1, Query2) to execute automatically.

2.how do I pass the variables from a form to the queries’ criteria field?
[Forms]![YourFormName]![YourControlName]

3.
Not sure what you mean here.
Access pre-compiled SQL statements can not use global variables, only form-fed variables.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17013110
if you want to call input from a form from inside your design voew queries, then pass in the form name & control name, then it will look for it on the form. Once you do that, you can simply take the SQL of it and use it as you described in #1.
 Do a search here on EE for using dates in Queries. tons of good answers.

0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17013122
Sorry Jim...refreshed too late.
J
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 17013158
np
0
 

Author Comment

by:kate_y
ID: 17013213
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
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 17013260
> 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
0
 

Author Comment

by:kate_y
ID: 17013328
> 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.

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 17013361
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.
0
 

Author Comment

by:kate_y
ID: 17013406
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]![YourControlName] in the Criteria: field in the query design view?

thanks
0
 

Author Comment

by:kate_y
ID: 17013529
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
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 17013549
>So i can put [Forms]![YourFormName]![YourControlName] in the Criteria: field in the query design view?
Yes.  e.g. [Forms]![frmCustomers]![txtStartDate]

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
0
 

Author Comment

by:kate_y
ID: 17013572
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
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 17013602
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.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question