I use the below connection and ADO recordset to get SQL Server 2012 data by Access 2013 VBA code. The recorset is an query(strQuery) which join couple SQL tables together. I am not familiar with the conenction with SQL Server. The conenction string is copied from Excel Data connection properties.
Dim ADOConn As New ADODB.Connection
Dim rs As New ADODB.Recordset
str = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;" & _
"Initial Catalog=PlanSystem;Data Source=RD\SQLServer;Use Procedure for Prepare=1;" & _
"Auto Translate=True;Packet Size=4096;Workstation ID=RD;" & _
"Use Encryption for Data=False;Tag with column collation when possible=False"
ADOConn.Open str
ADOConn.CursorLocation = adUseClient
strQuery = "SELECT * FROM Table1 JOIN Table2 ON ... "
rs.Open strQuery, ADOConn, adOpenStatic, adLockReadOnly, adCmdText
How can I rewrite the recordset connection to a query which I can set qdf.SQL to SQL Server tables? Below is the code I can set query to local table. I need a similiar code which I can set query to SQL Server. The query SQL will be passed to SQL Server and return the join linked data back to query only.
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryPlan")
qdf.SQL = strQuery