Question on conversion to ADP

[Bob Snedden]

Dear Sylvain,

I seem to have come into the middle of a debate about ADP/Access/COM/DCOM
and I wish I could give a considered opinion however:-

I am a hard working database designer, working for myself and I sense my
livelihood is threatened. Would you be so kind as to answer the following Q's

1 - Can I do design changes in Access 2003 to a SQL 2005 based ADP (Service
Pack?)
2 - Should I now convert to Access2007 if the answer to 1 is NO
3 - If clients want ADP's, as mine seem to do, for the low end database
applications should I be recommending that SQL 2000 is retained for such
applications

Thank you
--
Bob Snedden, MCT, MCSD.Net

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

I think your point of view is belied by the actions of the Access
development team in the recent time frame. Access has a new life
both as end-user tool and as developer platform, precisely because
of the decisions made in preparation for Access 2007.

Seems to me that MS has almost returned to the point of view about
Access that it had c. Office 95/97, where they were pushing Office
as a development platform for small and medium-sized businesses. MS
went way, way off track with Office 2000 and Access 2000, in my
opinion, and it's taken them this long to get back to the right
path.

In my opinion, ADPs were a bad idea based on stupid prejudices
rather than technical merit/need, and not really a
properly-implemented development platform. The development of ADPs
sapped resources that should have gone into the core Access
application. The fact that ADPs really are dead now (thankfully)
just points out the waste of resources. How much better Access could
have been for all users if they'd not been led down the garden path
by the crazy Enterprise development ideas that mostly came from
irrational fear of Jet.

Nice talk but totally beside the point. ADO (and ADP), VBA, VBScript, VB6,
etc., are all technologies based on COM/DCOM and MS is in the process of
killing all technologies based on these because you cannot make real object
oriented programming with COM/DCOM; only some sort of simulation; unlike
..NET which has been designed as an object oriented programming platform from
the ground up.

First, they have killed DNA (Distributed Network Architecture) before it got
even the chance of getting out of the laboratoray; then their next target
has been VB6 as well as a lot of smaller associated technologies like ADO,
VBScript, ASP Classic; etc. Did you really think that after having killed
these very big pieces of development platforms that were VB6, ASP Classic
and VBScript, that MS would be giving some serious toughts about keeeping
VBA and DAO in the long term?

I don't really understand how you can say that Access 2007 has got a new
life as a development platform when practically all the technological
development that have been made these last years are based on .NET and the
only thing that Access got was these small bones about Sharepoint; which, by
the way, are totally useless for anyone using SQL-Server as the backend.

They don't give a s**t about the technical merit/need of ADO vs DAO; all
they want is to get rid of COM/DCOM in the fatest way; so they are killing
these littles bunnies one after the other and it shouldn't take to long
before it's the turn of DAO and VBA. Now that VB6 is dead, it's totally
absurd for anyone entering the field to learn VBA and DAO (as well as ADO);
so it doesn't take a big brain to see that there is absolutely no future
there; whatever you might say. Like ADP; JET, DAO and VBA are dead, the
only difference if that some peoples using these don't know it yet.

