How to link a Access FE to SQL Server BE

[Tom]

All:

I’ve been tasked with upsizing an Access database to SQL Server and am
having some problems wrapping my mind around how to efficiently fetch
data from the server. Pointers would be very much appreciated.

Currently a single Access based FE is used to manage about 20
different Access BE files. Each of the BE files is structurally
identical (basically each one represents a different plant). In the
transition to SQL Server, it seems obvious that we need to compress
all those Access BE files into one SQL Server DB for ease of future
maintenance and cost issues. This is easily done by adding a PlantID
to the proper tables. The intent is to use the existing Access FE to
connect to the SQL Server for data entry and reporting. I understand
and can manage the linking via a DSN-less connection. Additionally,
I’d prefer to use bound forms.

What I don’t understand is how to limit the data pulled down via the
link to a single PlantID (the user will never need to work with more
than one plant’s data at a time). My efforts include:
• Linking directly to the tables – but then I get all the data, not
just one PlantID worth
• Linking directly to views – better, but I don’t understand how to
limit the view to one PlantID from within the Access FE
• Using a pass-through query to link to either a table or a view – can
easily manage retrieving only one PlantID worth of data, but pass-
through queries are read only, so I can’t update the data.
• Using a stored procedure - I can write the stored procedure in SQL
Server, but how do I provide the PlantID as the parameter value
outside of a pass through query

I’m obviously missing some simple concept here. Help please!

Thanks

Tom

 

[Tom van Stiphout]

On Fri, 28 Aug 2009 21:29:52 -0700 (PDT), Tom <[email protected]>
wrote:

I am a fan of ADP, but I see the writing on the wall.

I would link the tables using ODBC, and then write queries to select
only the current plant. Perhaps I have a form for the user to select
the plant to work with. Then my query for the RecordSource of some
form would be something like:
select * from myTable
where PlantID = Forms!myPlantSelectForm!myPlantControl

If you run SQL Server Profiler you will see only the data for the
given plant is queried.

-Tom.
Microsoft Access MVP

 

[Albert D. Kallal]

All:

Each of the BE files is structurally

identical

If all the back ends are identical, then all you have to do is add one
additional column to the table that distinguishes the reason that you have
20 BE's in the first place. Remember any design problem or things that makes
things a little bit more difficult in your current designs will also make it
more difficult when you move to SQL server.

What I don’t understand is how to limit the data pulled down via the

link to a single PlantID (the user will never need to work with more
than one plant’s data at a time). My efforts include:
• Linking directly to the tables – but then I get all the data, not
just one PlantID worth

The above is not true at all. Linking to a table or view does NOT cause ANY
reocrds to come down the wire. Even in an environment where using a network
and not using SQL server, if you load up a form with a where clause to a
given Plant ID then ONLY the one plant record comes down the wire over the
network. This is TRUE FOR BOTH USING A linked table to a backend database
(mdb), or if the front end is linked to SQL server.

The fact that you link to a table has actually NO relationship to the fact
of how many records come down the pipe over the network. How many records
transfer down the network is going to be the issue of how you design your
application.

With the above information means that it is that it's your application
design that limits the amount of records that travel over the network. In
fact a good design and access right now for a split database that runs well
will ALSO tend to work well with SQL server. And a current poor design or
one that was designed without any intention of limiting the records that
travel across the network will also run poorly even when you don't use SQL
server. So, I can't strees that sql server will NOT magic speed things up.
If your application is designed and runs well now, then it'll tend to do so
with SQL server.

SQL server is indeed a high performance system, and also a system that can
scale to many many users.

If you write your application in c++, or VB or in your case with ms-access,
in GENERAL the performance of all of these tools will BE THE SAME.

In other words...sql server is rather nice, and is a standard system used in
the IT industry.

However, before you convert..how well does your application run now?

We often see posts here that a application is too slow with one user. If the
application is too slow with one user..then what can one expect when they
try and run 10 users. That is now 10 times the requirements..

The other issue is how well is the database setup?

Further..how well are the forms designed?

How well does the application work with 5 users..and then when you jump to
10 users...how much a slow down to you notice?

A few things:

Having a table with 75k records is quite small. Lets assume you have 12
users. With a just a 100% file base system (jet), and no sql server, then
the performance of that system should really have screamed. If that
Application performed poorly, then it's the design that was the problem.

Before Microsoft started "really" selling sql server, they rated JET could
handle easily 50 users. We have credible reports here of people
running 100 users. however, in those cases everything must be
"perfect".

I have some applications out there with 50, or 60 HIGHLY related tables.
With 5 to 10 users on a network, response time is instant. I don't think any
form load takes more then one second. Many of those 60+ tables are highly
relational..and in the 50 to 75k records range.

So, with my 5 users..I see no reason why I cant scale to 15 users with
such small tables in the 75,000 record range.

