ODBC --Connection Server 'ServerName' failed

[kashif.special2005]

Hi,

I have created a User DSN and successfully created a linked table in Microsoft Access with that DSN, however through excel vba when I tried to fetch data from that linked table an error message is displaying "ODBC --connection 'SERVERNAME' failed.

However I am successfully able to fetch data from normal table from MS-Access table

Note: I using Office Pro 64-bit

USER DSN For Linked Table:-

ODBC;DSN=DSN Name;Description=DSN Description;Trusted_Connection=Yes;APP=Microsoft Office 2016;DATABASE=Database Name;Table=TableName

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=databaspath;"
    
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    cn.Open conn
    
    rs.Open "Select * from MainPeople", cn [B]'Error Line, Table 'MainPeople' is a linked table.[/B]
    
    ActiveSheet.Range("A1").CopyFromRecordset rs
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Sub

Thanks
Kashif