Re: Question on conversion to ADP
- From: "Mary Chipman [MSFT]" <mchip@xxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 20 Nov 2008 09:18:43 -0800
You raise many valid points. I have always publicly and privately
maintained that Microsoft wrongly neglected Access developers, but
unfortunately I'm pretty far down the food chain, so I'm pretty much
ignored every time I make that case :)
If you want security, you must ALWAYS implement it on the data source,
in this case Windows/SQL Server. Nobody ever maintained that you could
secure an application through Access or the ODBC API. In the case of
an Access-SQL app, that means using integrated security, granting EXEC
perms on stored procedures and denying all direct access to tables
while creating an "unbound" front-end that passes stored procedure
parameters through VBA/pass-through queries. The linked table approach
is good here because you can fetch read-only data and cache it on the
client, reducing network/server load. Of course, you have to handle
concurrency yourself, opening another can of worms. An ADP is harder
to implement as "unbound", not easier.
That being said, there will always be tradeoffs between security and
flexibility. If you want the secretary to create data objects, then
you're not going to have a secure app no matter which approach you
choose.
The other point on which we concur is that there isn't enough
help/guidance coming from Microsoft for Access developers trying to
make the decision whether to use ADPs or linked tables for their SQLS
apps. As I said before, every application is unique, and people have
different business requirements. So I've taken to posting this list of
resources which will help people decide what works for them. If I've
missed any that you think will help, please let me know and i'll add
them to the list.-- Mary
TechEd Online Panel (video):
Go to http://msdn.microsoft.com/en-us/events/teched/cc676818.aspx and
search for:
"Are we there yet? Successfully navigating the bumpy road from Access
to SQL Server"
Microsoft Access or SQL Server 2005: What's Right in Your
Organization?
http://www.microsoft.com/sql/solutions/migration/access/sql-or-access.mspx
Optimizing Microsoft Office Access Applications Linked to SQL Server
http://msdn.microsoft.com/en-us/library/bb188204.aspx
What are the main differences between Access and SQL Server?
http://sqlserver2000.databases.aspfaq.com/what-are-the-main-differences-between-access-and-sql-server.html
"The Best of Both Worlds--Access MDBs and SQL Server"
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp
SQL Server Migration Assistant for Access (SSMA for Access)
http://www.microsoft.com/sql/solutions/migration/access/default.mspx
FMS Upsizing Center
http://www.fmsinc.com/Consulting/sqlupsizedocs.aspx
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/dp/0672319446
On Wed, 19 Nov 2008 12:35:25 -0500, "Sylvain Lafontaine" <sylvain aei
ca (fill the blanks, no spam please)> wrote:
Oh, everyone here agree that ADP is a dead technology; doesn't take a big.
brain to figure that but it's not because ADP is inferior to ODBC linked
tables but simply because MS has took the decision to put practically all
the money toward developping .NET technologies instead of Access. Unless
you're developping a database for storing the kitchen's recipes of your
great, great, grand-mother or the friday night's hockey pool of your local
pub, the gentle push that you're talking about is not toward ODBC linked
tables but toward .NET. In MS's opinion, Access is geared toward usage, not
development. This is why there is no Access certification at the present
time. It's hard to believe that MS itself is taking Access seriously when
there is not even any certification for it.
In your previous post, you have said the following statement: « Basically it
boils down flexibility, security and managing server-side objects. ... ».
Well, then, first, we'll take a look at the word "flexibility". If ODBC
linked tables are so flexible, can you explain to me why it's impossible to
make any little complex statements without having JET saying that the
expression is too complexe, to heavy on ressources, crashing or - in the
best case scenario - see the performance dropping faster than a rock in free
fall? Why is it that in 2008, something as simple as the following
statement:
SELECT T1.*, T2.*
FROM T1 LEFT JOIN T2 ON (T1.Id1 = T2.Id2 and T2.Id2 = 10)
make Access 2003 (didn't took the time to check for other versions) crash if
T1 and T2 are ODBC linked tables (and that it will also crash for a lot of
other simple constructs involving outer joins, subqueries and union
queries).
Make Access capable of doing some little complexe statements against ODBC
linked tables without crashing a regularly as crash test dummy and we could
talk about the technical capabilities of JET's ODBC linked tables. Make the
result of passthrough queries read/write instead of read only and also make
them available as record source for subreports and subforms for continuous
forms and maybe that some people dealing with real enterprise databases will
start looking at this stuff as potentially useful.
And while you're on it, take also the time of solving your case-sensitivity
and collation problems. The fact that in 2008, JET's queries against ODBC
linked tables are still not offering full support for Unicode is practically
unbelievable.
Second, you have wrote the word "security". So you really think that OBDC
linked tables are anything but unsecure? If someone were to store something
confidential like your credit card numbers, you would put your faith on an
ODBC linked table?
Question from the client: - We have a safe but we have some concerns about
the safekeeping of the key because we are keeping under the rug.
Solution from MS: no problem, we will remove it from under the mat and put
it in plain sight on a table nearby. This way, not only you're sure that
anyone around will see it but you're also sure that a thief won't hurt his
back by bending down to take it.
Client: - Great! But what happens if they are two or more thieves?
MS: - Maybe we could put a machine for duplicating keys nearby?
Client: - OK, but make sure that the user's manual is clear and easy to
follow and don't forget the blanks!
Finally, you have made a mention about the difficulty of managing
server-side objects. Great, the next time that a secretary will call me for
a request for a new set of data, I tell her that she'll have to look herself
at the database's schema and make her own set of queries. This is for
reading. Now, for writing, I wonder how many hours it will take before the
database become corrupted when the users will start making their very own
updating statements.
Your notions of security and centralized management of database objects
could be applied to a list of kitchen's recipes but I don't see how anyone
would use those for an enterprise level database or any other serious
business model.
You're totally right when you said that ADPs have their share of problems.
However, I'm afraid that the ADP's set of problem is only a very small
subset of the whole collection held by JET's ODBC linked tables and
passthrough queries. It's also true that MS is actually pushing people out
of ADP but what they are pushed toward is - how could I say that politely -
"questionable".
- References:
- Question on conversion to ADP
- From: Dave
- Re: Question on conversion to ADP
- From: Mary Chipman [MSFT]
- Re: Question on conversion to ADP
- From: Mary Chipman [MSFT]
- Re: Question on conversion to ADP
- From: Sylvain Lafontaine
- Re: Question on conversion to ADP
- From: Mary Chipman [MSFT]
- Re: Question on conversion to ADP
- From: Sylvain Lafontaine
- Question on conversion to ADP
- Prev by Date: Re: Split Forms and ADP issues
- Next by Date: Re: Question on conversion to ADP
- Previous by thread: Re: Question on conversion to ADP
- Next by thread: Re: Question on conversion to ADP
- Index(es):
Relevant Pages
|