If the application did not perform with such small tables of only 75k
records..then upsizing to sql server will do absolute nothing to fix
performance issues. In fact, in the sql server newsgroups you see weekly
posts by people who find that upgrading to sql actually slowed things down.
I even seem some very cool numbers showing that some queries where actually
MORE EFFICIENT in terms of network use by JET then sql server.
So just moving to sql server will Usually result in slower performance.

My point here is that technology will NOT solve performance problems.
However, good designs that make careful use of limited bandwidth resources
is the key here. So, if the application was not written with good
performance in mind..then you kind are stuck with a poor design!

I mean, when using a JET file share, you grab a invoice from the 75k record
table..only the one record is transferred down the network with a file share
(and, sql server will also only transfer one record). So, at this point, you
really will NOT notice any performance difference by upgrading to sql
server. There is no magic here.

Sql server is a robust and more scalable product then is JET. And, security,
backup and host of other reasons make sql server a good choice.
However, sql server will NOT solve a performance problem with dealing
with such small tables as 75k records

Of course, when efforts are made to utilize sql server, then
significant advances in performance can be realized.

I will give a few tips...these apply when using ms-access as a file
share (without a server), or with odbc to sql server:

** Ask the user what they need before you load a form!

The above is so simple, but so often I see the above concept ignored.
For example, when you walk up to a instant teller machine, does it
download every account number and THEN ASK YOU what you want to do? In
access, it is downright silly to open up form attached to a table WITHOUT
FIRST asking the user what they want! So, if it is a customer invoice, get
the invoice number, and then load up the form with the ONE record (how can
one record be slow!). When done editing the record...the form is closed, and
you are back to the prompt ready to do battle with the next customer. You
can read up on how this "flow" of a good user interface works here (and this
applies to both JET, or sql server applications ):

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

My only point here is restrict the form to only the ONE record the user
needs. Of course, sub-forms, and details records don't apply to this rule,
but I am always dismayed how often a developer builds a nice form, attaches
it to a large table, and then opens it..and the throws this form attached to
some huge table..and then tells the users to go have at and have fun. Don't
we have any kind of concern for those poor users? Often, the user will not
even know how to search for something ! (so, prompt, and asking the user
also makes a HUGE leap forward in usability. And, the big bonus is reduced
network traffic too!...Gosh...better and faster, and less network
traffic....what more do we want!).

** Use caution with quires that require more then one linked table

This mostly applies to odbc to sql server.
(note that most of us tend to avoid using ADP projects for existing
applications, it's not recommend to go down that road, and you're better off
to use ODBC linked tables to SQL server for *existing* applications)

