Solved

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

Posted on 2006-06-29
13
338 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
Comment Utility
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
Comment Utility
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
Comment Utility
Sorry Jim...refreshed too late.
J
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
np
0
 

Author Comment

by:kate_y
Comment Utility
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
Comment Utility
> 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:kate_y
Comment Utility
> 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now