Results 1 to 5 of 5
  1. #1
    chucky67 is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2021
    Posts
    2

    New SQL Driver options? Pros & Cons of various options.

    Hello!

    I recently began development of a new Access application that leverages MS SQL as a backend. I have become accustomed to selecting the 'SQL Server Native Client 11.0' as the driver when linking tables in Access. I know that at one time, this was highly recommended by some, even though it requires a separate software installation on the endpoints. Today when creating the linked tables, I noticed some options that I don't recall seeing before when creating a new DSN for connection. The new previously unnoticed options include 'CR SQL Server Classic Wire Protocol ODBC Driver 7.1.5' and 'CR SQL Server Native Wire Protocol ODBC Driver 7.1.5'. So my questions are several:

    1) Are these new options or have I just not noticed them until now? If so, when did they come out?


    2) What are the advantages and disadvantages of each of these 'new' drivers when compared to each other?
    3) What are the advantages and disadvantages of these 'new' drivers compared to the SQL Server Native Client 11.0 Driver?
    4) What do you feel the best driver to use is in 2021 and why?

    I make extensive use of the VBA, DAO, Access, Office, Excel and Outlook libraries in my code, if that makes any difference as to what the 'correct' answer is.

    Thank you for taking the time to read my post and I'm hoping someone here can shed some light on this subject for me.

    Thanks!!!

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    We use the latest ODBC driver from Microsoft - Microsoft ODBC Driver 17 for SQL Server : https://docs.microsoft.com/en-gb/sql...l-server-ver15


    This seems to be stable, reliable and supports all the appropriate features available in the later versions of SQL server.

    This page https://docs.microsoft.com/en-gb/sql...vious-releases
    lists the multitude of previous versions and the release notes if you scroll down.

    On the whole we have found that the latest version seems the best option.

    EDIT : Your CR... drivers are I suspect Crystal Reports drivers that have been loaded on that machine.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I also use the ODBC 17 driver. There's a discussion on the options here:

    https://accessexperts.com/blog/2018/...-oledb-driver/
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    chucky67 is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2021
    Posts
    2
    Thank you all for your insights and for taking the time to respond! I appreciate it!

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    As long as you do not use DO in your code ODBC 17 should work; if you need to use ADO then you might need to use Microsoft OLE DB Driver 18.6 for SQL Server.
    More info here:
    https://accessexperts.com/blog/2018/...-oledb-driver/

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Pros and cons of table field value list vs lookup table
    By pmhb2011 in forum Database Design
    Replies: 9
    Last Post: 10-02-2020, 01:11 AM
  2. Options Options Dialog
    By Mister-B in forum Modules
    Replies: 2
    Last Post: 05-10-2020, 01:07 AM
  3. Replies: 1
    Last Post: 02-07-2014, 11:21 AM
  4. Pros & Cons or Record locks
    By h3nno in forum Forms
    Replies: 0
    Last Post: 03-22-2012, 10:48 PM
  5. Pros and Cons about splitting a database
    By TCB in forum Database Design
    Replies: 1
    Last Post: 04-07-2011, 11:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums