If you mean ADP and you are using SQL Server 2005 you may need to run SQLS
at compatibility level 80 to access the data, and the daVinci toolset (the
design tools of the Access Data Project) only works with SQL Server 2000.
You will NOT be able to design objects in a SQL Server 2005 database at any
level of compatibility, with the daVinci toolset, I believe.
At the beginning of the year we dropped all future development (for the
forseeable future) with SQL Server (in favour of IBM DB2) so others may have
more recent information.
Best advice IMO is to upgrade to SQL Server 2000, it is a better (and more
robust) product than SQL Server 2005 (as far as the relational database
engine is concerned).
Even better advice if the ADP is at an early stage, then abandon it and
revert to MDB as the front-end.
This is a quote from Mary Chipman of Microsoft on these newsgroups on 12
October.
"The Access team at Microsoft recommends that for new Access-SQL
projects you use an mdb for the front-end. This gives you greater
flexibility in designing your application because you can also take
advantage of local storage for database objects and pass-through
queries for stored procedures."
and
"specifically SQL Server 2005, which offers
limited ADP support. Microsoft will of course continue to support ADP
applications written against SQL Server 2000, and will support
connecting design-complete ADP's against SQLS 2005. I don't believe a
final decision has been reached by the Access team on the future
ADP's, but in the meantime they recommend MDB's as the front end for
all *new* Access-SQL projects."
ADPs were a good idea, badly implemented, and now may be set for the
scrapheap, if you have not yet invested a lot of work on your current
project I would consider using the recommended approach.
