How to do multiple select queries for Access Report

[pattyd]

I have multiple queries that will run on my onopen event of my report. I use the docmd.openquery for the select queries the problem is I can see all these popup in the background. I do use the close query command also and the setwarnings to false and true. Can anyone give me some sample code that uses like 3 generic select queries using any sample database and then uses that data to create a temp table for my report all in vba code.I can use the temp table as my record source of the report..I do use the strSQL for my action queries.it's the select queries that I need help on. I know this can be done by using qrydef and recordsets I just don't understand how to put it together. please help me. I have many reports that have those little popups and if I can get help on one I should be able to fix my other reports. Thnks so much for any help you can give. Desperate!

[Delerna]

You can use eithe DAO or ADO in VBA code to SELECT data, UPDATE data or INSERT data.

I will give you an example of the older DAO because it might be a bit simpler.
1) From the code page of your form goto tools/references.
2) Tick the reference "Microsoft DAO 3.6 Object Library"
It may already be ticked.

Now you can use DAO to interract directly with your database objects.
First thing you will need is an instance of your database
[code=vba]
'Create an instance of the database for use in VBA
Dim db As DAO.Database
Set db = CurrentDb
[/code]

you can run update and insert queries directly through
the database instance

[code=vba]
db.execute "INSERT INTO theTargetTable SELECT * FROM theSourceTable"


db.execute "UPDATE theTargetTable SET theField=theVal ue"
[/code]

these are just sample queries, you can write something more realistic.

To query data and then work with that data you need a recordset object.

[code=vba]
Dim rst As DAO.Recordset
Set rst = db.OpenRecordse t("SELECT * FROM theTable")

'Loop through the records and do something
While Not rst.EOF
MsgBox rst.Fields(0)
rst.MoveNext
Wend
[/code]

Once finished with the DAO objects you should destroy them
[code=vba]
rst.close
set rst=nothing
db.close
set db=nothing
[/code]


I hope that is enough to get you started
Haven't tested that so I hope I haven't given you any syntax errors

[pattyd]

Thanks so much ..been swamped at work & had not had chance to reply..will this still work if my select queries uses info from the prev query example: I run qry1..now qry 2 uses info from qry1 & another table..qry 3 uses info from qry 2 and so on until my last query creates the table for my report..all these queries need to be run prior either in a form or as a report which is what I want..still confused?

[Delerna]

I don't have a clear understanding of what you are doing so I can only give you general Ideas.

Take the code from query1

for example
[code=sql]
SELECT Field1,Field2,F ield1+Filed2 as Field3
FROM Table1
[/code]


Now your code for query2 might be
[code=sql]
SELECT Query1.Field1,Q uery1.Field2,Qu ery1.Field3,Tab le2.Field4
FROM Query1
JOIN Table2 on Query1.Field1=T able2.Field1
[/code]

To turn both those queries into a single query then
[code=sql]

SELECT a.Field1,a.Fiel d2,a.Field3,Tab le2.Field4
FROM
( SELECT Field1,Field2,F ield1+Filed2 as Field3
FROM Table1
) a
JOIN Table2 on a.Field1=Table2 .Field1
[/code]

its called a subquery and you can have a suquery within a subquery within a subquery ....



Now to turn that query into an insert query
[code=sql]
INSERT into Table3
SELECT a.Field1,a.Fiel d2,a.Field3,Tab le2.Field4
FROM
( SELECT Field1,Field2,F ield1+Filed2 as Field3
FROM Table1
) a
JOIN Table2 on a.Field1=Table2 .Field1
[/code]

wrap all that up into a string variable and execute it
like in my first post.

Does that help?

[Delerna]

[code=sql]
Dim db As DAO.Database, strSQL as string
Set db = CurrentDb

strSQL=""
strSQL=strSQL & "INSERT into Table3 "
strSQL=strSQL & "SELECT a.Field1,a.Fiel d2,a.Field3,Tab le2.Field4 "
strSQL=strSQL & "FROM "
strSQL=strSQL & "( SELECT Field1,Field2,F ield1+Filed2 as Field3 "
strSQL=strSQL & " FROM Table1 "
strSQL=strSQL & ") a "
strSQL=strSQL & "JOIN Table2 on a.Field1=Table2 .Field1 "

