Excel VBA ERROR ODBC connection failed

[kashif.special2005]

Hi,

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.

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

Please help me to resolve this problem.

Thanks
Kashif

 

[mole999]

probably considered, some tables are case sensitive ?

 

[VBABEGINER]

Hi Kashif, i think after rs.open you have to give source name..

--

You can try this may b worked for you..

Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset


Dim Connect As String, Source As String
Dim DBFullName As String


'Your path will be different
DBFullName = "C:\Users\643550\Documents\Database2.accdb"


'Open the connection
Set Connection = New ADODB.Connection
Connect = "Provider=Microsoft.ACE.OLEDB.16.0;"
Connect = Connect & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Connect


Set Recordset = New ADODB.Recordset
With Recordset
Source = "SELECT * from..

.Open Source:=Source, ActiveConnection:=Connection

'Write recordset
 Sheet1.Range("A1").CopyFromRecordset rs

 

[kashif.special2005]

Hi All,

Thank you so much for reply, however after making changes suggested by you, still not working.

Thanks
Kashif

 

[kashif.special2005]

Hi,

I somehow manage to retrieve the query's result in recordset, however setting the accdb=nothing, it show the error message like below.

Error Message:

Microsoft Access has stopped working


Windows can try to recover your information

Sub FetchData()


    Dim rs As ADODB.Recordset
    Dim ss As String
    Dim conn As String
    Dim accdb As Access.Application
    
    Set accdb = New Access.Application


    
   Call accdb.OpenCurrentDatabase(Filepath:="G:\Workflow Tools (Michael Cantor)\Tool For Fixing Bug From Michael Cantor\PI MDT Reconciliation Workflow Tool\PI Database.accdb", exclusive:=False)


    ss = "[B]Select * from peoplemain[/B]"


    Set rs = New ADODB.Recordset
    Set rs = accdb.CurrentProject.Connection.Execute(ss)
    
    Sheet1.Range("A1").CopyFromRecordset rs




[B]    Set accdb = Nothing  'Here it is giving me that error message[/B]
    rs.Close
    Set rs = Nothing
    'cn.Close
    
    MsgBox "done"


End Sub

Thanks
Kashif

 

[ScottR]

I don't understand why you are querying Access for a SQL Server table? Why deliberately add in an extra level of complexity when you can query SQL Server directly?

 

[kashif.special2005]

Hi,

Thanks for reply, actually my main database is MS-Access database, and all the required tables are in MS-Access database except 4 tables, that's why I created linked table's for these 4 in MS-Access so that I can create a join query from one platform, in my post for example purpose I gave linked table name 'peoplemain', actually those queries are not working that is taking reference of "Linked" table.

I hope I clarify your thought.

Thanks for your reply.

Thanks
Kashif

 

[ScottR]

actually those queries are not working that is taking reference of "Linked" table

Well... exactly. Those connections are failing because you're routing the query through a different "engine" to a different location with a different native syntax and (most importantly) a completely different security environment. Access is designed to handle that internally but for pass through queries? I seriously doubt it.

Query separately then join the results. Failing that create a query in Access that exposes the actual data in the underlying SQL Server tables you need and query that.

 

[kashif.special2005]

Hi ScottR,

Thanks for reply, actually it is working in excel 32-bit version, 32-bit users are successfully getting the results from linked tables from join query, but some users moved in excel 64-bit version, and 64-bit users are facing the query problem from linked table error "ODBC connection failed".

Thanks
Kashif

 

[ScottR]

I'm not surprised. There are different versions of "Microsoft.ACE.OLEDB.12.0" for 32bit and 64bit architectures and they don't co-operate well. See here for potential steps to fix

http://blogs.microsoft.co.il/meravk...fice-32-bit-installed-on-a-64-bit-windows-os/