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

kashif.special2005

Active Member
Joined
Oct 26, 2009
Messages
435
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.


Code:
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Watch MrExcel Video

Forum statistics

Threads
1,089,850
Messages
5,410,747
Members
403,328
Latest member
dalan

This Week's Hot Topics

Top