creating and executing queries in VBA.

G

Guest

Hi everyone,
I hope that I can effectively articulate my issue here... here goes...

I am writing an analysis in VBA that requires the use of several queries,
many of whom are related, which have been writen entirely in VBA (ie. they
don't represent a query object that can be accessed via the database window.

I believe that the SQL statements are written correctly, but apparently, I'm
not actually running the query...only creating it. Here is a code example:

v_SQLEntityMasterjoin = "SELECT v_qryENTITY_CT_threshold.txtAENO,
v_qryENTITY_CT_threshold.txtMEGA_PROCESS,
v_qryENTITY_CT_threshold.txtMAJOR_PROCESS, " & _
"v_qryENTITY_CT_threshold.txtAUDIT_ENTITY,
v_qryENTITY_CT_threshold.AvgOfdblINHERENT_RISK,
v_qryENTITY_CT_threshold.AvgOfdblRESIDUAL_RISK,
v_qryENTITY_CT_threshold.AvgOfdblPROCESS_CRITICALITY, " & _
"v_qryENTITY_CT_threshold.AvgOfdblRESIDUALXCRITICALITY,
v_qryENTITY_CT_threshold.AvgOfdblTARGET_RISK,
v_qryENTITY_CT_threshold.SCORE_LEVEL_CT, v_qryENTITY_PY_threshold.txtAENO,
v_qryENTITY_PY_threshold.txtMEGA_PROCESS, " & _
"v_qryENTITY_PY_threshold.txtMAJOR_PROCESS,
v_qryENTITY_PY_threshold.txtAUDIT_ENTITY,
v_qryENTITY_PY_threshold.AvgOfdblINHERENT_RISK,
v_qryENTITY_PY_threshold.AvgOfdblRESIDUAL_RISK,
v_qryENTITY_PY_threshold.AvgOfdblPROCESS_CRITICALITY, " & _
"v_qryENTITY_PY_threshold.AvgOfdblRESIDUALXCRITICALITY,
v_qryENTITY_PY_threshold.AvgOfdblTARGET_RISK,
v_qryENTITY_PY_threshold.SCORE_LEVEL_PY " & _
"FROM v_qryENTITY_CT_threshold INNER JOIN v_qryENTITY_PY_threshold ON
v_qryENTITY_CT_threshold.txtAENO = v_qryENTITY_PY_threshold.txtAENO;"

Set v_qryENTITY_MASTERJOIN_ALLMATCHED = CurrentDb.CreateQueryDef(,
v_SQLEntityMasterjoin)


From what I can gather, v_qryENTITY_MASTERJOIN_ALLMATCHED is a query object
now, but how can I execute it?
 
G

Guest

use the following:

CurrentDb.Execute v_SQLEntityMasterjoin

I did not check your sql, assuming that it works fine!
Al
 
G

Guest

Hi Steve,
to execute your SQL there's no need to create a querydef, just do that

Currentdb.execute(v_SQLEntityMasterjoin)

Your SQL is just a select query so you will have no output.

HTH Paolo
 
G

Guest

Al and Paolo,
Thanks for the help.

I am trying to better conceptually understand what this code is doing,
because I am still getting errors...possibly due to the sequence of sql
statements.

My whole analysis starts with two queries that pull data from 2 different
tables and perform a join. Next, a new query is used to run a number of
computations on the previously joined table. Finally, I'd like to append the
results of the final computational-query to a table (which still needs to be
created/I'd like to initially create the table from the computational query).

Would I use recordset object or querydef objects to make all this work?
Would I use a combination of both and if so, HOW so?

Please advise...I've been stuck for a while with this one!
THANKS!!
 
G

Guest

Here is my code:

Private Sub cmdImportNewData_Click()

'Data Analysis Process
Dim v_avgRiskScore As Integer
Dim v_MidHIGH As Integer
Dim v_MidLOW As Integer
Dim v_scorespread As Integer
Dim v_qryENTITY_PY_threshold As QueryDef
Dim v_qryENTITY_CT_threshold As QueryDef
Dim v_qryENTITY_MASTERJOIN_ALLMATCHED As QueryDef
Dim v_qryENTITY_MATCHED_FinalSelection As QueryDef
Dim v_SQLidentifyscorethresholdPY As String
Dim v_SQLidentifyscorethresholdCT As String
Dim v_SQLEntityMasterjoin As String
Dim v_SQLEntityFinalSelection As String



'Compute score classification threshholds.
v_avgRiskScore = Round(DAvg("[AvgOfdblRESIDUALXCRITICALITY]",
"tblAUDIT_ENTITY_PY"))
v_MidHIGH = v_avgRiskScore + (Round(v_avgRiskScore * 0.33))
v_MidLOW = v_avgRiskScore - (Round(v_avgRiskScore * 0.33))


'build SQL statements with criteria created from above code.
v_SQLidentifyscorethresholdPY = "SELECT tblAUDIT_ENTITY_PY.txtAENO,
tblAUDIT_ENTITY_PY.txtMEGA_PROCESS, " & _
"tblAUDIT_ENTITY_PY.txtMAJOR_PROCESS, tblAUDIT_ENTITY_PY.txtAUDIT_ENTITY,
tblAUDIT_ENTITY_PY.AvgOfdblINHERENT_RISK, " & _
"tblAUDIT_ENTITY_PY.AvgOfdblRESIDUAL_RISK,
tblAUDIT_ENTITY_PY.AvgOfdblPROCESS_CRITICALITY,
tblAUDIT_ENTITY_PY.AvgOfdblRESIDUALXCRITICALITY, " & _
"tblAUDIT_ENTITY_PY.AvgOfdblTARGET_RISK,
IIf([tblAUDIT_ENTITY_PY]![AvgOfdblRESIDUALXCRITICALITY]>" & "'" & v_MidHIGH &
"'" & ",'HIGH'," & _
"(IIf([tblAUDIT_ENTITY_PY]![AvgOfdblRESIDUALXCRITICALITY]<" & "'" & v_MidLOW
& "'" & " And [tblAUDIT_ENTITY_PY]![AvgOfdblRESIDUALXCRITICALITY]>" & "'" &
v_MidLOW & "'" & ",'Med','low'))) " & _
"AS SCORE_LEVEL_PY FROM tblAUDIT_ENTITY_PY;"

v_SQLidentifyscorethresholdCT = "SELECT tblAUDIT_ENTITY.txtAENO,
tblAUDIT_ENTITY.txtMEGA_PROCESS, " & _
"tblAUDIT_ENTITY.txtMAJOR_PROCESS, tblAUDIT_ENTITY.txtAUDIT_ENTITY,
tblAUDIT_ENTITY.AvgOfdblINHERENT_RISK, " & _
"tblAUDIT_ENTITY.AvgOfdblRESIDUAL_RISK,
tblAUDIT_ENTITY.AvgOfdblPROCESS_CRITICALITY,
tblAUDIT_ENTITY.AvgOfdblRESIDUALXCRITICALITY, " & _
"tblAUDIT_ENTITY.AvgOfdblTARGET_RISK,
IIf([tblAUDIT_ENTITY]![AvgOfdblRESIDUALXCRITICALITY]>" & "'" & v_MidHIGH &
"'" & ",'HIGH'," & _
"(IIf([tblAUDIT_ENTITY]![AvgOfdblRESIDUALXCRITICALITY]<" & "'" & v_MidLOW &
"'" & " And [tblAUDIT_ENTITY]![AvgOfdblRESIDUALXCRITICALITY]>" & "'" &
v_MidLOW & "'" & ",'Med','low'))) " & _
"AS SCORE_LEVEL_CT FROM tblAUDIT_ENTITY;"



'Create a query for both PLANYEAR and CURRENT entity tables and create a
data field based on
'computed threshholds (above)
Set v_qryENTITY_PY_threshold = CurrentDb.CreateQueryDef(,
v_SQLidentifyscorethresholdPY)

Set v_qryENTITY_CT_threshold = CurrentDb.CreateQueryDef(,
v_SQLidentifyscorethresholdCT)



'Create a new query that joins the above queries and includes all fields
from both.
'Next, only select the records whose score changes exceed 20% of the average
risk score (determined above)

v_SQLEntityMasterjoin = "SELECT v_qryENTITY_CT_threshold.txtAENO,
v_qryENTITY_CT_threshold.txtMEGA_PROCESS,
v_qryENTITY_CT_threshold.txtMAJOR_PROCESS, " & _
"v_qryENTITY_CT_threshold.txtAUDIT_ENTITY,
v_qryENTITY_CT_threshold.AvgOfdblINHERENT_RISK,
v_qryENTITY_CT_threshold.AvgOfdblRESIDUAL_RISK,
v_qryENTITY_CT_threshold.AvgOfdblPROCESS_CRITICALITY, " & _
"v_qryENTITY_CT_threshold.AvgOfdblRESIDUALXCRITICALITY,
v_qryENTITY_CT_threshold.AvgOfdblTARGET_RISK,
v_qryENTITY_CT_threshold.SCORE_LEVEL_CT, v_qryENTITY_PY_threshold.txtAENO,
v_qryENTITY_PY_threshold.txtMEGA_PROCESS, " & _
"v_qryENTITY_PY_threshold.txtMAJOR_PROCESS,
v_qryENTITY_PY_threshold.txtAUDIT_ENTITY,
v_qryENTITY_PY_threshold.AvgOfdblINHERENT_RISK,
v_qryENTITY_PY_threshold.AvgOfdblRESIDUAL_RISK,
v_qryENTITY_PY_threshold.AvgOfdblPROCESS_CRITICALITY, " & _
"v_qryENTITY_PY_threshold.AvgOfdblRESIDUALXCRITICALITY,
v_qryENTITY_PY_threshold.AvgOfdblTARGET_RISK,
v_qryENTITY_PY_threshold.SCORE_LEVEL_PY " & _
"FROM v_qryENTITY_CT_threshold INNER JOIN v_qryENTITY_PY_threshold ON
v_qryENTITY_CT_threshold.txtAENO = v_qryENTITY_PY_threshold.txtAENO;"

Set v_qryENTITY_MASTERJOIN_ALLMATCHED = CurrentDb.CreateQueryDef(,
v_SQLEntityMasterjoin)



'Create a new query from that selects records from the previous one that
meets the following criteria:
'1) score threshold change criteria (ie. low or Med to HIGH)
'2) numeric score change exceeding 20% of the average risk score which was
computed above and stored in variable v_avgRiskScore
'note: criteria 2 uses the ScoreChangeCalc funtion to compute score
differences. Current score is listed first in the function followed by
PlanYear

v_scorespread = (v_avgRiskScore * 0.2)


'v_SQLEntityFinalSelection = "SELECT * INSERT INTO tblEntities_Comments FROM
v_qryENTITY_MASTERJOIN_ALLMATCHED WHERE
((v_qryENTITY_CT_threshold.SCORE_LEVEL_CT = 'HIGH' AND
v_qryENTITY_PY_threshold.SCORE_LEVEL_PY = 'Med') OR
(v_qryENTITY_CT_threshold.SCORE_LEVEL_CT = 'HIGH' AND
v_qryENTITY_PY_threshold.SCORE_LEVEL_PY = 'low')) " & _
'"OR ((ScoreChangeCalc(v_qryENTITY_CT_threshold.SCORE_LEVEL_CT,
v_qryENTITY_PY_threshold.SCORE_LEVEL_PY) >= v_scorespread) OR
(ScoreChangeCalc(v_qryENTITY_CT_threshold.SCORE_LEVEL_CT,
v_qryENTITY_PY_threshold.SCORE_LEVEL_PY) <= -v_scorespread));"

v_SQLEntityFinalSelection = "CREATE TABLE tblEntities_Comments FROM
v_qryENTITY_MASTERJOIN_ALLMATCHED WHERE
((v_qryENTITY_CT_threshold.SCORE_LEVEL_CT = 'HIGH' AND
v_qryENTITY_PY_threshold.SCORE_LEVEL_PY = 'Med') OR
(v_qryENTITY_CT_threshold.SCORE_LEVEL_CT = 'HIGH' AND
v_qryENTITY_PY_threshold.SCORE_LEVEL_PY = 'low')) " & _
"OR ((ScoreChangeCalc(v_qryENTITY_CT_threshold.SCORE_LEVEL_CT,
v_qryENTITY_PY_threshold.SCORE_LEVEL_PY) >= v_scorespread) OR
(ScoreChangeCalc(v_qryENTITY_CT_threshold.SCORE_LEVEL_CT,
v_qryENTITY_PY_threshold.SCORE_LEVEL_PY) <= -v_scorespread));"




