Microsoft Access 掲示板

ODBCのドライバですが、[ODBC Driver 17 for SQL Server]をインストールし、
ADODBの接続に"Provider=MSOLEDBSQL";としてもダメでした。
（ちなみにSQLOLEDBも試しましたが同様でした。）

おや。
MSOLEDBSQL プロバイダは ODBC ではなく、OLEDB ドライバーではなかったでしょうか。
Microsoft OLE DB Driver for SQL Server

あと、念の為に申し上げておきますと
ODBCドライバが関係するのは、「 リンクテーブル 」です。
ADO で SQLServer に接続しているコードにおいては
OLEDB接続のため、ODBCドライバは関係ありません。
　　

DAO接続では正しく動作したのでそうすればよいのですが、
修正箇所が多く…またデータ量が多いうえ
多人数で使用するシステムなので出来ればADO接続の方が良いのかなと考えています。

私はクロさんと見解が少し異なります。

ADO は【 SQLServerに接続し、操作するミドルウェア 】という点においては
確かに DAO より優位であると考えています。
ですが、Access フォームに SQLServer のデータをバインドする
という状況下では、利用しないでしょう。

ADO を利用しない理由としては、Access のフォーム
つまり Me.Recordset が DAO そのものであるから、です。
( Me.Recordset が ADO でいられるのは、現在は利用できない ADP だけですね )

なので、Set Me.Recordset = rs というコードは
ADO.Recordset が DAO.Recordset に暗黙変換されて
Access のフォームにバインドされます。
動作原理はリンクテーブルと似たりよったりでしょう。

したがって、パフォーマンスをお求めの場合はリンクテーブルではなく
パススルークエリをフォームのレコードソースに指定することをお薦めします。
　　

' イケてないADO 接続(テキストボックスに#Name?が表示される)

以下のようにコードを変更して原因を探ってみてはいかがでしょう。

Private Sub cmdADODB_Click()
    '■ 参照設定を Microsoft ActiveX Data Objects 6.X Library に変更
    
    Dim rs As ADODB.Recordset '■ Nothingの状態に移行出来ないため、宣言時にNewはつけない。
    Dim strSQL As String
    
    strSQL = "SELECT * FROM SQLServer上のテーブル WHERE DATA1 LIKE '%みかん%'"
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient '■ クライアントカーソルに変更
    rs.Open strSQL, cn, adOpenStatic, adLockOptimistic
    
    Set rs.ActiveConnection = Nothing '■ RecordsetをSQLServerと切り離して独立させる
    Set Me.Recordset = rs
    
    If rs.EOF = False And rs.BOF = False Then
        Me.txtData1.ControlSource = rs![Data1].Name
        Me.txtData2.ControlSource = rs![Data2].Name
    End If
    
    Set rs = Nothing
End Sub