As a generale rule - Query or VBA?

[Paul H]

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_DeleteEn triesBeforeADat e
qryfrmMaintenan ce_CloneOrder_S tep1_CreateOrde rIDAndDescripti on

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

[pietlinden]

if they're action queries I won't use elsewhere, I just use

DBEngine(0)(0). Execute "SQL Statement...", dbFailOnError

[ManningFan]

I think a good portion of Access people do what you do, however it is
better to write and execute the SQL in code when possible. It reduces
clutter, but it makes it more difficult for lesser-experienced people
to debug issues.

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_DeleteEn triesBeforeADat e
qryfrmMaintenan ce_CloneOrder_S tep1_CreateOrde rIDAndDescripti on

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

[Terry Kreft]

Prepared queries are more efficient than executing SQL prepared on the fly.

The problem is that as you say the d window gets cluttered up, I suspect
most people with experience use a mixture of methods.
--

Terry Kreft
"Paul H" <no****@nospam. comwrote in message
news:G6******** ************@ec lipse.net.uk...

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_DeleteEn triesBeforeADat e
qryfrmMaintenan ce_CloneOrder_S tep1_CreateOrde rIDAndDescripti on

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

[chris.nebinger]

-->Should I code most of my action queries to tidy up the
clutter in the database window?

My answer is no. Instead, name them USYS_del_Entrie s_Before_a_date .

If your only intention is to declutter the database window, then this
method will hide them, only visible if you turn on system objects. If
the query is saved, then JET will save the fastest execution path. If
its in code, the JET engine first has to ensure it is valid SQL, then
determine the execution path, then execute it. Which sounds faster?

Granted, for smaller tables, it might not make a noticeable difference.
But, for scalability concerns, it is much easier to use queries and
then move them to SQL Server instead of code. Using code will not
achieve any performance gains with SQL then with Access.

I'm trying to declutter my code using procedures. The database window
is not a concern for me, especially with the Object Dependencies tool
now. I'd hate to think I tried cleaning up the database window and
deleted a query that some code required.

Chris Nebinger


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_DeleteEn triesBeforeADat e
qryfrmMaintenan ce_CloneOrder_S tep1_CreateOrde rIDAndDescripti on

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

[Rick Brandt]

ch************@ gmail.com wrote:

-->Should I code most of my action queries to tidy up the
clutter in the database window?

My answer is no. Instead, name them USYS_del_Entrie s_Before_a_date .

If your only intention is to declutter the database window, then this
method will hide them, only visible if you turn on system objects. If
the query is saved, then JET will save the fastest execution path. If
its in code, the JET engine first has to ensure it is valid SQL, then
determine the execution path, then execute it. Which sounds faster?

[snip]

I completely disagree. The speed difference is neglible and the
self-documenting nature of SQL executed in code is a HUGE advantage over a
stored query def. When looking at that query months later you will have to
search to find out where it is called (if at all), then research some more
to determine if a change will break some other part of the app that you
didn't remember also used the same query. SQL executed in code is self
explanatory and has an obvious scope.

I use saved querydefs only when I have to.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[rkc]

ch************@ gmail.com wrote:

-->Should I code most of my action queries to tidy up the
clutter in the database window?

My answer is no. Instead, name them USYS_del_Entrie s_Before_a_date .

If your only intention is to declutter the database window, then this
method will hide them, only visible if you turn on system objects.

You can also hide a query by right clicking it, selecting properties and
checking attributes: Hidden. Hidden Objects has to be not selected in
Tools, Options, View, Show.

[David W. Fenton]

"Paul H" <no****@nospam. comwrote in
news:G6******** ************@ec lipse.net.uk:

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.

I store few action queries, but lots of SELECT queries.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"Terry Kreft" <te*********@mp s.co.ukwrote in
news:Dc******** ************@ka roo.co.uk:

Prepared queries are more efficient than executing SQL prepared on
the fly.

Is that really a significant issue in most cases? I have never seen
any real performance drain from using DAO for things.

Does it really take that long to optimize SQL?

Keep in mind also that if you're sending the SQL to a back-end
server database, those often will optimize and cache the temporary
query, as long as the SQL remains the same (dunno if they can do it
with queries that differ only in the values in the WHERE clause).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

rkc <rk*@rochester. yabba.dabba.do. rr.bombwrote in
news:%l******** ***********@twi ster.nyroc.rr.c om:

You can also hide a query by right clicking it, selecting
properties and checking attributes: Hidden.

Doesn't every developer work with all system and hidden objects
showing?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/