to SQL or not to SQL

  • Thread starter Thread starter Mazza
  • Start date Start date
M

Mazza

Hi all,
Can someone tell me please is it better to use an SQL statement as the
source in a multi-user database form or use the table as a direct source.

Thanks in anticipation
 
Mazza said:
Hi all,
Can someone tell me please is it better to use an SQL statement as the
source in a multi-user database form or use the table as a direct source.
Click to expand...

There is no particular advantage of using either. however, the sql allows
you to set the sorting order.

In addition, you might want to restrict the records loaded to a form.
However, this is best done using the "where" clause of the openform.

So, for setting sort order, or resting records to a particular set, then a
query is obviously going to be needed.

However, if you are like me, and as a general rule use the "where" clause to
open the form to ONE record, then a table, or query makes no difference in
terms of performance, or maintains issues....

However, you ALWAYS want to restrict the amount of records loaded to a form
in either case. It makes no sense at all to load up a form that is attached
to a large table...and then let users have at it....

I usually build some type of search form..and this means the main edit form
only has to load one record, and thus if the file has 1, or 100,000 records,
the load time is going to be the same. Here is some ideas as to what I mean

http://www.members.shaw.ca/AlbertKallal/Search/index.html
 
Hi all,
Can someone tell me please is it better to use an SQL statement as the
source in a multi-user database form or use the table as a direct source.

Thanks in anticipation
Click to expand...

Generally a stored Query is the best option. This gives you control
over the order of records (a query can be sorted, a table cannot) and
the option to filter the data to a subset of the records (or a subset
of the fields).

John W. Vinson[MVP]
 
Thanks heaps Albert.



Albert D.Kallal said:
There is no particular advantage of using either. however, the sql allows
you to set the sorting order.

In addition, you might want to restrict the records loaded to a form.
However, this is best done using the "where" clause of the openform.

So, for setting sort order, or resting records to a particular set, then a
query is obviously going to be needed.

However, if you are like me, and as a general rule use the "where" clause
to open the form to ONE record, then a table, or query makes no difference
in terms of performance, or maintains issues....

However, you ALWAYS want to restrict the amount of records loaded to a
form in either case. It makes no sense at all to load up a form that is
attached to a large table...and then let users have at it....

I usually build some type of search form..and this means the main edit
form only has to load one record, and thus if the file has 1, or 100,000
records, the load time is going to be the same. Here is some ideas as to
what I mean

http://www.members.shaw.ca/AlbertKallal/Search/index.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal
Click to expand...
 
Back
Top