Solved

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

Posted on 2006-06-29
13
347 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
  • 6
  • 5
  • 2
13 Comments
 
LVL 65

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 65

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 65

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 65

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 65

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 65

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 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