CODEWITHSUNDEEP
sql-serverms-access
YellowLarry
YellowLarry

Reputation: 431

From Access 2013 to query SQL Server 2012

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

Upvotes: 0

Views: 212

Answers (1)

FakeisMe
FakeisMe

Reputation: 464

To Set Connection String :

 sConnString = "Provider=SQLOLEDB.1;Data Source=RD\SQLServer;" & _
                  "Initial Catalog=PlanSystem;" & _
                  "Integrated Security=SSPI;"

 sConnString2 =   "ODBC;DRIVER=SQL Server;SERVER=RD\SQLServer;DATABASE=PlanSystem;Trusted_Connection=Yes;"

Set qdf = db.QueryDefs("qryPlan")
qdf.Connect = sConnString2 
qdf.SQL = strQuery

Also ,
Set qdfNew = db.CreateQueryDef(strQueryName, strQuery)
qdfNew.Connect = sConnString2 
qdfNew.Close

Upvotes: 1

Related Questions