using SQL Native Client 9.0 ODBC Driver with ms access

Question

I have an application that is currently using the older sql 2000 odbc driver to connect ms access to sql server.  I wanted to try the newer SQL Native Client 9.0 ODBC Driver.  I don't get an error msg but when I try to run a passthru query it kicks out without an error and does not run my passthru query on sql server.  It kicks out of the function in the bolded line. 

the line it kicks out of in the function below has this value:

sql = "SELECT MASTERREC,EMAILGUID,SentDate,ReceivedDate,Subject,ConversationID,ConversationTopic,LOCKED_BY_USER,RECID,AttachmentCount,"

sql2 = ""

sql3 = "Cast(' ' as Varchar(MAX)) as 'Comments',Cast(' ' as Varchar(255)) as 'Address' "

sql4 = "Into [dbo].[tbl_KOHLS_EV_2010_abenit01] FROM tblv_EmailViewer WITH (NOLOCK) WHERE MasterRec ='177'  Order by SentDate desc"

I tried simplifying it by just saying "Select Masterrec from tblv_EmailViewer"  and i get the same result.  When i use the old odbc driver it works both ways.

This is my connections string:

"Driver={SQL Native Client};Server=ATL20AS1100SQ02;Database=AS_KHL_2010_2011;Trusted_Connection=yes;"

function:

Function SQL_PassThrough2(ByVal ConnectionString As String, _

                              ByVal sql As String, _

                              ByVal sql2 As String, _

                              ByVal sql3 As String, _

                              ByVal sql4 As String, _

                              Optional ByVal QueryName As String)

    Dim dbs As DAO.Database

    Dim qdf As DAO.QueryDef

    Set dbs = CurrentDb

    dbs.QueryTimeout = 300

    Set qdf = dbs.CreateQueryDef

    With qdf

        .Name = QueryName

        .Connect = ConnectionString

        .sql = sql & sql2 & sql3 & sql4        .ReturnsRecords = (Len(QueryName) > 0)

        .ODBCTimeout = 300

        If .ReturnsRecords = False Then

            .Execute

        Else

            If Not IsNull(dbs.QueryDefs(QueryName).Name) Then dbs.QueryDefs.Delete QueryName

            dbs.QueryDefs.Append qdf

        End If

        .Close

    End With

    qdf.Close

    Set qdf = Nothing

    dbs.Close

    Set dbs = Nothing

End Function

Answer 1

This worked:

strConnection = "ODBC;Driver={SQL Native Client};Server=" & strSQLServer & ";Database=" & strSQLDatabase & ";Trusted_Connection=yes;" ' SQL Native Client 9.0 ODBC Driver