I have a project that requires the use of SQL Server (in place already) with A03 as the frontend. I've downloaded SQLServer Express 2012 for testing purposes and managed to successfully interface A03 with SQLServer using SQL Server Native Client 11.0. I use the following (ADO instead ODBC) to SELECT the contents of a table which I created in SQLServer Express 12. This code that NOT require a Linked table (dbo.tbl1) in the Access FE. Dim con As New ADODB.ConnectionDim rst As New ADODB.RecordsetDim sXMLResult As String'OLE DB (ADO) Native CLientcon.ConnectionString = "Provider=SQLNCLI11;" _ & "DataSource=MSSQLSERVER;" _ & "Server=(local);" _ & "Database=JP1;" _ & "Uid=sa;" _ & "Pwd=sapass;" _ & "DataTypeCompatibility=80;" _ & "MARS Connection=True;"con.OpenSet rst.ActiveConnection = conrst.Open "SELECT * FROM dbo.tbl1;", con, adOpenKeyset, adLockPessimisticSet Me.Recordset = rstcon.CloseSet con = Nothing
To define a Passthrough query when using ODBC, I can define it in the query section and set the "ODBC Connect Str" property of the query to something likeODBC;DSN=XXX;SERVER=YYY;
Do you know how to define Passthrough queries when using ADO instead of ODBC?John