December 27, 2011 at 12:28 pm
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
December 27, 2011 at 3:08 pm
This worked:
strConnection = "ODBC;Driver={SQL Native Client};Server=" & strSQLServer & ";Database=" & strSQLDatabase & ";Trusted_Connection=yes;" ' SQL Native Client 9.0 ODBC Driver
December 28, 2011 at 1:40 am
When you work with the DAO library you need to specify the provider, whatever the driver can be (this is not the case when working with ADODB):
"Provider=ODBC;Driver={SQL Native Client};Server=ATL20AS1100SQ02;Database=AS_KHL_2010_2011;Trusted_Connection=yes;"
Have a nice day!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply