Why does SQL server 2005 not work with Access 2003 Projects (adps)?

[J.S.]

I know there are other ways to connect to SQL server but I am wondering
why SQL server 2005 databases cannot be accessed through Access 2003
projects. Is it to force people who prefer the Access interface to
upgrade to Access 2007?

 

[PBsoft]

I know there are other ways to connect to SQL server but I am

wondering why SQL server 2005 databases cannot be accessed through
Access 2003 projects. Is it to force people who prefer the Access
interface to upgrade to Access 2007?

You can't use Access 2003 UI to manipulate SQL 2005 objects, but you can
use Access 2003 to create and manage an ADP connected to SQL 2005 with no
problem.

 

[J.S.]

You can't use Access 2003 UI to manipulate SQL 2005 objects, but you can
use Access 2003 to create and manage an ADP connected to SQL 2005 with
no problem.

Thanks! I'll look into this again and try to do it.

Best,
J.S.

 

[PBsoft]

Thanks! I'll look into this again and try to do it.

If you get some problem, you can open a new thread int his group.
I developed a very small application for a customer of mine, based on an
Access 2003 ADP + SQL 2003 Express, and will be happy to help you if I could.

 

[Norman Yuan]

What do you mean "does not work" here? If it means "SQL server 2005
databases cannot be accessed through Access 2003 projects", that is not
true, or at least, depending on the meaning of "be accessed", not complete
true.

Yes, you can access SQL Server2005 database with Access2003 ADP, your ADP
app will run fine. You just cannot use ADP to create/modify SQL Server 2005
server side objects (table, view, sp, UDF...) as you used to be able to with
Access2000/2002/2003 and SQL Server2000. This is understandable: SQL Server
was released 3 years after Access2003's release and when Access2007 was on
its way. Of course we could complain that why MS does not release an
Access2003 patch, as they did with Access2000 when SQL Server2000 was
released after Access2000, so that Access2003 can work wirh SQL Server2005
the say way as with SQL Server2000.

Yes, if you insist to use Access ADP as tool to design/modify/manage SQL
Server2005, you need to move up to Access2007. If you only want to run your
ADP app as front-end, Access2003 would be fine. I wouldn't bet my new
development on ADP, which may not be there any more in next Access release
(purely guessing).

 

[Jello World]

Norman;

we don't 'insist on using adp'

adp just flat out works better.

can you copy and paste views in Enterprise Manager?
can you copy and paste views in SQL Server Management Studio?


-Charlie

 

[J.S.]

If you get some problem, you can open a new thread int his group.

I developed a very small application for a customer of mine, based on an
Access 2003 ADP + SQL 2003 Express, and will be happy to help you if I
could.

Thanks! I appreciate that. Actually I was able to access the SQL
server database with an Access 2003 ADP. It's just that I received
messages which indicating that I wouldn't be able to save any design
changes.

Could you share what the application that you developed with Access 2003
ADP and SQL 2005 Express do? Does it enable Access 2003 to make design
changes to the SQL server 2005 Express database or does it add some
other functionality to Access 2003 ADPs?

 

[Norman Yuan]

I aggree with you. Actually, I alway use Access ADP as my tool for my SQL
Server development. The copy/paste server objects with ADP is very handy.
However, if MS decided not making Access2003 support SQL Server2005 server
object manipulattion, what you can do? With ADP possibly going away soon, I
do not see MS would make extra efford to give Access2003 and older a push on
this regard.

 

[J.S.]

Does not work means that one cannot perform all functions that one could
perform with a SQL server 2000 database. Sure, I can "access" the 2005
database but I can hardly do anything with it.

Yes, if you insist to use Access ADP as tool to design/modify/manage SQL
Server2005, you need to move up to Access2007. If you only want to run your
ADP app as front-end, Access2003 would be fine. I wouldn't bet my new
development on ADP, which may not be there any more in next Access release
(purely guessing).

I don't know how an Access 2003 ADP can be used as a front-end since one
can hardly do anything with it. To me a front-end means that one can
also make changes to the back-end.

I have been using Enterprise Manager since time immemorial. It's
just that when I want to use something easier then I use ADPs. And as
Charlie mentioned elsewhere in the thread, the ADPs are much easier to
use. Why can't such an interface be a selectable option in SQL server
itself? If only software companies listened to users better than they do...

Apparently, I'll have to upgrade to Access 2007, which is ok, but right
now I am using 2003.

Perhaps, I'll just go back to using Management Studio for now.

 

[dbahooker]

JS -

I agree; it's a total load of crap that ADP 2003 won't update SQL 2005.

I don't care if it was 10 years in between.
it's about investing in a PLATFORM.


plus, MIcrosoft still owes us my screwing us on the whole Access 2000 /
SQL 2000 interop thing

-Aaron

 

[PBsoft]

Actually I was able to access the SQL

server database with an Access 2003 ADP. It's just that I received
messages which indicating that I wouldn't be able to save any design
changes.

It's correct: using Access 2003 you CANNOT modify any SQL Server object (tables,
stored procs, inline functions, views....)

Could you share what the application that you developed with Access
2003 ADP and SQL 2005 Express do? Does it enable Access 2003 to make
design changes to the SQL server 2005 Express database or does it add
some other functionality to Access 2003 ADPs?

The ADP I developed is used for managing appointments for a car distributor/workshop.
Obviously, as I said before, neither this ADP nor any other (built with Access
2003) can manipulate SQL Server object.
Anyway, I don't recommed to let customers to access SQL Server objects, so
there is no need for it. I know that it could be very useful when you develop
the ADP, but SSMS is more powerful and allows you to do all you have to do
with SQL Server, even in a better way.

 

[PBsoft]

I aggree with you. Actually, I alway use Access ADP as my tool for my

SQL Server development. The copy/paste server objects with ADP is very
handy.

Did you try SQL Server Management Studio? It's very useful and its learning
curve is very low.
Moreover, working with SQL Server you must learn how to manipulate its objects
from its native tools or even T-SQL directly: it's inevitable.
If you learn to use SSMS you will also have no problems when will update
your skiils to Visual Studio .NET

However, if MS decided not making Access2003 support SQL
Server2005 server object manipulattion, what you can do? With ADP
possibly going away soon, I do not see MS would make extra efford to
give Access2003 and older a push on this regard.

You can continue to use ADPs for years, don't worry.

Anyway, Microsoft was clear: ADPs technology will not be developed more,
even if it is fully supported also in Access 2007.
So, I think the message is precise: stop developing with ADPs and switch
back to MDBs + ODBC or upgrade to .NET.

 

[J.S.]

The ADP I developed is used for managing appointments for a car
distributor/workshop.
Obviously, as I said before, neither this ADP nor any other (built with
Access 2003) can manipulate SQL Server object.
Anyway, I don't recommed to let customers to access SQL Server objects,
so there is no need for it. I know that it could be very useful when you
develop the ADP, but SSMS is more powerful and allows you to do all you
have to do with SQL Server, even in a better way.

You are right. I am going to go with SSMS for now.

Thanks,
J.S.

 

[dbahooker]

SSMS is _NOT_ More powerful; it doesn't let you copy and paste views /
sprocs; etc

SSMS doesn't let you set extended properties for tables.. such as
lookup columns

I can't wait to find a copy of Access 2007

-Aaron

 

[dbahooker]

you dont reccomend to let customers access SQL Server Objects?

WHY NOT?

it works like a charm; it's more reliable than MDB.

you've got to be kidding me.. it's not like they'll magically break
something.
dont treat your customers like retards

give them ADP

-Aaron