Best practice to be prepared...

[Ronald]

Hi.

I hope anyone can give me some guidelines in programming my frontend
database to be prepared for upsizing my growing data database.
Should I use ADO instead of DAO library calls?
Should I use separate queries instead of 'embedded' queries?
Now I use (a lot of) code to build flexible queries. Will it be possible to
transfer such a created query to the SQLserver to minimize the quantity of
data send over the network?

Thanks in advance,

Ronald.

[Alex Dybenko]

Hi,

Post by Ronald
I hope anyone can give me some guidelines in programming my frontend
database to be prepared for upsizing my growing data database.
Should I use ADO instead of DAO library calls?

I suggest to stay with DAO, this is a native library for access and
recommended approach by Microsoft

Post by Ronald
Should I use separate queries instead of 'embedded' queries?

For read-only queries best approach is to make pass-through queries, so
they run on SQL server.

Post by Ronald
Now I use (a lot of) code to build flexible queries. Will it be possible to
transfer such a created query to the SQLserver to minimize the quantity of
data send over the network?

Yes, best is to move as much as possible processing to SQL server, so you
get only result on the client.

[David W. Fenton]

Post by Alex Dybenko
best is to move as much as possible processing to SQL server, so
you get only result on the client.

I disagree. Jet will do its best to figure out the most efficient
processing, and most of the time, it will automatically forward your
query to the server for processing there. The only reason to move
anything server side is if you find that Jet makes the wrong guess
and is doing too much processing locally (or sends something very
inefficient for the server to process).

In other words, convert without changes, and test. For the things
that perform poorly, re-architect them, which may include moving all
or part of them to the server.

[Alex Dybenko]

Hi David,
I only agree that jet will do his best. But jet is not perfect in this case,
once you get large database - you stuck with locks issues, then you had to
rewrite queries.
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by David W. Fenton

Post by Alex Dybenko
best is to move as much as possible processing to SQL server, so
you get only result on the client.

I disagree. Jet will do its best to figure out the most efficient
processing, and most of the time, it will automatically forward your
query to the server for processing there. The only reason to move
anything server side is if you find that Jet makes the wrong guess
and is doing too much processing locally (or sends something very
inefficient for the server to process).
In other words, convert without changes, and test. For the things
that perform poorly, re-architect them, which may include moving all
or part of them to the server.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Post by Alex Dybenko
I only agree that jet will do his best. But jet is not perfect in
this case, once you get large database - you stuck with locks
issues, then you had to rewrite queries.

I don't see how the size of your database has anything to do with
locks, to be honest.

But you're not really contradicting what I said -- upsize and try it
as is, and then fix what isn't efficient.

[Alex Dybenko]

You can't control locks in queries generated by Jet, this is an issue. Try
to load a 5 thousands records in a form or combobox, using access queries,
in multiuser environment, and then try to update it - you will get a dead
lock
Same time on one hundred table you never get such problem
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by David W. Fenton

Post by Alex Dybenko
I only agree that jet will do his best. But jet is not perfect in
this case, once you get large database - you stuck with locks
issues, then you had to rewrite queries.

I don't see how the size of your database has anything to do with
locks, to be honest.
But you're not really contradicting what I said -- upsize and try it
as is, and then fix what isn't efficient.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Post by Alex Dybenko
You can't control locks in queries generated by Jet, this is an
issue. Try to load a 5 thousands records in a form or combobox,

...something you should never do?

Post by Alex Dybenko
using access queries,
in multiuser environment, and then try to update it - you will get
a dead lock
Same time on one hundred table you never get such problem

Isn't the first principle of client/server database user interface
design to only retrieve the number of records your user can actually
use? That's even the way I design my apps using Jet back ends.
There's no way that a 5,000-row combo box is appropriate in any
environment.

So, it seems to me that if you've designed your app correctly,
you're not going to encounter these locks you're talking about --
it's only when you've done a bad job to begin with that it's a
problem. And if you encounter the problem, it's just a situation
where, as I said, you'll have to re-engineer to work with your
upsized back end.

But I believe that a properly designed app will have very few of
these points where Jet makes the wrong decision on how to retrieve
the data.

[Alex Dybenko]

If client wants this, and there is a way to make it running - I do not see
any problem.
5,000-row combo box works ok with pass-through query as rowsource and
nolocks hint
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by David W. Fenton

Post by Alex Dybenko
You can't control locks in queries generated by Jet, this is an
issue. Try to load a 5 thousands records in a form or combobox,

...something you should never do?

Post by Alex Dybenko
using access queries,
in multiuser environment, and then try to update it - you will get
a dead lock
Same time on one hundred table you never get such problem

Isn't the first principle of client/server database user interface
design to only retrieve the number of records your user can actually
use? That's even the way I design my apps using Jet back ends.
There's no way that a 5,000-row combo box is appropriate in any
environment.
So, it seems to me that if you've designed your app correctly,
you're not going to encounter these locks you're talking about --
it's only when you've done a bad job to begin with that it's a
problem. And if you encounter the problem, it's just a situation
where, as I said, you'll have to re-engineer to work with your
upsized back end.
But I believe that a properly designed app will have very few of
these points where Jet makes the wrong decision on how to retrieve
the data.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Post by Alex Dybenko
5,000-row combo box works ok with pass-through query as rowsource
and nolocks hint

It's very user-hostile, in my opinion. I would probably only assign
a rowsource after the user has 2 or 3 characters.

[Alex Dybenko]

Hmm, looks like a good idea, but what if you have this combobox in
continuous or datasheet form?
Imagine order form and products combobox
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by David W. Fenton

Post by Alex Dybenko
5,000-row combo box works ok with pass-through query as rowsource
and nolocks hint