Set v_qryENTITY_MATCHED_FinalSelection = CurrentDb.CreateQueryDef(,
v_SQLEntityFinalSelection)


'CurrentDb.Execute (v_SQLEntityFinalSelection)










MsgBox "Analysis Complete!", vbInformation, "Evergreen Risk Assessement
Database"


End Sub
 
A

Albert D. Kallal

From what I can gather, v_qryENTITY_MASTERJOIN_ALLMATCHED is a query
object
now, but how can I execute it?
Click to expand...

Execute it into what?

You can't just "execute" a select query. You can ONLY do this for an action
query (a query that does smting to the data, say delete, or update query).

When you execute a select command you have to have a place for the results
to go to.

so the following is legal

strSql = "update tblCustomers set City = 'New York' where city = 'N.Y.'"
currentdb.Execute strSql

however the following is not legal

strSql = "select * from tblCustomers"
currentdb.Execute strSql

When you execute sql it has to go into a report, or a form or a record set.
You can't just execute a select out of the blue and expect MS access to
guess were the results are supposed to. go. Further compounding this issue
is that MS access does not have temporary or intermediate cursors. We do
however have record sets and in effect they do represent the results of a
sql select. However, you can't execute FURHTER SQL statements on that
reocrdset like you can with temporary curses and SQL server for example.
Yyou can however loop and process the data row by row in a recordset (which
is often the case of what we need to do).

