I'm writing some documentation on how to connect Access to SQL via ODBC. I honestly always just choose "11.0" when it comes to drivers, just because. But I'm wondering what determines the driver I "should" be using?
Does the version of SQL Server determine this? And I'm assuming the "User's" computer won't have all the driver's I'm seeing here so can someone just clarify the rules to choosing the correct driver?
According to a blog post located here,
SQL Native Client was introduced in Microsoft SQL Server 2005 to provide new functionality above and beyond that supplied by the Microsoft Data Access Components (MDAC).
It goes on to list some of the new features:
SQL Native Client can be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2005 features such as Multiple Active Result Sets (MARS), user-defined data types (UDT), query notifications, snapshot isolation, and XML data type support.
I think it unlikely that a SQL Server linked table in Access would leverage any of those newer SQL Server features for two reasons:
ODBC links to SQL Server from Access are probably kept fairly generic to support links to older versions of SQL Server, and
As I understand it, all operations on an Access linked table are handled first by the ACE/Jet database engine, which in turn sends requests to the ODBC driver. I suspect that if a query involved any "fancy stuff" then ACE/Jet would take care of it and send relatively routine requests to the SQL Server via ODBC.
Therefore, since the venerable "SQL Server" (MDAC) ODBC driver is shipped with Windows and will be available on any machine then that would be the most straightforward solution. I doubt that using one of the newer "Native Client" drivers would hurt, but I also doubt that it would help much (if at all) in this case.
I'm writing some documentation on how to connect Access to SQL via ODBC. I honestly always just choose "11.0" when it comes to drivers, just because. But I'm wondering what determines the driver I "should" be using?
Does the version of SQL Server determine this? And I'm assuming the "User's" computer won't have all the driver's I'm seeing here so can someone just clarify the rules to choosing the correct driver?
According to a blog post located here,
SQL Native Client was introduced in Microsoft SQL Server 2005 to provide new functionality above and beyond that supplied by the Microsoft Data Access Components (MDAC).
It goes on to list some of the new features:
SQL Native Client can be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2005 features such as Multiple Active Result Sets (MARS), user-defined data types (UDT), query notifications, snapshot isolation, and XML data type support.
I think it unlikely that a SQL Server linked table in Access would leverage any of those newer SQL Server features for two reasons:
ODBC links to SQL Server from Access are probably kept fairly generic to support links to older versions of SQL Server, and
As I understand it, all operations on an Access linked table are handled first by the ACE/Jet database engine, which in turn sends requests to the ODBC driver. I suspect that if a query involved any "fancy stuff" then ACE/Jet would take care of it and send relatively routine requests to the SQL Server via ODBC.
Therefore, since the venerable "SQL Server" (MDAC) ODBC driver is shipped with Windows and will be available on any machine then that would be the most straightforward solution. I doubt that using one of the newer "Native Client" drivers would hurt, but I also doubt that it would help much (if at all) in this case.
0 commentaires:
Enregistrer un commentaire