Re: As a generale rule - Query or VBA?

Paul H wrote:
I seem to end up with loads of append and update queries just because it's
quick and easy to build queries or make a new ones based on an existing
query. But I end up with loads of queries with long names like:

qryfrmCustomers_Diary_DeleteEntriesBeforeADate
qryfrmMaintenance_CloneOrder_Step1_CreateOrderIDAndDescription

Am I being lazy? Should I code most of my action queries to tidy up the
clutter in the database window?

Is there a general rule for this?

Thanks,

Paul

I think there is not a general rule.

Stored queries have a precompiled query plan. But that plan may not be
efficient for stored Parameter queries. The nature of the Parameter
will be unknown to the query engine; it will guess what type and size
the Parameter will be but it cannot know. Because of this, in some
cases it may create an inefficient plan. So, substituting an SQL string
passed to OpenRecordset or Execute instead of using a stored Parameter
query may result in better performance (sometimes, maybe).
For a code jockey (I am one of these) the SQL string is tempting. We
don't leave the VB editor window. We debug.print our string to check
it. When we want to re-visit some operation we go to our module and
everything is right there. As we become comfortable with writing SQL
strings off the top of our head doing seems like nothing more than a
natural extension of our scripting. In some cases, we can force JOINs
which may or may not be more efficient than other operators. And, if we
move back and forth between JET and SQL-Server, we may have to do
nothing to use the same query strings. And of course, when we want to
port our application to ASP and SQL-Server all our strings are right
there.
In JET, I use the SQL string 99% of the time. But in MS-SQL Server I
tend to start with the SQL string, but later, when I try to optimize
what I am doing, I port the string to a Stored Procedure, View or User
Defined Function.
But MS-SQL Server T-SQL is a million times more powerful than
JET/Access SQL, allowing for looping, cursors, testing, explicit data
conversion etc. (I think this is the weakness of MS's current
encouragement of ODBC connections to MS-SQL Server; while these
capabilities can be used, there is no encouragement to do so, and in
fact, one can just pretend the whole thing is JET (I think) with not so
may problems. How often is this done? By the looks of the posts here, I
would say about 99.44%. SO to most of these persons who say, "I am
using MS-SQL Server" I would say, "Yeah, Yeah, Sure, Sure, Whatever!".
When creating something that makes use of these T-SQL strengths, it's
convenient to separate the logical parts into lines, as we do with most
code. Doing so in VBA, and then sending 500 lines off as a string is
not so convenient, IMO. Also creating procedures in most of the MS
utilities gives us a syntax check, which can be very helpful.

.

Relevant Pages

  • Re: Updating only changed records
    ... What exactly are the queries doing? ... a query that will update matching records, and add records that don't match. ... Dim stDocName1 As String ... Dim Msgstr2 As String ...
    (microsoft.public.access.formscoding)
  • RE: Error "no value given for one or more required parameters" HELP
    ... i tried putting the sql string into 2 parts by: ... i am going to have to cut back on the query name lenght. ... Dim sqlProducts As String ...
    (microsoft.public.access.formscoding)
  • Re: Make Multiple Tables via Query
    ... Execute method with a select query. ... Select queries contain a SELECT statement and can return ... Dim strExcelFileName As String ...
    (microsoft.public.access.queries)
  • Re: Data type mismatch in criteria expression. (Error 3464)
    ... I keep getting the Error 3464 when I run a particular query. ... This query is based on two other queries. ... Public Function FirstNumber(ByVal StringIn As String) As String ... Dim strChar As String ...
    (microsoft.public.access.formscoding)
  • Re: What do I need?
    ... Dim strFirst As String, strSecond As String, strThird As String ... Set rst = New ADODB.Recordset ... who have not audited a site on the occasions of its last three audits queries ... i have the query but i only want to assign certain values to variables. ...
    (microsoft.public.access.queries)