Excel VBA ERROR ODBC connection failed

[kashif.special2005]

Hi,

I have a linked table in ms-access which is linked to a sql server table, and when I am trying to fetch the data in excel via VBA from ms-access linked table the error message "ODBC connection failed" showing.

Note:- I am manually successfully able to refresh ms-access linked table in ms-access, "peoplemain" is the name of linked table.

Note:- When I tried to fetch data from non linked table, it is running successfully.

Sub FetchData()


    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim conn As String
    
    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\SampleforPractice.accdb;"


    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    cn.Open conn
    
    rs.Open "Select * from peoplemain", cn  'Error Line
    


    Sheet1.Range("A1").CopyFromRecordset rs


    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing


End Sub

Please help me to resolve this problem.

Thanks
Kashif

 

[kashif.special2005]

Hi ScottR,

Thanks for reply however I tried all steps in the link you shared but it is still not working.

Thanks
Kashif