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]

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 log in first

Post Answer
  1. Please log in to post answer

Similar Questions

Tagsfor this question

Forumall questions