Excel VBA ERROR ODBC connection failed

Hi,

it is working in Excel-32 bit version, but not working in excel-64 bit.

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.
[code]
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
[/code]

1 answer

Hello,

try use:

Dim cn As Object

Dim rs As Object

Set cn = CreateObject("ADODB.connection")

cn.open conn

set rs=cn.execute("Select * from peoplemain")

or dont use connection - just recordset

Set rs = CreateObject("ADODB.RecordSet")

rs.open "Select * from peoplemain", conn, 2, 4

If error occur change connection string to standard connection string:

conn="Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=G:\Workflow Tools (Michael Cantor)\Tool For Fixing Bug From Michael Cantor\PI MDT Reconciliation Workflow Tool\SampleforPractice.accdb;
Uid=Admin;Pwd=;"