db.execute strSQL

db.close
set db=nothing
[/code]

[Delerna]

Actually rereading your posts I am a little confused.
Your second post implies that one query is querying from another query which is querying another query and eventually one of the queries inserts records into a table.
In that case you can use subqueries to make one query out of two or more queries


Your first post states that you use multiple docmd.openquery and each query uses the results of the previous query that was run. This suggests to me that each of those queries either inserts records into a table, or updates records in a table with the final query inserting records from the records inserted/updated by the other queries into another table for your report.

The problem you are having with that is

the problem is I can see all these popup in the background

That being the case then the answer to your second question

will this still work if my select queries uses info from the prev query example

is yes.
This is the question that confuses me because that question should also apply to
docmd.openquery

Instead of using docmd.openquery
you are using db.execute QueryString.

The difference (in the context of your question) is docmd.openquery is executing a query definition which you can see popping up in the background.

db.execute QueryString is happening within the database object in VBA. Nothing will pop up in the background.


An answer to your orriginal question, How to stop these things from popping up in the back ground, is use DAO instead of DOCMD.
In this situation you should find that they are different methods of doing the same thing

[NeoPa]

Welcome to Bytes Patty!

Maybe I can help to clarify things a little here. Your question shows a little confusion over how things fundamentally work in Access specifically, and possibly databases in general. Hence Delerna's difficulty in giving a matching response (He does, but as he's unsure of the question I suspect he doesn't realise which of his various good answers is appropriate).

What I think you're trying to do is build a temporary table of data that involves running multiple queries. Quite naturally, you want to run the queries in order. This doesn't make as much sense as you expect though. What needs to be done is that each level of query needs to be built on the previous level. The lowest level will use one or more tables as the source for its data. Other queries will use the results of the first queries to build upon. The way that is achieved is by using these saved queries (Technically called QueryDefs in Access) as the source for the new query. The MakeTable query would use one or more of these subqueries to do the work. Invoking this one query would cause all the lower level queries to execute automatically and fit together sensibly in the way you've designed it. Does that clarify things for you?

[OldBirdman]

Tell me I'm wrong, but if a report is bound to a query, then won't that query will be run when the report is opened?
If so, then if qry1 is "SELECT A, B, C, ... FROM tbl1 .....
and qry2 is "SELECT B, C, ... FROM qry1 .....
and qry3 is "SELECT ..... FROM qry2 .....
and the report is bound to qry3, then Access will run all the queries when the report is opened.

[NeoPa]

You're not wrong OB, but I suspect Patty is still pretty inexperienced at this and didn't appreciate that that's how things fit together.

[Delerna]

I don't mean any disrespect to Patty but it just never occured to me that someone would think that. That certainly removes my confusion if that is the case.

[NeoPa]

I know I've had to explain similar concepts to members in the past, so I wouldn't be as surprised. If you consider the concept from the point of no previous Access experience, it's not so strange really, but it's always hard to consider not knowing something you know - if that makes sense.

[fdfjc]

I think this may be what you need. You can create multiple queries, building the second one based on the first one, the third one based on the second one, etc. in the following way.

The two lines I have commented out are the ones I use to make the query visible--the popup you are seeing and a msg box as a debugging breakpoint. I only use this during development and debugging -- then I comment it out. If you comment out DoCmd.OpenQuery ...., it won't show up, but will still run in the backgound. When you no longer need the query, DoCmd.Close acQuery, "nameofquer y" will close it. You can also delete it as shown if you want.

Dim qdf, qdf2, qdf3, qdf4, qdf5 As QueryDef

sqlStr = "your select query here"

Set qdf = CurrentDb.CreateQueryDef("TA1", sqlStr)

'DoCmd.OpenQuery "TA1", acViewNormal, acReadOnly
'msgbox "Do you want to close view?", vbOKOnly, "Close view"

DoCmd.Close acQuery, "TA1"
DoCmd.DeleteObject acQuery, "TA1"