Adding records through a view: ADP and SQL Server 2005

[mjbrydon]

Hi,

I am encountering a problem in SQL Server 2005 that I have never before
encountered with previous versions of SQL Server. I have an Access
(ADP) client accessing data on a SQL Server backend. A form is bound to
a view and records can be added in the usual way.

With SQL Server 2005, I get an "Invalid Input Parameters Values. Check
the status values for detail." error. This has nothing to do with the
form. I can add the record through the view just fine using MSDE or SQL
Server 2000. But SQL Server 2005 (even in SQL Server 2000 compatiblity
mode) can't do it.

Has anyone else encountered this problem? I would hate to have to
re-write this application---adding records through views in an ADP
front-end is such a quick and easy way to build apps.

/M

 

[mjbrydon]

I have done some more testing and should point out that the problem
only occurs when the primary key of the table on the "one" side of the
view uses and increment to automatically generate a new primary key
value. It seems that the increment is not generated when the view is
accessed through ADP. In contrast, a record can be added to the view
(with or without an increment primary key) directly though the
interface of the SQL Server 2005 manager.

In short, this is a SQL Server 2005 + ADP + Increment problem...

 

[Norman Yuan]

Based on the information so far available, common sense would be: Do not use
ADP with SQL Server2005, until MS either clearly says ADP is dead from SQL
Server2005 on, or release service pack for Access2000-2003 ADP for
supporting SQL Server, or make ADP workable with SQL Server2005 in upcoming
new version (Access12). The odd on the former (ADP dies on SQL Server2005 or
later) seems higher to me.

 

[Patrick Jackman]

Norman, you mention ADPs could be dead. Where can I read more about this?

I am at the stage of deciding whether to start working with Access and SQL
Server through ADPs or ODBC. I would hate to start working with ADPs if the
format is soon to be dropped.

Patrick.

 

[Brendan Reynolds]

Mainstream support for Access 2003 is guaranteed for almost another three
years ...

http://support.microsoft.com/lifecycle/?p1=2509

.... so even if support for ADPs was dropped in the next version - which I
think is extremely unlikely ... you can go right on working with ADPs in
Access 2003 at least until the end of 2008.

I'm not saying you should choose ADPs, just that you should decide on the
basis of current technical merit rather than on speculation about what may
or may not happen in the future.

 

[Norman Yuan]

I've heard that MS Access team itself recommend use *.mdb/*.mde as SQL
Server2005 FE, instead of *.ADP. The fact is SQL Server 2005 was realeased
almost three years later that Access2003. ADP, as a FE specifically designed
for SQL Server, did not know all those new features/objects in SQL Server,
so, you can not use it as you do with SQL Server2000 (you still can connect
to it, as you already know, but you cannot create SQL Server2005 object,
like table, view, sp. Facing such hurdles, ADP is literally useless). When
SQL Server2000 was released (also after Access 2000, the first version
sopprting ADP), MS quickly released Access SQL Server2000 compatibility
service pack. But this time, the Access team even not recommeds to use ADP
for SQL Server. At least it indicates ADP's future is not clear at this
point. SO, for SQL Server2000/7, yes, APD is OK (even for upcoming Access12,
I'd bet it would be OK, MS is fairly good on supporting what is already
there), but, for SQL Server2005 or later, no, I'd not invest into it until
its future is cleared up. With Access12 coming out very soon, still not a
single word is heard on whether ADP will support SQL Server2005, that is not
a good hint to me.

Actually, my knowing on this info, is mostly from this NG and somewhere on
MS site (cannot remember the links). If you search this NG, you may find a
bit more info on this.

 

[Malcolm Cook]

FWIW, searching http://blogs.msdn.com/access/default.aspx for adp reveals eactly one entry:

ADP's and Access 12 Security:

The ADP architecture is conceptually unchanged between Access 2003 and Access12, which means that the features continue to work in
essentially the same way they did. We continue to believe that SQL Server makes a great store for Access data and that building the
UI either through linked tables or ADPs will continue to work well.

 

[Patrick Jackman]

Thanks Malcolm,

Mary Chipman and Andy Baron offer good advice in "Microsoft Access
Developer's Guide to SQL Server" from Apress.

I've decided to stick with mdb's for the flexibility they offer,
particularly local temp tables which I use extensively in apps that will be
migrated.

Patrick.

FWIW, searching http://blogs.msdn.com/access/default.aspx for adp reveals
eactly one entry:

ADP's and Access 12 Security:

The ADP architecture is conceptually unchanged between Access 2003 and
Access12, which means that the features continue to work in
essentially the same way they did. We continue to believe that SQL Server
makes a great store for Access data and that building the
UI either through linked tables or ADPs will continue to work well.