Thus, while jet does it best..these types of joins can often be
real slow (this is especially the case when there's aggregates
queries - since those aggregates (sum, count) are actually values
that are the result of "many" records. The simple solution in these
cases is to change the query to view and link to that. This is the
least amount of work compared to other approaches that can reduce
bandwidth. This also means that the aggregates occur on the
server side. This is also an example where sql server uses LESS
bandwidth then JET (since simple row of something like customerID
+ a total of sales results in only one row of data being sent across the
network - this is despite the fact that the aggregate of customer sales
total could be 1000's of reocrds. In the case of jet, every single record
will have to travel down the network pipe, whereas with SQL server, only one
row of data will travel down the pipe. Therefore if you had only ten
customers, but each customer had 10,000 sales records, in the case of jet
you would see 30,000 records travel accross the network, but in the cases
SQL server you would only see ten records traveled down the network pipe
(30,000 - 10 = 29990 records saved In terms of network reduction that's a
lot of saving here). so in some cases SQL server can substantially increase
the performance of things, but it's not very often that you're viewing
30,000 records at one time, the exception being here tottals etc in a a
report.

This also applies to combo boxes. Most
combos boxes has sql embedded in them. That sql has to be processed, and
then thrown to a linked odbc table. This is a bit sluggish. (a form can have
maybe one, or two combos..but after that ..it will start to load slow). So,
remove the sql from the combo box, build a view..and link the combo box
direct to that view (JUST USE the view name...the sort, and any sql need to
be in the view). The result is quite good combo box load performance. (and
again, not very much work. There are other approaches that can even speed
this up more..but we have to balanced the benefits VS. the amount of work
and coding. I don't think once should re-code all combo boxes to a call back
with a pass-through reocrdset..but that can be a solution also).
Notet that if you linked to SQL server and if the form load time is
acceptable, then often you don't have to do anything at all. As always the
trick here in these upsizing projects is only to do the amount of work you
need to get acceptable performance.

** Of course, if you do have sql with more then one table..then pass-though
is the best if using odbc.
This is usually done for reports as pass-through queries are read
only.

** You can continue to use bound forms..but as mentioned..restrict the form
to the one record you need. You can safely open up to a single invoice,a and
even continue to use the "where" clause of the openform. Bound forms are way
less work then un-bound forms...and performance is generally just is good
anyway when done right. So, just use a open form with the "where" clause
option to restrict the form to ONE record. This suggestion applies equally
well to SQL server based system, or one that is a split file share to a mdb
file on a network. so a bound form with a "where" clause will ONLY pull
reocrds down the pipe that satisfy the where clause.

** Large loading of combo boxes. A combo box is good for about 100
entries. After that..you are torturing the user (what..they got to look
through 100s of entries). So, keep things like combo boxes down
to a min size. This is both faster..and MORE importantly it is
kinder to your users.

After all, at the end of the day..what we really want is to make
things easy for the users...and treat them well.. It seems that
treating the users well, and reducing the bandwidth
(amount of data) goes hand in hand. So, better applications
treat the users well..and run faster! (this is good news!)

 

[Albert D. Kallal]

Therefore if you had only ten
customers, but each customer had 10,000 sales records, in the case of jet
you would see 30,000 records travel accross the network

The math is wrong..10 customers x 10,000 sales records = 100,000 records
traveling down the network...

In the case of sql server...only 10 records will travel down the network.
So, the savings are larger then even what I pointed out....

 

[Michael Gramelspacher]

Currently a single Access based FE is used to manage about 20
different Access BE files.

In the meanwhile, a single Access FE can use 20 SQL Server databases almost as easily as it can
manage 20 Access BE databases.

I use this concept with about 20 church databases with identical structures. The problem with
mergeing the data in my case is that all the lookup tables have different values. Priest No. 15 is
going to be different in each database.

Just a thought.

 

[Tom]

Thanks for all the suggestions and information - it will take me
awhile to digest everything you wrote Albert. Some followup points.

* I'm comfortable in Access MDB - don't really want to learn ADPs and
SQL Server at the same time, plus as you mentioned Tom, ADPs seem to
be a dying vine.

* The application runs well now as is (reports are a little slow, but
that's 'cause there's alot of data manipulation) - forms were
specifically designed to return as few records as possible - generally
only two or three at a time. There are no large cbos - most, if not
all are driven off all are driven off what will remain a local lookup
table.

* the push to SQL Server is not a performance enhancement issue, its
an accessibility issue. The business has grown to the point that the
"back office staff" can no longer handle all the data entry in a
timely manner. Bossman wants to push the initial data entry out to
the field techs, with appropriate checks by back office prior to
committing to the main tables. That means the database needs to play
well with the internet - my understanding is that Access has issues in
that department. Since this is a small business with no IT staff to
speak of, the db will be hosted by an outside vendor (any suggestions
on a good one?)

* Thanks for clarifying what comes over the wire, so to speak, when
linking. I had thought linking to a very large table would
necessarily be slower than linking to a small one - I guess not.

* In reading "MS Access Developer's Guide to SQL Server" (Chipman and
Baron) I had understood that one of the advantages of views was that
you could limit the number of rows returned. This was deemed to be a
good thing for a variety of reasons. I was trying to employ this
feature by returning only one PlantID worth of data to the Access FE,
but couldn't (and still can't) figure out how to pass the PlantID up
from Access to SQL Server and still get an editable return table/
recordset.

In fact, I'd still like to do this last item - there is no good reason
to return more than one PlantID's worth of data to the Access FE - and
while I can idiot proof the database to make sure the end users don't
do anything dumb that "cross contaminates" the data between Plants, my
general operating world view is that newer, more innovative idiots are
released everyday. Any suggestions? (Tom - unless I misunderstood
your answer, you suggested linking to a table/view with all the data,
then using a local filter in Access to get only the PlantID you want,
based on a pick form).

Thanks - Tom

 

[Albert D. Kallal]

I had understood that one of the advantages of views was that
you could limit the number of rows returned.

You can use a view to restrict records. However that assumes you can build
a view that restricts the records ahead of time.

So, views CAN be used to restrict records, but ONLY if you know ahead of
time what the restricting is going to be. For example, we might have 10,000
records of customers, but we can create a view of ONLY customers from New
York. That way the view will ONLY return records of those people from New
York. On the other hand if you don't know ahead of time what city you want
to report on, then using a view to restrict records is a complete 100% waste
of time.

Why not just use a where clause on the report when you open it? You gain
absolute nothing by attempting to use a view here. So using a view when you
don't know ahead of time what the restriction is going to be is of no use at
all.

I mean if you need one staff person to ONLY work on customers from New York,
then you know ahead of time about this and then using a view would make
sense (you would give that one user permissions to use that view, and thus
that stuff will only be able to use records from New York).

From an overall application design issue an view does absolute nothing for
your case and is not at all appropriate for your needs in terms of
restricting records.

Using a where clause for a openreport, or openform will restrict the records
sent down the wire. Using a view in this case will not change the number of
records send down the wire nor will it reduce your network ( bandwidth)
requirements.

I suppose if you know ahead of time that you going to have to look at a
Plant iD 731, then sure you can create a view that only returns plant ID
731, but that going to be of absolute no use at all if tomorrow you need to
look at plant ID 732 (you would have to create another view that returns id
732). So sure, views are great way to restrict records, but only if you know
ahead of time what that restriction is going to be.

In my other post I mention some examples in which views can substantial
reduce bandwidth requirements.

Since you don't know ahead of time what your search or record is going to be
then a view is of absolute no use and is complete waste of your time (it is
a wild goose chase).

Just bind your form to a table, and pass the ID using the where clause and
only one record will travel down the wire. You don't have to do anything
else here.

Albert D. Kallal (Access MVP)
Edmonton, Alberta, Canada
(e-mail address removed)

 

[Hans Up]

good thing for a variety of reasons. I was trying to employ this
feature by returning only one PlantID worth of data to the Access FE,
but couldn't (and still can't) figure out how to pass the PlantID up
from Access to SQL Server and still get an editable return table/
recordset.

Look at table functions in SQL Server.

 

[Hans Up]

Look at table functions in SQL Server.

Ignore that, Tom. I went looking for more information and discovered
they don't do what I thought I remembered.

Sorry.

 

[David W. Fenton]

I would link the tables using ODBC, and then write queries to
select only the current plant. Perhaps I have a form for the user
to select the plant to work with. Then my query for the
RecordSource of some form would be something like:
select * from myTable
where PlantID = Forms!myPlantSelectForm!myPlantControl

If you run SQL Server Profiler you will see only the data for the
given plant is queried.

I've found that it's important to define form control references
like that as a parameter to insure that the results are correct when
the control is Null. This is less of an issue for WHERE clauses, and
more of an issue for, say, append queries where you are drawing the
values to be appended from a control on a form. I discovered this in
the conversion from A97 to A2003 of an app that went live in late
1997. An append query that appended values from form controls
started failing when one of the controls were Null (all the fields
ended up Null). The interim solution was to declare parameters, but
I eventually stopped using a saved QueryDef and instead wrote the
SQL on the fly, i.e., resolving the control values once in code.

But because of being bitten by that, I have continued to declare the
parameters, as it seems to me to be a good way to hint the query
optimizer.

 

[David W. Fenton]

m:

* the push to SQL Server is not a performance enhancement issue,
its an accessibility issue. The business has grown to the point
that the "back office staff" can no longer handle all the data
entry in a timely manner. Bossman wants to push the initial data
entry out to the field techs, with appropriate checks by back
office prior to committing to the main tables. That means the
database needs to play well with the internet - my understanding
is that Access has issues in that department. Since this is a
small business with no IT staff to speak of, the db will be hosted
by an outside vendor (any suggestions on a good one?)

You need to read up on Windows Terminal Server/Citrix, then. If
you're contemplating giving access to users in multiple locations
over the Internet, that's by far the easiest method for doing so.
The end users would connect to your VPN, then run the Remote Desktop
client to connect to the Terminal Server. They'd get their own
desktop (just as they would if they were running on a workstation
connected to the LAN), and each should have an individual copy of
the front end (stored on the Terminal Server), just as they would
running from a workstation on the LAN.

This is easier for two main reasons:

1. you don't have to upsize -- your app is going to run exactly the
same (and perhaps faster, since the data won't be going across the
LAN) than it currently does.

2. you don't have to worry about administering all the workstations
and laptops -- everything the users need is on one box, the Terminal
Server.

The costs of Terminal Server are quite low, about $40 per user for
the CALs, and whatever it takes to beef up the Windows server you
choose as the Terminal Server. I have always allocated about 128MBs
of RAM per user (WTS optimizes memory quite well, so read-only code
used by many users is loaded into memory only once), and have found
10 simultaenous users on a box with 2GBs of RAM to run very well.

You also need to make sure your WAN infrastructure is in place,
i.e., you have to have a VPN (you don't want the RDP port open to
the wild and woolly Internet!), but that's not even that big a deal
for small businesses, as even consumer-level routers and firewalls
have VPN software built in. And the Windows VPN client is fine, too,
as long as it's configured for security (the defaults are a bit too
loose).

Then all you have to worry about is bandwidth, and that's a very
small concern, as Terminal Server is workable even over dialup! The
more bandwidth you have, the better the user experience, but I had a
client with a main office with the Terminal Server connected to a T1
with satellite offices connected to 384K DSL, and 5-10 simulaneous
users, and it worked just fine. I thought the DSL would be a
bottleneck, but because there were only 2 or 3 users at each
location, it was never a problem at all.

I am very impressed with Terminal Server and think it's a remarkable
good technology. It certainly would be the preferred approach for me
were I to find myself in your circumstances.