The problem is not to know if there is a future or not for DAO and ADO
(there's none for both of them); the problem is to know what's the best
thing to do in the meantime before we can get our hands on a version of
Access with and integrated version to .NET.

 

[Bob Snedden]

Hi David,

From your post you seem to still have faith in ADP's which gladens my heart
as I have many customers who look to me for design support in this area.

I have one very important customer who has moved to SQL 2005 and I now
understand there are lots of problems associated with this move.

Are you able to advise the best resource for me to study in order to get
fully up-to-speed with what I need to know, e.g I assume I need to move to
Access2007 to be able to do design changes to the 2005 Backend from the ADP

What is your advice?

 

[David W. Fenton]

1 - Can I do design changes in Access 2003 to a SQL 2005 based ADP
(Service Pack?)

An ADP is an Access feature, not a SQL Server feature. What I think
you mean is can an A2K3 ADP admin a SQL Server 2005 database. The
answer is, so far as I know, yes, but with some limitations --
features introduced in SQL Server 2005 are not going to be
supported. Access 2000 had this same problem with SQL Server 2000
(which came out after A2K), and so I've always used A2K3 to do
upsizing and an A2K3 ADP for the few tasks I use an ADP for (I use
MDBs with ODBC by default and would never consider embarking on an
ADP project).

2 - Should I now convert to Access2007 if the answer to 1 is NO

It depends on what you need, and what features of SQL Server 2005
are supported in A2K7 that is not in A2K3. Keep in mind that SQL
Server 2008 is the current version, so you're still going to be
behind.

3 - If clients want ADP's, as mine seem to do, for the low end
database applications should I be recommending that SQL 2000 is
retained for such applications

Seems to me that the developer has different needs than the users,
so you should evaluate those separately. For administration and
application building, it's handy to have full support for all the
features of your SQL Server version. For the end users, there is not
necessarily going to be that same need, so an older version may
suffice.

 

[David W. Fenton]

From your post you seem to still have faith in ADP's

I don't know what post of mine you might be reading that would make
you think that. You replied to a post by Aaron Kempf, who believes
that SQL Server is the answer to every problem, include world hunger
and the problems of the US auto industry. He has no credibility on
any issue, including his alleged area of expertise (i.e., SQL
Server).

ADPs are not going to be developed any further my Microsoft. They
come in different flavors with different kinds of problems and
plusses (they changed from version to version, with broken things
fixed in the 2nd iteration, and working things broken in the same
new version).

Microsoft is not recommending ADPs for SQL Server front ends any
longer. They recommend MDBs with ODBC. They do allow that for
certain reporting tasks, and ADP can be more efficient than an
MDB/ODBC setup. But that's the only area that they recommend ADPs as
superior to MDBs.

 

[Norman Yuan]

An ADP is an Access feature, not a SQL Server feature. What I think
you mean is can an A2K3 ADP admin a SQL Server 2005 database. The
answer is, so far as I know, yes, but with some limitations --
features introduced in SQL Server 2005 are not going to be
supported. Access 2000 had this same problem with SQL Server 2000
(which came out after A2K), and so I've always used A2K3 to do
upsizing and an A2K3 ADP for the few tasks I use an ADP for (I use
MDBs with ODBC by default and would never consider embarking on an
ADP project).

To be exact, if the OP means to use Acccess2003 ADP to design SQL Server
server object, such as table/view/SP/UDF, then the answer is no, you cannot,
whether you have service pack or not. The ADP fron-end as user app in
Access2003 still runs, but to design server objects, you need Access2007.

It depends on what you need, and what features of SQL Server 2005
are supported in A2K7 that is not in A2K3. Keep in mind that SQL
Server 2008 is the current version, so you're still going to be
behind.

If the purpose to manage/design SQL Server2005 database, then yes, you need
Access2007 ADP. If it is ADP user app, then no, you do not need to. Be
aware, to manage/design SQL Server2005 database, one can use SQL Server
Management Studio/Express (latter is free download). However, ADP does
provide some convenient functiinality on this ground, such as copy/paste
server object into the same or different database in the same or different
SQL Server

 

[Mary Chipman [MSFT]]

Here's a list of resources for migrating to SQL Server and continuing
to use an Access front-end. If you are mainly concerned about the
design surfaces for creating SQL Server objects, then the Developer
Edition ($49 US) is the best solution. It has all of the features and
functionality of the Enterprise Edition, which means there are no
limits on designing and testing your SQL Server database objects. The
license agreement restricts you to single user (you need one copy per
developer) and prohibits using it as a production server. HTH,

--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.aspf...ifferences-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

 

[a a r o n . k e m p f]

Yes, stick with SQL 2000.

Or move to Access 2007.
I like a lot of things about Access 2007.

But SQL 2000 is still an outstanding platform, and it will be for a
long long long long long time.

-Aaron

Dear Sylvain,

I seem to have come into the middle of a debate about ADP/Access/COM/DCOM
and I wish I could give a considered opinion however:-

I am a hard working database designer, working for myself and I sense my
livelihood is threatened. Would you be so kind as to answer the followingQ's

1 - Can I do design changes in Access 2003 to a SQL 2005 based ADP (Service
Pack?)
2 - Should I now convert to Access2007 if the answer to 1 is NO
3 - If clients want ADP's, as mine seem to do, for the low end database
applications should I be recommending that SQL 2000 is retained for such
applications

Thank you
--
Bob Snedden, MCT, MCSD.Net

Nice talk but totally beside the point.  ADO (and ADP), VBA, VBScript, VB6,
etc., are all technologies based on COM/DCOM and MS is in the process of
killing all technologies based on these because you cannot make real object
oriented programming with COM/DCOM; only some sort of simulation; unlike
..NET which has been designed as an object oriented programming platform from
the ground up.

First, they have killed DNA (Distributed Network Architecture) before it got
even the chance of getting out of the laboratoray; then their next target
has been VB6 as well as a lot of smaller associated technologies like ADO,
VBScript, ASP Classic; etc.  Did you really think that after having killed
these very big pieces of development platforms that were VB6, ASP Classic
and VBScript, that MS would be giving some serious toughts about keeeping
VBA and DAO in the long term?

I don't really understand how you can say that Access 2007 has got a new
life as a development platform when practically all the technological
development that have been made these last years are based on .NET and the
only thing that Access got was these small bones about Sharepoint; which, by
the way, are totally useless for anyone using SQL-Server as the backend..

They don't give a s**t about the technical merit/need of ADO vs DAO; all
they want is to get rid of COM/DCOM in the fatest way; so they are killing
these littles bunnies one after the other and it shouldn't take to long
before it's the turn of DAO and VBA.  Now that VB6 is dead, it's totally
absurd for anyone entering the field to learn VBA and DAO (as well as ADO);
so it doesn't take a big brain to see that there is absolutely no future
there; whatever you might say.  Like ADP; JET, DAO and VBA are dead, the
only difference if that some peoples using these don't know it yet.

The problem is not to know if there is a future or not for DAO and ADO
(there's none for both of them); the problem is to know what's the best
thing to do in the meantime before we can get our hands on a version of
Access with and integrated version to .NET.

 

[Tony Toews [MVP]]

Yes, stick with SQL 2000.

But SQL 2000 is still an outstanding platform, and it will be for a
long long long long long time.

Why on earth would you stay with SQL 2000? 2005 and 2008 are now out
and are being updated. SQL Server 2000 is now only getting security
patches.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[a a r o n . k e m p f]

SQL Server 2000 is the best develpoment platform.

the newer flavors-- they may run stuff faster, but as a certified DBA,
I can't reccomend the latest and greatest until Microsoft comes out
with the Office 2003 SP4 which truly makes it compatible with SQL 2005/
SQL2008.

 

[a a r o n . k e m p f]

Maybe if you knew anyhting about SQL Server-- you would realize that
SQL Server has won the equation for the past decade.. it's like
you're somehow implying that only SQL 2005 is 'better' than Access or
something?

Is that what your'e saying?

But SQL 2005 doesn't have many development benefits over SQL 2000.

DDL triggers is nice-- but mainly for when it's in production.
And SQL 2005 has a lot of indexing tools.. but you can run through
those wizards before you upsize to the production / staging
environments.

 

[Sylvain Lafontaine]

Sorry for the delay; I had to give some rest to my wrist because of pain and
I forgot somehow to keep an eye on all these threads.

1 - Can I do design changes in Access 2003 to a SQL 2005 based ADP
(Service
Pack?)

By design change, do you mean to the ADP file itself or the SQL-Server? For
the ADP file or the users using it, there is no problem; however, to change
the tables/views/SP/functions on the SQL-Server 2005; you will have to use
the SQL-Server Management Studio. I don't see why this could be a serious
problem.

2 - Should I now convert to Access2007 if the answer to 1 is NO

There are some unrelated problems to ADP in Access 2007 such as the loss of
the setting of individual printers for the reports or the ribbon bat. If
you want to use ADP, there is no advantage of using 2007 - excerpt maybe for
the free availability of the runtime - but there are some disadvantages
related to Access 2007 itself, not to ADP. Unless you want to use the
runtime or the sync service available with Windows Azure, you will see some
advantages to keep Access 2003 instead of switching to 2007 but there are
not stopping show. If you want to use the syncing service, it's possibly
better to use A2007 but I did not really make any test on this.

3 - If clients want ADP's, as mine seem to do, for the low end database
applications should I be recommending that SQL 2000 is retained for such
applications

I don't see why, unless you want your user to bring changes to the database
themselves using Access. Knowing that's easier to optimize a SP using
SQL-2005 or - even better - SQL-2008; I would not recommend to keep SQL-2000
if you can switch to 2005 or 2008.

Happy new year 2009!

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Dear Sylvain,

I seem to have come into the middle of a debate about ADP/Access/COM/DCOM
and I wish I could give a considered opinion however:-

I am a hard working database designer, working for myself and I sense my
livelihood is threatened. Would you be so kind as to answer the following
Q's

1 - Can I do design changes in Access 2003 to a SQL 2005 based ADP
(Service
Pack?)
2 - Should I now convert to Access2007 if the answer to 1 is NO
3 - If clients want ADP's, as mine seem to do, for the low end database
applications should I be recommending that SQL 2000 is retained for such
applications

Thank you
--
Bob Snedden, MCT, MCSD.Net

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in
It's hard to believe that MS itself is taking Access seriously
when there is not even any certification for it.

I think your point of view is belied by the actions of the Access
development team in the recent time frame. Access has a new life
both as end-user tool and as developer platform, precisely because
of the decisions made in preparation for Access 2007.

Seems to me that MS has almost returned to the point of view about
Access that it had c. Office 95/97, where they were pushing Office
as a development platform for small and medium-sized businesses. MS
went way, way off track with Office 2000 and Access 2000, in my
opinion, and it's taken them this long to get back to the right
path.

In my opinion, ADPs were a bad idea based on stupid prejudices
rather than technical merit/need, and not really a
properly-implemented development platform. The development of ADPs
sapped resources that should have gone into the core Access
application. The fact that ADPs really are dead now (thankfully)
just points out the waste of resources. How much better Access could
have been for all users if they'd not been led down the garden path
by the crazy Enterprise development ideas that mostly came from
irrational fear of Jet.

Nice talk but totally beside the point. ADO (and ADP), VBA, VBScript,
VB6,
etc., are all technologies based on COM/DCOM and MS is in the process of
killing all technologies based on these because you cannot make real
object
oriented programming with COM/DCOM; only some sort of simulation; unlike
..NET which has been designed as an object oriented programming platform
from
the ground up.

First, they have killed DNA (Distributed Network Architecture) before it
got
even the chance of getting out of the laboratoray; then their next target
has been VB6 as well as a lot of smaller associated technologies like
ADO,
VBScript, ASP Classic; etc. Did you really think that after having
killed
these very big pieces of development platforms that were VB6, ASP Classic
and VBScript, that MS would be giving some serious toughts about keeeping
VBA and DAO in the long term?

I don't really understand how you can say that Access 2007 has got a new
life as a development platform when practically all the technological
development that have been made these last years are based on .NET and
the
only thing that Access got was these small bones about Sharepoint; which,
by
the way, are totally useless for anyone using SQL-Server as the backend.

They don't give a s**t about the technical merit/need of ADO vs DAO; all
they want is to get rid of COM/DCOM in the fatest way; so they are
killing
these littles bunnies one after the other and it shouldn't take to long
before it's the turn of DAO and VBA. Now that VB6 is dead, it's totally
absurd for anyone entering the field to learn VBA and DAO (as well as
ADO);
so it doesn't take a big brain to see that there is absolutely no future
there; whatever you might say. Like ADP; JET, DAO and VBA are dead, the
only difference if that some peoples using these don't know it yet.

The problem is not to know if there is a future or not for DAO and ADO
(there's none for both of them); the problem is to know what's the best
thing to do in the meantime before we can get our hands on a version of
Access with and integrated version to .NET.

 

[a a r o n . k e m p f]

Sylvain;

I've had some problems getting SQL 2008 to work, maybe it's related to
high instance count--

have you gotten ADP working with SQL 2008?

-Aaron