SQL Native client with ms access

[abenitez77]

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

 

[abenitez77]

This line is where it kicks out:
.sql = sql & sql2 & sql3 & sql4

 

[abenitez77]

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