Re: creating and executing queries in VBA.

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!

"Albert D. Kallal" wrote:


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

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
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).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx



.

Relevant Pages

  • Re: UPDATE query in Access 2003 raising error
    ... Runtime error 3066 Query must have at least one destination field. ... The SQL works fine if I use it in the QBF Design mode. ... Set qdfTemp = db.CreateQueryDef ... Elsewhere in the code I use the same technique to execute an SQL ...
    (microsoft.public.access.formscoding)
  • Re: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT I
    ... This statement causes ADO to create a recordset to receive the results ... of the query being executed and assign that recordset to your qryAddProj ... You should use the ExecuteOptions argument of the Execute ... string containing a sql statement to be executed. ...
    (microsoft.public.data.ado)
  • Re: ADO.NET query execution much slower than SQL Management Studio
    ... A stored procedure uses a query plan that's created when the SP is first executed and reuses that plan regardless of the suitability of the plan from that point forward--until it's replaced. ... Something that would affect the .NET SqlClient but not SQL Mgmt Studio? ... takes less than one second to execute. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: UPDATE query in Access 2003 raising error
    ... Doug Steele, Microsoft Access MVP ... Runtime error 3066 Query must have at least one destination field. ... The SQL works fine if I use it in the QBF Design mode. ... Elsewhere in the code I use the same technique to execute an SQL ...
    (microsoft.public.access.formscoding)
  • Re: creating and executing queries in VBA.
    ... Execute it into what? ... You can't just "execute" a select query. ... When you execute sql it has to go into a report, or a form or a record set. ...
    (microsoft.public.access.modulesdaovba)