SQL in Code: Stacking queries 2

[Elysynn]

Hello all,

Just have a really quick question. Is it possible to stack queries in code? I have three queries designed to give me the information that I need. They are dependant on each other, and I would like to move them into the code that runs behind the form. I'm not sure if this is something you would use dynasets for or how to go about this - or if it's even possible. If it is possible, I'm not sure what syntax to use... Please let me know if I'm being unclear.

Thanks!
Elysynn

 

[Crowley16]

how much experiance do you have with sql?

the standard query builder doesn't really let you do this, so you would have to write the sql yourself.

there's 2 ways to combine queries, either a UNION query which goes:
select * from tbl
union select * from tbl

or you can try a nested query which goes:
select * from tbl where tbl.fld = (select fld from tbl2)

of course this is just a quick summary, there's much more to it, I suggest you look for some good sql books

--------------------
Procrastinate Now!

 

[Elysynn]

I have some experience with SQL.

I don't think the nested query quite does what I am looking for.

The queries that I have in the query builder do exactly what I need them to do. I just need to duplicate these three into VBA and produce the same result. This is where I'm getting lost.

If it would be more helpful, I can post the SQL for each of the three queries to see how they relate...

Thanks again,
Elysynn

 

[hkaing79]

something like...

Function RunAllMyQueries()
CurrentDB.Execute "SQL Statement 1"
CurrentDB.Execute "SQL Statement 2"
CurrentDB.Execute "SQL Statement 3"
End

You can also create a macro using RunSQL. You can also run the Query itself in VB using DoCmd.OpenQuery QueryName.

 

[Elysynn]

Thank you hkaing79.

I just have one last question (I think... ) Can you access the fields of the query within the code using either of the above mentioned methods?

Thanks again!!

-Elysynn

 

[hkaing79]

Ah. I was assuming you just wanted to perform a series of action queries. To access the fields of the query, you'll need to create a recordset...or three...

I use DAO (make sure you have DAO 3.6 checked in Tools -> References)

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strSQL as String

strSQL = "SELECT * FROM Query1"
set db = CurrentDB()
set rs = db.OpenRecordset(strSQL)

rs.MoveFirst
While Not rs.EOF
'You can access fields by rs.Fields("Field1")
rs.MoveNext
WEND

rs.Close
db.Close

If you expect just one value, then a shorter but slower code is to use the DLookup function.

 

[Elysynn]

I think you just solved my problem. =) I was trying to figure out how to reference my query using DAO (where this whole thread sprung from...) and you just showed me the light. While I go remove the blinders from my eyes for missing the obvious, I'm going to give you a star...


Thanks again for your help and your patience!
-Elysynn

 

[MichaelRed]

I generally prefer to leave the queries in the query "domain" (e.g. stored queries or procedures) which show up in the database window. In a procedure, define an array or strings to hold the names, and instantiate the queries as desired / necessary.

a brief (and incomplete) snippet:


dim strQryNames(3) as String

strQryNames(0) = qryMyQueryForStep1
strQryNames(0) = qryMyQueryFollowingStep1
strQryNames(0) = qryMyQueryAtTheEnd


While Idx <= UBound(strQryNames)
set rst = dbs.openrecordset(strQryNames(Idx),
dbopendynaset)

'do other stuff if you want with the single rs

Idx = Idx + 1
Wend




MichaelRed

 

[Elysynn]

MichaelRed,

Thank you for your information. I will have to experiment with that functionality as well. I can already see some potential uses for this (I think it might solve another problem I am currently working on...) Thanks again for the tip - I deem it star worthy! =)

-Elysynn