Access adp vs mdb

[Mitch]

What is the advantage of adp over mdb? I have a Access mdb (split) that I
have MAJOR code behind the forms... This thing has evolved into quite the
application as well as a DB... It has over 6000 records in the main table
and probably 15 subtables... I want to know if I should consider migrating
the backend to SQLServer (Have it already with SBS2K but not using it) ?
Will I lose the functionality of some or all of the code I have written
behind these forms? Or will the transistion be seemless from a user's
standpoint? I hesitate to migrate the back end to SQLServer since I have
zero experience with it... I have about 20 users (probably 8 concurrently at
any given time) and I do plan on getting the db to the web eventually
somehow for remote users....

Mitch

 

[Jerry]

You should seriously consider migrating to SQL server with that number of
users. Mirgating as such is an automatic process, but I woould reccommand
seriously to look at changing any action for whihc you have writen code in
Access and which are file or table bound to stored procedures in SQL Server.
This is not a 5-minute job, but the benifits can be great in terms of speed
and increase flexibility for future changes. I would take the Access
aaplciation, mograte it and than make a new project copy teh existing
screens and look at which part of the code shoudl be server based and which
part should be client based; also you will have to know something about how
you can access data in SQL Server tabels using ADO.

 

[Kevin3NF]

Speed. ADP projects do not use the JET engine, they use the SQL Server
engine, which runs on your SBS server (assumes your server is more powerful
than your users' desktops).

This is not a project to be undertaken lightly, as many of your objects may
not convert nicely over to SQL Server. Basic select queries will, but
parameter queries need to be converted to stored procedures. If you have no
experience with SQL Server, you may want to consider moving the tables to
SQL Server and linking your front-end MDB file via ODBC, instead of going to
ADP. Kind of like a half-way move. This will also better serve your data
up for the coming web application you mention.

8 users is not that many for a well-designed Access app, nor is 6000 records
all that much. I have a client with 125K records in their main table and
several hundred thousand in the remaining tables combined. It works lke a
champ in the ODBC configuration I mentioned above, but I am moving it to ADP
for speed reasons, and to be able to use some of SQL Server's other
functions.

 

[Mary Chipman]

There are no advantages of an ADP over an MDB and you in fact give up
a great deal of flexibility in the process. A hybrid app may make more
sense in your environment, where static data can be stored locally on
a user's front-end (for loading combo boxes, etc), a possibility that
doesn't exist in an ADP unless you write a ton of code to store it
locally in XML files.

It also depends on how much users customize their own environment
(such as writing their own queries, etc) and how much you do in code.
A DAO-code heavy app will require a considerable about of
rearchitecting and rework, and users won't be able to save data
objects in an ADP without saving them on the SQL Server (which you
definitely don't want to do).

Make sure you're getting the most out of Jet before you jump by
reexamining your existing architedure, using unbound techniques and
fetching only needed data. 6000 records is not a lot of data and 8
concurernt users not a lot of users if you're not getting conflicts.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

 

[Paul]

Mary

I am being asked by a client to rewrite all of my reports and associated
queries because they run too slowly from an Access frontend mdb with linked
tables via odbc to sql server database mdf. These are very complex queries,
in some cases, involving 10 or more tables with inner and outer joins
(little wonder they run slowly). In your reply to the above post you stated
"A hybrid app may make more sense in your environment, where static data can
be stored locally on a user's front-end (for loading combo boxes, etc)". My
frontend makes extensive use of combo and list boxes, filters, etc,
particularly on the report interface.

I have two questions, first could you describe in a little more detail, the
general concept of this "static data" approach, and secondly, does your book
show how to do this in some detail?

Thanks,

Paul

<snip>

 

[Brian M. Sockey]

As far as your reports go, take a serious look at using pass-through queries
instead of queries based on linked tables. Because of the way Jet works
with queries based on multiple linked ODBC tables, a LOT of data is brought
down over the network to join locally before the actual data to be used for
the report is ever fetched. This can be a very inefficient process.

I have seen reports that previously took hours drop to only a couple of
minutes just by switching to pass-throughs. Reports based on multiple ODBC
tables should always be based on a pass-through query, with very rare
exceptions.

-Brian M. Sockey

 

[Paul]

Brian

Thanks for your comments re: pass-through queries. I have timed several of
the reports. One in particular that has 12 tables with joins ran under an
access frontend (installed on users' machines) and access backend (installed
on a network server) in less than 2 seconds. One of the primary tables has
3600 records, and another has 15,000 records. The tables are properly
indexed. After creating the backend as sql server database this report runs
in 1 minute and 46 seconds.

I then created a pass-through query for this same report - the result: it
ran in 1 minute and 35 seconds. I have also split the query into separate
statements and run the report with a function to see if this would help. No
go, same result. My tentative conclusion is that this query (and typical of
many of the ones that run my reports) is just too complex for sql server to
handle. I think the only way I'm going to speed things up is to create a
stored procedure in the sql server database, however, the user interface
form has so many pull-down boxes, filters, etc. that I cannot ask the users
to fill in popup dialog boxes each time they want to run a report. They have
used this database as a daily working tool for over 6 years and to now ask
them to do something that is both time-consuming and frustrating would be
unacceptable to them and me.

Thus, my question to Mary about her statement "where static data can be
stored locally on a user's front-end (for loading combo boxes, etc)," I just
don't know how to do this. I don't know why the IT dept. of this company
just couldn't leave well enough alone, they changed their internal standards
for all servers from windows boxes to apache boxes running linux.

If you have further ideas or comments, I would welcome them.

Paul

 

[Mary Chipman]

If you have a reporting app, then you need to code all of your
queries, joins, aggregates and so on in stored procedures. That way
all joins, etc., are performed on the SERVER not on the CLIENT. You
then execute your stored procedures "EXEC MyProcName" in a
pass-through query. You then base the report on the pass-through
query, where the results are read-only (but you don't care because the
the report is read-only). The only thing that Access should be doing
on the front-end is formatting the report -- all data processing
should be done on the back end in stored procedures. If your app is
only for reporting, you could probably get away without having a
single query or linked table on the front end. I can guarantee that
this will solve your performance problems (unless you've somehow
neglected to create any indexes on the back-end tables). Yes, the book
covers this.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

 

[Paul]

Mary

Thanks for the detailed response. I have ordered your book and it will
probably have the answers to my other questions. However, if you have the
time, I am curious about how the values in pull-down boxes get passed to the
stored procedures. I have been developing Access dbs for many years but am
new to sql server. In the reading I've done so far, I can't find anything
that addresses the above issue. I understand the concept of stored
procedures and executing them via pass-through queries, but not how to pass
values from access forms to the stored procedures.

Other issues you raised: All tables are properly indexed. The db is not only
for reporting but also data input. I currently have all tables linked via
odbc and the data input forms function properly and quickly. It's only the
reporting that is the problem.

Paul

 

[Brian M. Sockey]

Hi Paul,

I would have to analyze the queries myself to pinpoint the cause for certain
but I have rarely run into a case where a properly designed query takes so
much longer as a pass-through than as a query against linked ODBC tables.

First, when comparing time differences, be sure to compare the time it takes
to view the last record on a given query or report and not how long it takes
the first record to pop up. Jet can frequently display the first records
faster than a pass-through but the overall speed to view all records is
slower.

Secondly, time your queries without the report to verify whether it's the
queries themselves or the way they are being used in the report that is
causing the speed problem. For example, if you are using subreports,
Access is forced to do some local joining to display the relevant subreport
data for each record of main report data. Sometimes it is necessary to
change from a report/subreport design to a single more-complicated
pass-through query with a single report that uses grouping to display the
hierarchical relationship in the data.

Thirdly, just to clarify, your report needs to be based on a single
pass-through query whenever possible. If you just change your linked tables
to pass-throughs and then select from multiple pass-throughs, the joining is
still done locally and you have not moved the processing to SQL Server.
The "where" clauses and joining need to be defined in the text of the
pass-through itself and not in the recordsource property if the report.
If you are doing this because you need to specify criteria for the report,
then you should change the text of the report's pass-through query
programmatically, not the recordsource of the report.

Again, it all depends on how the data is structured. It's hard to point to
a specific cause without being able to reproduce the problem myself. It is
certainly not the case that the query is too complex for SQL Server to
handle. However, poorly structured queries usually need to be redesigned
to take best advantage of SQL Server's client-server nature.

Hope this helps,

Brian M. Sockey

 

[Paul]

Hi Brian

Thanks for hanging in there with me. First, to answer a few of your
questions. The report is based on a single query and it is the recordsource
for a single report with no subs. I was also timing the query itself, not
the report. The report does have a bit of formating, but that does not
change the response time appreciably.

I believe that you have hit upon the key issue though when you say that the
query usually needs to be redesigned to take best advantage of SQL Server's
client-server nature. I believe the problem lies with the way I have written
the criteria statements in the "where" clause. I did an experiment in which
I simply entered a set of specific criteria values (there are 4 possible for
the user to select) and the query ran almost instanteously.

I appreciate your diagnosis and assistance. Now, all I have to do is
redesign and rewrite this monster. Thanks again.

Paul

 

[Mary Chipman]

What you need to do is construct a string in your code that you use to
set the .SQL property of a QueryDef object. You can either create a
new one, or use an existing one and just re-set the SQL property.
Consult your DAO documentation. The string incorporates the exact
syntax you'd use if you were executing the sproc in the Query
Analyzer. This string then gets "passed through" to SQLS to be parsed
and executed, bypassing Jet entirely. You need to pay attention to
delimiters, etc. Using QA in conjunction with VBA helps a lot, the
same way you'd use the query designer in Access to parse query syntax.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

 

[Paul]

Mary and Brian

Many thanks to the two of you for your effort and very detailed
explanations. I now have a much better conceptual overview of how to
approach this task. Now it's just a question of design and coding, hopefully
without too many frustrating delimiter errors, etc.

Regards,

Paul

 

[Mary Chipman]

Use the Query Analyzer to create and test any SQL. Accept no
substitutes

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446