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

Question

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 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 giving an error message "Could not find installable ISAM", below is the code that I am using.

Note:- table "PeopleMain" 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 " & _
    "[Provider=sqloledb;Server=servername;Database=database;Trusted_Connection=Yes].PeopleMain) Requestor  " & _
    "ON Requestor.People_ID = RM.PrimaryRequestor) LEFT JOIN (SELECT Preferred_Name + ' ' + Last_Name AS Name, " & _
    "People_ID FROM [Provider=sqloledb;Server=servername;Database=database;Trusted_Connection=Yes].PeopleMain) " & _
    "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

Erik A · Accepted Answer · 2019-04-12 09:03:10Z

0

Access allows querying and joining in ODBC data sources, not OLEDB data sources.

Change the connection string used inside your query to an ODBC string:

[ODBC;Driver={SQL Server};Server=servername;Database=database;Trusted_Connection=Yes].PeopleMain

When available, I recommend using a more up-to-date ODBC driver, of course.

answered Apr 12, 2019 at 9:03

Erik A

32.8k13 gold badges49 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user11339713 Over a year ago

Hi Thanks for reply, I changed the code as per your suggestion but it is giving different error message. "The Microsoft Access database engine cannot find the input table or query 'PeopleMain'. make sure it exists and that its e is spelled correctly."

Erik A Over a year ago

Well, is it? It must reflect the name on SQL server, including schema if it's not in the default schema.

user11339713 Over a year ago

Yes, you are right I included the schema name, but this time giving me different error message "now it is giving me an error message ODBC --connection to '{SQL Server}servername' failed"

Erik A Over a year ago

Well, I can't troubleshoot connection issues remotely. It's up to you to figure that out.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related