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 Stringconn = "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.Recordsetcn.Open connrs.Open "Select * from peoplemain", cn  'Error Line
Sheet1.Range("A1").CopyFromRecordset rsrs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub [/code]

0
edit delete
are you sure?
asked Kashif Khan

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=;"

0
edit delete
are you sure?
answered Krysztof Spaliński

Post an answer but please login first

Post Answer
  1. Please login to post answer

Similar Questions

Tagsfor this question

Forumall questions

This page is the archive of this page:https://www.connectionstrings.com/questions/54177/excel-vba-error-odbc-connection-failed/, the copyright belongs to the original page!
aspxhtmlmobile0898kendouidemosaspxhtml docs