Sylvain, in addition to what Mark Olbert said, you should know that in .ADP
mode, Access can indeed do things that SSMS does not.
You know how SSMS lets you manipulate Extended Properties on Tables and
other database objects? Ever wonder what those were for?
They can be used for a lot of things, but they’re specifically for Access in
ADP mode!
Try this:
Use any XP/2002 or later version of Access against any version of SQL Server
that came out BEFORE that version of Access (e.g. Access XP/2002 or 2003
against SQL Server 2000 or earlier, Access 2007 against SQL Server 2005 or
earlier, etc.).
In Access, do "File / New" and ask to create an Access Data Project with
EXISTING Data. Enter the connection info (server name or IP address, login,
and password) needed to connect, and select the default database if that
login has access to multiple databases. Make sure that TCP Port 1433 (IRC) is
not being blocked by any firewall, hardware or software, between you and the
SQL Server, and if connecting to an SQL Server Express, make sure that it’s
either on localhost or that you’ve enabled remote TCP/IP connectivity.
You should soon see an Access Data Project appear, with all Tables, Views,
Stored Procedures, and Database Diagrams (!) visible and accessible, in
Design View as well as in Datasheet View.
Now, you COULD create Forms and Reports and Macros and Modules and such.
Those go into the .ADP file (just as they would in the .MDB file when working
with an .MDB), but any Tables, Views, Stored Procedures, or Database Diagrams
you create or manipulate from within Access are stored on the SQL Server
itself. So you would only need to distribute an ADP file to someone else who
needs remote access to the database if they need Forms and/or Reports.
A typical need for a Form would be to simplify data entry, such as replacing
Boolean fields with checkboxes instead of having to type in “true†and
“False†into the Datasheets, or replacing Foreign Key fields with combo boxes
showing some identifying text from the Parent table instead of the numeric ID
numbers, etc.
If you just linked to the SQL Server from an .MDB file, you would indeed
have to create Forms for all that stuff, or perhaps in some later versions of
Access you could go into Design view and change items in the Lookup tab on
fields to implement such things in Datasheet mode, but they would stick with
that .MDB file and not be usable by others using the same SQL Server in
Access.
With the .ADP file, you again go into Design mode and use the Lookup tab to
set up your checkboxes, combo boxes, etc. You can also, once back in
Datasheet mode, resize the columns for more usability, and even re-order
them, etc.
In either case, when you save the changes to Lookup in Design mode or column
widths and orders and the like in Datasheet Mode (it asks you if you want to
save the current “layoutâ€), those things get saved ON THE SQL SERVER! They’re
stored in the form of Extended Parameters!
Now you can simply walk another user through connecting to the SQL Server
from Access by again Creating a new Access Data Project with Existing Data.
And, when they call up the Tables in Datasheet mode, lo! The checkboxes and
combo boxes and column widths and order of columns is the same for them as it
was for you!
Try THAT in Server Management Studio!
But yes, it has always been the case that ADP in Access only works with SQL
Server versions that PREDATE the Access version. The sole esception was
Access 2000 with a downloadable update to enable PARTIAL ADP compatibility
with SQL Server 2000 (no BIGINT fields, for instance).
So, we may not see SQL Server 2008 ADP compatibility in Access until the
next major release of Access. This is only to be expected. I don't forsee a
patch similar to the Access 2000/SQL Server 2000 one coming out, because SQL
Server 2008 implements some drastic new data types that Access 2007 would
need to be “educated†about (DateTime2, geographic types, etc.).
