Excel VBA How To Fetch Data In Excel From A MS-Access Linked Table

[kashif.special2005]

Hi,

I have a MS-Access database and in this database there are around 20 tables, and 5 tables from 20 are linked from SQL Server Table through ODBC connectivity.

Now I want to fetch the result of a SQL Query and paste that result in a excel sheet.

Note:- the query is taking a reference of a LINKED TABLE.

When I tried to execute the sql query from Excel VBA the following error message showing

Error Message:-
Run-time error '2147467259 (80004005)'"
ODBC --connection to 'SQL Serverashsqlprod1' failed

Note:- when I tried to access non LINKED TABLE, it is working fine, and when I paste that query in MS-Access SQL Query window and tried to RUN, it is working fine.

I am not able to understand what is the problem.

Note:- The table "PeopleMain" is a LINKED TABLE.

SQL Query:-

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 [B]PeopleMain[/B]) Requestor
    ON Requestor.People_ID = RM.PrimaryRequestor)
LEFT JOIN (SELECT Preferred_Name + ' ' + Last_Name AS Name, People_ID FROM PeopleMain) SecondaryRequestor
    ON SecondaryRequestor.People_ID = RM.SecondaryRequestor
WHERE RM.ReconciliationID = 628

Sub Fetch_Data_FromAccess()


Dim mysql As String
Dim cn As ADODB.Connection
Dim RS As ADODB.Recordset


Set cn = New ADODB.Connection
Set RS = New ADODB.Recordset


mysql = GetSQLNew("G:\Workflow Tools\mysql.sql")


cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\Workflow Tools (Michael Cantor)\Tool For Fixing Bug From Michael Cantor\PI Database.accdb;"


With RS
    .Open mysql, cn
End With


If RS.RecordCount <> 0 And RS.BOF = False And RS.EOF = False Then
    Sheet9.Range("A1").CopyFromRecordset RS
End If
    
End Sub


Public Function GetSQLNew(strFilepath) As String
'Uses reference Microsoft Scripting Runtime


Dim fso As FileSystemObject
Dim tsInput As TextStream
Dim strQuery As String


Set fso = New FileSystemObject
Set tsInput = fso.OpenTextFile(strFilepath, 1)


Do While Not tsInput.AtEndOfStream


strQuery = strQuery & vbCrLf & tsInput.ReadLine


Loop


tsInput.Close
Set fso = Nothing


GetSQL = strQuery


End Function

ODBC Connection String:-

ODBC;Description=sql prod 1;DRIVER=SQL Server;SERVER=ServerName;Trusted_Connection=Yes;APP=Microsoft office XP;DATABASE=DatabaseName;Network=NetWorkName;TABLE=TableName

Please help me to resolve this problem.

Thanks
Kashif

 

[kashif.special2005]

Hi,

I just want to clarify one thing is that the ODBC connection string are used in MS-Access to linked the SQL Server table.

Thanks
Kashif

 

[kashif.special2005]

Hi,

Kindly request to all vba professionals please give me some advice that how can I solve this problem.

Thanks
Kashif