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.Connection
Dim rst As New ADODB.Recordset
Dim sXMLResult As String
'OLE DB (ADO) Native CLient
con.ConnectionString = "Provider=SQLNCLI11;" _
& "DataSource=MSSQLSERVER;" _
& "Server=(local);" _
& "Database=JP1;" _
& "Uid=sa;" _
& "Pwd=sapass;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;"
Set rst.ActiveConnection = con
rst.Open "SELECT * FROM dbo.tbl1;", con, adOpenKeyset, adLockPessimistic
Set Me.Recordset = rst
Set 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 like
Do you know how to define Passthrough queries when using ADO instead of ODBC?