JOIN an Access table and a SQL Server table in a single query from Excel VBA

[kashif.special2005]

H,

I am using Excel 64-bit


I have a ms-access database and in this database I have normal ms-access table and some linked table from SQL Server, I have a query that is taking a reference of a linked table, and when I am firing that query from excel vba it is giving me an ODBC connection failed error, however I am successfully able to fetch non linked table from excel vba.



Now I am thinking about different approach, can it be possible to join ms-access and SQL Server table in a single query, I found some code from net and tried my luck, but it is not working and again giving ODBC connection failed error below is the code that I am using.


Note:- table "dbo.People" is a sql server table, and except this table all are ms-access table.

Sub FetchData3()
Dim rs As Object
Dim cn As Object
Dim ss As String
Dim conn As String
Dim accdb As Object

conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\Workflow Tools (Michael Cantor)\Tool For Fixing Bug From Michael Cantor\PI MDT Reconciliation Workflow Tool\PI Database.accdb;Persist Security Info=False;Mode=Read"
ss = "SELECT RM.ReconciliationID, RM.FirmID, RM.FirmName, RM.DateRequested, RM.DueDate, Rm.ExtendedDueDate, " & _
    "Requestor.Name, SecondaryRequestor.Name FROM " & _
    "((ReconciliationMaster RM INNER JOIN Reconciliation_Fund RF ON RF.ReconciliationID = RM.ReconciliationID) " & _
    "LEFT JOIN (SELECT Preferred_Name + ' ' + Last_Name AS Name, People_ID FROM " & _
    "[ODBC;Driver={SQL Server};Server=servername;Database=databasename;Trusted_Connection=Yes].dbo.People) Requestor  " & _
    "ON Requestor.People_ID = RM.PrimaryRequestor) LEFT JOIN (SELECT Preferred_Name + ' ' + Last_Name AS Name, " & _
    "People_ID FROM [ODBC;Driver={SQL Server};Server=servername;Database=database;Trusted_Connection=Yes].dbo.People) " & _
    "SecondaryRequestor ON SecondaryRequestor.People_ID = RM.SecondaryRequestor WHERE RM.ReconciliationID = 522;"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open conn
rs.Open ss, cn

Sheet1.Cells.ClearContents
Sheet1.Range("A1").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

MsgBox "done"
End Sub

Thanks
Kashif