In our experience in using linked tables with Access, we get the best response using Native Client 11.0 with dsnless tables. You could use ODBC 11 for SQL Server but then you would NOT be able to use ADODB, so we prefer the latter and not the former.
Microsoft does a great job of hiding Native Client 11.0 on their site, so I’ve done the hard work, you can download the files right from this blog. If you are running 64-bit Windows (even though you might be running 32-bit Access), download the 64-bit version. If you are still using 32-bit Windows, download the 32-bit version.
Please join me on September 8 for the opening season of the SQL Server with Access group!
Next month’s presentation will be on “When to use a Stored Procedure, Pass Through Query, View or ADODB in Access?”, it will be at 6:30 PM CST and for details please click here. While you’re at it, why not register for the group at AccessUserGroups.org? looking forward to seeing you that evening!