It's very user-hostile, in my opinion. I would probably only assign
a rowsource after the user has 2 or 3 characters.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Post by Alex Dybenko
what if you have this combobox in
continuous or datasheet form?
Imagine order form and products combobox

One principle I use is that continuous forms are never editable. My
usual method is to use the continuous form as a listbox with
extended capabilities, and use a single form bound to the PK of the
continuous form.

Of course, there are cases where that is overkill. In any invoice, I
don't think I'd bother, for instance. The problem of needing to
assign a rowsource dynamically is caused, in my opinion, by bad
design. I don't think a combo box is the right interface for
choosing a record from a 10,000-record table. I'd likely pop up
something for that.

[Mary Chipman [MSFT]]

In general, moving data access from code to saved, standalone queries
will help you migrate the logic to SQL Server. In order to minimize
the amount of data sent over the wire you'll want to port your data
logic to stored procedures or parameterized T-SQL statements, and
standalone queries will facilitate that. In terms of DAO vs. ADO, the
rule of thumb is: when coding against Jet use DAO; when coding against
server-side data objects, use ADO. If you use DAO against SQL Server,
then you are needlessly loading the Jet engine, which doesn't happen
when you use ADO. If you are coding against local Jet objects, such as
pass-through queries or linked tables, then DAO is the best choice in
terms of speed and functionality.

--Mary

Post by Ronald
Hi.
I hope anyone can give me some guidelines in programming my frontend
database to be prepared for upsizing my growing data database.
Should I use ADO instead of DAO library calls?
Should I use separate queries instead of 'embedded' queries?
Now I use (a lot of) code to build flexible queries. Will it be possible to
transfer such a created query to the SQLserver to minimize the quantity of
data send over the network?
Thanks in advance,
Ronald.

[David W. Fenton]

Post by Mary Chipman [MSFT]
In general, moving data access from code to saved, standalone
queries will help you migrate the logic to SQL Server. In order to
minimize the amount of data sent over the wire you'll want to port
your data logic to stored procedures or parameterized T-SQL
statements, and standalone queries will facilitate that. In terms
of DAO vs. ADO, the rule of thumb is: when coding against Jet use
DAO; when coding against server-side data objects, use ADO. If you
use DAO against SQL Server, then you are needlessly loading the
Jet engine, which doesn't happen when you use ADO. If you are
coding against local Jet objects, such as pass-through queries or
linked tables, then DAO is the best choice in terms of speed and
functionality.

It seems to me taht the crux of the advice is in the last sentence.
If you're using and MDB with ODBC, then DAO is by all means the best
approach.

It seesm to me that ADO is only useful if you intend for some
bizarre reason to work against the strengths of Access and not
utilize linked tables.

Jet is remarkably efficient and smart in working with ODBC data.
Yes, it makes mistakes, and in those cases, you can move part or all
of the data processing to the server. But in general, in upsizing to
SQL Server when using ODBC linked tables, the best practice is: If
it ain't broke, don't fix it. That is, most of it is going to Just
Work. What doesn't work, you redesign to make better use of the SQL
Server.

But you shouldn't *start* with moving things server side -- you
should start with doing NOTHING except testing your upsized app.

[Ronald]

Thank you both very much!

Post by Ronald
Hi.
I hope anyone can give me some guidelines in programming my frontend
database to be prepared for upsizing my growing data database.
Should I use ADO instead of DAO library calls?
Should I use separate queries instead of 'embedded' queries?
Now I use (a lot of) code to build flexible queries. Will it be possible to
transfer such a created query to the SQLserver to minimize the quantity of
data send over the network?
Thanks in advance,
Ronald.

[Armen Stein]

Hi Ronald,

Alex, David and Mary have given you good advice, not always in
complete agreement, just at different places on a spectrum of opinion.
How's that for diplomacy guys? <s>

I agree with David that often just linking tables will provide
adequate performance. More complex queries, especially subqueries,
will not get processed by SQL Server and will benefit greatly by using
passthroughs instead. But, David's approach overlook a performance
problem that doesn't appear when testing with small amounts of data,
but then chokes with production data. It isn't linear. You have to
test with realistic data sets to see what you'll really get. So, I
also agree with Mary and Alex that to *guarantee* the most power,
passthroughs are best, but they take more effort to develop.

We generally use DAO for everything except calling stored procedures
in VBA (for that we use an ADO command object).

Your code that builds queries should be limited to changing Where and
Order By clauses. The Select structures should be tested and stored
in the query and the other clauses added/modified in code when
necessary. I've debugged so many problems with code making
inadvertent changes to the basic query structure at run time.

Check out a slide deck I created that covers a lot of the techniques
we use to build Access-SQL Server client-server applications, called
Best of Both Worlds at http://www.jstreettech.com/downloads.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

[Ronald]

Thank you ALL very much!!!

Ronald.

[AlexM]

If you are developing a solutions that will handle millions of records and
more than 5 users and you have time use ADO. Time is the key.

If the database is small and not many users you can use DAO.

DAO is faster to develop, (slower in performance) and it maintains your
connections to the DB.
With ADO there is a lot more work, but the apps are a lot more robust and
easier to manage once done.
I prefer to learn a more comprehensive solution.
DAO is really Access to Access for small stuff.

ADO and OLEDB is everywhere and when you master that, you will easily
develop .net, web etc.

Good luck

Post by Ronald
Thank you ALL very much!!!
Ronald.

[David W. Fenton]

Post by AlexM
If you are developing a solutions that will handle millions of
records and more than 5 users and you have time use ADO.

That is, design an unbound application.

Once you go unbound, there is simply very little reason to use
Access, in my opinion.