Record Source Theory

[Michael Conroy]

After a few years of working with Access, I realize that there are about five
ways to do anything. With that in mind, what I am looking for is an opinion
on the "proper" way to design a professional database. The issue is this. I
have an ODBC connection (System DSN) to a large application that hundreds of
people are using every day. The primary table has forty thousand records and
when my database accesses the tables I don't want to slow down the system,
nor do I want my form to take 45 seconds to open, which it now does. I have
read through the suggestions here for speeding up a database, but I am not
sure which one is the "best" idea. Should I use many make table queries and
create temporary tables, which negates the "live" connection benefits and
requires someone to push an update button every now and then? Should I get
the form to open with recordsets (are they faster?) and pull just one record
at a time? (how do you do this?) Is that the way to do it or is there another
way to do this? If an expert out there were to design a top of the line
database with an SQLServer backend, how would you establish the record source
for your queries, forms and reports? The company requires that I use Access
97, DAO obviously, and I don't know the version of the SQL Server or the
speed. The network connection is 1GB to the desk, or so it says. This
question, I think, is more about putting a load on the server rather than the
throughput speed. As always, any help would be greatly appreciated. Thank you

 

[Rick Brandt]

After a few years of working with Access, I realize that there are
about five ways to do anything. With that in mind, what I am looking
for is an opinion on the "proper" way to design a professional
database. The issue is this. I have an ODBC connection (System DSN)
to a large application that hundreds of people are using every day.
The primary table has forty thousand records and when my database
accesses the tables I don't want to slow down the system, nor do I
want my form to take 45 seconds to open, which it now does. I have
read through the suggestions here for speeding up a database, but I
am not sure which one is the "best" idea. Should I use many make
table queries and create temporary tables, which negates the "live"
connection benefits and requires someone to push an update button
every now and then? Should I get the form to open with recordsets
(are they faster?) and pull just one record at a time? (how do you do
this?) Is that the way to do it or is there another way to do this?
If an expert out there were to design a top of the line database with
an SQLServer backend, how would you establish the record source for
your queries, forms and reports? The company requires that I use
Access 97, DAO obviously, and I don't know the version of the SQL
Server or the speed. The network connection is 1GB to the desk, or so
it says. This question, I think, is more about putting a load on the
server rather than the throughput speed. As always, any help would be
greatly appreciated. Thank you

You're over-worrying a bit in my opinion. Just use bound forms based on
queries or with a WHERE clause that limits how many records the form will
display to the minimum (preferably one).

Even when you bind the form to a table or to a query that will include all
records the records are not pulled by just opening the form. Access will
pull a few pages of records and then pull more as needed as the user
navigates. This is not a great idea because if the user hits "go to last"
you might very well pull a lot of data needlessly so as stated above, better
to give them an interface that pulls the record(s) they need.

Generally you will want to filter rather than search. A "Find" that matches
the 10,000th record will pull a lot of data besides the record that matches
the find. A Filter using the same criteria will pull just those records
that satisfy the filter and nothing else.

 

[Armen Stein]

After a few years of working with Access, I realize that there are about five
ways to do anything. With that in mind, what I am looking for is an opinion
on the "proper" way to design a professional database. The issue is this. I
have an ODBC connection (System DSN) to a large application that hundreds of
people are using every day. The primary table has forty thousand records and
when my database accesses the tables I don't want to slow down the system,
nor do I want my form to take 45 seconds to open, which it now does. I have
read through the suggestions here for speeding up a database, but I am not
sure which one is the "best" idea. Should I use many make table queries and
create temporary tables, which negates the "live" connection benefits and
requires someone to push an update button every now and then? Should I get
the form to open with recordsets (are they faster?) and pull just one record
at a time? (how do you do this?) Is that the way to do it or is there another
way to do this? If an expert out there were to design a top of the line
database with an SQLServer backend, how would you establish the record source
for your queries, forms and reports? The company requires that I use Access
97, DAO obviously, and I don't know the version of the SQL Server or the
speed. The network connection is 1GB to the desk, or so it says. This
question, I think, is more about putting a load on the server rather than the
throughput speed. As always, any help would be greatly appreciated. Thank you

This is a classic client-server application using Access & SQL Server.
We've built a lot of these. I've posted the techniques we use in the
file The Best of Both Worlds at
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp.

The bottom line is to minimize traffic over the wire. Access by
default can be very "chatty". Using a judicious mix of passthrough
queries and linked tables can reduce the chatter to a minimum and
result in very good performance.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com