adp vc mdb

[Souris]

I will develop a new application using SQL server as my backend.

I would like to know what will be my best choice between adp and mdb.

What are the benefits to use either one?
What should I know in advance between them?

your information is great appreciated,

 

[Albert D. Kallal]

If you have a large complex and existing application that you wish to
migrate to SQL server, then it's a very easy decision, and you should simply
stick with the standard MDB file, and simply link the tables the SQL server.

if you're developing something from scratch, then the issue becomes a little
bit more difficult. often I find it's still far superior to start designing
the application with the forms and even the tables in a simple one mdb file
that allows you to rapidly create tables modify fields etc, and simply just
work very efficiently as you design and build the application.

As the application begins to get more mature and takes more serious form,
then you run the databae splitter and send the data back and data out to a
separate mdb file.

Then at this point you can consider migration of your back end database to
the SQL server. The advantage of this approach is that you don't really have
to deal with SQL server during your development process. Some development of
using SQL servers features like stored procedures should be part of the
development process. I guess I'm saying it's simply not a bad idea to use MS
access without SQL server for the rapid prototyping portion of the
application development process.

The main advantages of an ADP project is that all of your queries are by
default pass through queries to SQL server. What this means is an ADP
project is more forgiving in terms of you doing stupid things that will hurt
performance.

I think if you're a seasoned MS access developer, and you know exactly what
you're doing and have good solid design in terms of performance, then I
would go mdb way.

If you don't know what you're doing and are not particularly skilled in
ms-access, then you'll likely get better performance with an ADP project. An
ADP project also allows you to develop and use your table designs with out
having to use the SQL server tools. if you use a mdb file then for table
designs and setting up of the database, you'll have to use the SQL server
tools (which is probably a good thing to learn in the first place anyway).

Much of using these tools really comes down to your skill levels
you gained over the years.

The #1 consideration is at what level the developer(s) are at. There are
certainly more levels then just "trained" or "not trained". Generally there
are a "lot" of skill levels, but the following breakdown is sufficient. **

Stage 1 Innocent (never heard of the product)

Stage 2 Aware (Has read an article about X)

Stage 3 Apprentice (has attended a three-day seminar)

Stage 4 Practitioner (ready to use X on a real project)

Stage 5 Journeyman (uses X naturally and automatically in his job)

Stage 6 Master (has internalized X, knows when to break the rules)

Stage 7 Expert (writes books, gives lectures, looks for ways to extend x)


One should NEVER attempt a project with a team consisting with Stage 3 or
lower people. This is a sure fire formula for failure. The team can consist
of stage 4's, but they should have at least access to Stage 5, or 6.

So another consideration here might be how familiar you are with SQL server
tools. ADP's are more forgiving, but also are somewhat less flexible
because you will not be using dao anymore, and don't have local tables.

So, if you familiar with dao, then that would be a consideration also.

 

[Souris]

Thanks for the message,
Is ADP more efficient for reponse time, since it does not have local table?
Everything runs on the server and return only client request?
ADP uses direct link to database which save times to configure ODBC
connection on every single client machine.

If ADP has better performance then MDB then what ware the drawback on ADP?

Thanks again,

 

[Rick Brandt]

Thanks for the message,
Is ADP more efficient for reponse time, since it does not have local
table?

Not that I have heard or read.

Everything runs on the server and return only client request?

As is mostly the case with an MDB. Making it always the case is possible, but
is a waste of development time. Better is to re-write those queries as
passthroughs or stored procedures that actually have performance issues than all
of them.

ADP uses direct link to database which save times to configure ODBC
connection on every single client machine.

This can be automated or one can link tables using DSNless connections thus
eliminating any per-PC setup.

If ADP has better performance then MDB then what ware the drawback on
ADP?

The performance premise is false. The real drawbacks of ADP had very little to
do with performance, but in the other differences. In reality the architecture
of each has advantages and disadvantages with the consensus being that MDBs have
the overall edge. In addition, real problems with ADPs that could have been
resolved my Microsoft were never addressed or were addressed poorly. For myself
I was never interested in them because I am not a strictly SQL Server back end
user and ADPs cannot work with anything else.

 

