Data selection with SQL Server backend

[Mike]

I've started moving some of my tables to a SQL Server
backend and created Views in SQL Server. Is there a way
to pass a form field to the SQL Server view so that it
only returns a partial set of records to increase speed
and reduce network traffic. Or is there a way to call a
stored procedure with a parameter? I could call the view
in a query and filter on the form field there, but I
think the full dataset would be passed across the network.

 

[Albert D. Kallal]

I've started moving some of my tables to a SQL Server
backend and created Views in SQL Server. Is there a way
to pass a form field to the SQL Server view so that it
only returns a partial set of records to increase speed
and reduce network traffic.

The same and good designs techniques you used in ms-access to reduce network
traffic also applies to sql server. It would be plain silly to load up a
instant teller machine with everyone's name and account number , AND THEN
ASK for your account number to work on!

Hence, I sure you never did just blindly load up a ms-access form attached
to a table without regards to what names you load into the form...right?
Also, just throwing up a form with a bunch of names and then telling the
user to have at it is not very user friendly anyway. This design approach
don't make sense when using JET, or sql server.

So, the best approach is to simply ask the user what they want BEFORE YOU
launch the form. So, ask for that invoice number, or that name or whatever.
You then simply launch the form to THAT NAME or THAT INVOICE number or
whatever. If you use the "where clause" of the OpenForm, then ONLY that one
record will be retrieved to the form, and thus no network traffic is wasted.

It is interesting to note that having some nice prompts to ask the user what
they want is not only more user friendly, but it also reduces network
traffic also! This is a real win win situation. So, your solution here is
to simply not load up forms with more data then they need. I would be
surprised if anyone reading this could not agree more that letting the user
edit JUST the record they need is more efficient, and more user friendly
then a form with a zillion records, and the user has to somehow wade their
way through that large number of records.

dim strInvoiceNum as string

strInvoiceNum = InputBox("What invoice to edit")

docmd.OpenForm "frmInvoice",,,"InvoicdID = " & strInvoiceNum

The above shows how the OpenForm can be used with the "where" clause.

Here is some screen shots of some search screens to give you even more
ideas:

http://www.attcanada.net/~kallal.msn/Search/index.html

Hence, not overloading forms explains how us developers write applications
for 10, or 15 users and small tables in the 50,000 record range but do NOT
need sql server.

These table sizes are absolute nothing for ms-access, and not even a sweat
breaks out. These efficient designs are why ms-access can run blinding fast
without the need for sql server on a network. However, the above ideas and
concepts apply equally well to sql server.

Sql server does not increase the performance of your application all by its
self unless YOU design your application as such. We see tons of posts about
how people moved their data to sql server, and performance actually drops.

SQL server is far more powerful and scaleable then the JET/mdb database
engine, but in both cases designs that respect the network are required.

Good luck!

 

[Mike]

Thanks for the response and advice.

So it sounds like the where clause on the form will
limit the retrieval from a table whether it is a local
Access or linked Access or SQL Server table. I guess if
that is the case, then maybe a query criteria will also
limit the retrieval from the referenced table.

Although I do my selection on the form, I think it is
still efficient.

I have a combo box on the form which lists the subsets of
data available to that user. When I open the form, the
data on the form is from a query that filters based on
the initial combo box value so in effect I am only
returning one subset to the form. The user needs to
select each of the subsets for data entry ( by selecting
key in combo box ) and I thought the combo box would be
friendly since they could stay on the same form. But
maybe I'll try having the where clause on the form
reference the combo box selection and see if that works.
It is a complicated form with complicated totals in the
footer. It can take 10 to 20 seconds to load which is why
I'm pursuing optimization.