There is little if any advantage to building the SQL directly in code or
building in the sql via the query builder. AT the end of the day in both
cases you can run some code that will execute that SQL and put the results
into a record set.

If you're looking to execute sql select in code about only reasonable choice
here is to send it into a record set.

Dim rstData As DAO.Recordset
Set rstData = CurrentDb.QueryDefs("nameofquey").OpenrecordSet

As you can see you can type in your big huge long text of SQL. You gain
nothing in terms of your code.
Next, a new query is used to run a number of
Click to expand...
computations on the previously joined table.

You might be able to base this 2nd query on the first query you built.
O\It's not clear what kinda computations are talking about so it's really
hard to guess the correct approach.

If your data processing means you actually have to update or modify the data
and save some values in each record for further processing, then you'll
likely have to send that data to a temporary table. So instead of using a
select query you might actually wind up using an append query.

That menas:

1) - emppty our temp data table
currentdb.execute "delete * from myTempTable"

2) run append query (our original query that is a joint of the two tables --
but now it's gonna be an append query)
CurrentDb.QueryDefs("NameOfAppendQuery").Execute

3) Possibly open up the data we just created and do further data processing
on it for example

dim rstData as dao.RecordSet

set rstData = currentdb.OpenreocrdSet("nameofThatTempTable")

do while rstData.EOF = false
' data process reocrd
rstData.MoveNext
loop
rstData.Close

it really depends on what you're really trying to accomplish at the end of
the day here.

It is very possible with the summary sorting and grouping options that the
report writer can actually build you the summary and totaling and do the
calculations that you actually need. You might not need a lot of code (or
any code).
 
G

Guest

Albert,
Thank you for your explanation...it was VERY helpful.

I'm gong to change my select statements to insert statements and utilize
some tables for temporary record storage.

THANKS AGAIN!
 
Top