[Souris]

Thanks for the message,

It is nice to know DSNLess connection.

I look on the internet.
I only found DSNLess connection for ASP and ColdFusion.
Can you please give me more information how to use DSNLess connection using
MS Acess?

Yuor information is great appreciated,

 

[Douglas J. Steele]

See http://www.accessmvp.com/DJSteele/DSNLessLinks.html for one approach.

 

[David W. Fenton]

I will develop a new application using SQL server as my backend.

I would like to know what will be my best choice between adp and
mdb.

For what it's worth, Microsoft itself is now recommending MDB over
ADP. If you'd like a URL for that recommendation, let me know.

 

[Souris]

Thanks for the message,

Can you please give me the URL?
Thanks again,

If I understand correctly, as long as I use pass through query then MDB and
ADP have same performanace.

I must use local query for the sub form.

MDB supports local table, but ADP does not.

ADP only support MS SQL server. It is impossible to chage back end in the
future if I want to change it.



Please let me know if I am wrong,

Thanks millions,

 

[David W. Fenton]

Can you please give me the URL?
Thanks again,

http://technet2.microsoft.com/Office/en-us/library/1dce641e-ba1c-446a
-8ff2-221769a58ba51033.mspx?mfr=true

(get rid of the word wrap, with no spaces)

The key section:

Access Data Projects (ADPs)

An Access Data Project is an OLE document file, like the .xls
or.doc file formats. It contains forms, reports, macros, VBA
modules, and a connection string. All tables and queries are
stored in SQL Server. The ADP architecture was designed to create
client-server applications. Because of this, there is a limit to
the number of records that Access returns in any recordset. This
limit is configurable, but you typically must build enough
filtering into your application so that you do not reach the
limit.

Access uses OLEDB to communicate with SQL Server. To provide the
Jet-like cursor behavior desired for desktop applications, Access
implements the Client Data Manager (CDM) as an additional layer
between Access and OLEDB.

Because of the layers required to get from Access to SQL Server
in the ADP architecture, it is often easier to optimize MDB/ACCDB
file solutions. However, there are some scenarios where a report
might be generated significantly faster in an ADP file. To add
these performance improvements and retain the flexibility of SQL
Server, you can build the majority of the application in an MDB
or ACCDB file and have the file load reports from a referenced
ADP file.

One advantage that ADP files have over files in MDB or ACCDB
format is the ability to make design changes to SQL Server
objects. ADP files include graphical designers for tables, views,
stored procedures, functions, and database diagrams.

If I understand correctly, as long as I use pass through query
then MDB and ADP have same performanace.

It's not quite that simple. The easiest way to do this is to write
your SQL in Access and if it has a problem, then create views or
stored procedures in SQL Server, or try pass-through. Jet actually
does a surprisingly good job of guessing what's best to send to SQL
Server.

The place where it does badly is with INSERT and UPDATE queries with
multiple records being updated, because (for good reasons) it sends
one row at a time to SQL Server (which allows the SQL Server to
serialize the requested operation in small bits so as to not hog all
the resources). In those cases, a pass-through or stored procedure
is much faster.

I must use local query for the sub form.

You'll use local queries for everything. Some of those local queries
may be pass-throughs.

MDB supports local table, but ADP does not.
Yes.

ADP only support MS SQL server. It is impossible to chage back end
in the future if I want to change it.

Yes. However, an MDB optimized for ODBC to SQL Server will not
necessarily perform well with another back end database engine over
ODBC, as the optimizations may need to be completely different, and
because Jet will be depending on the ODBC driver for figuring out
what to send to the server. It's probably marginally easier to
convert an MDB to a different ODBC back end than to convert from a
Jet back end to SQL Server.

All that said, if there's any contemplation of any back end other
than SQL Server, then an ADP would likely be an unwise choice.