From SQL to Access linked table issues - ODBC fail

Question

I am developing form in MS access. I use SELECT function to load the data to the form. When I am creating a SQL-linked table (External data, ODBC database ..), I am getting error '3146 ODBC -- call failed'. But when I am importing the table with no link to SQL, it works.

Other select functions work with linked table fine.

    Dim rs As Recordset
    Dim rs_Change_Control As Recordset

    CustId = Me.txt_ID.Value
    If Len(CustId) > 0 Then

       Set rs = CurrentDb.OpenRecordset("SELECT a.key, a.date 
        FROM (SELECT gnr.key, gnr.date
            FROM gnr) a 
                INNER JOIN (SELECT key, MAX(date) AS maxtime 
                FROM gnr GROUP BY key) b 
            ON a.key = b.key AND a.date = b.maxtime WHERE a.key = '123456789';")
If rs.EOF = False Then
Me.txt_ID.Value = rs!key
    End if

First and foremost, please specify the ODBC database. SQL is a programming language which even Access uses! So an SQL linked table is not specific. Did you mean SQL Server? MySQL? PostgreSQL? Oracle? All of which Access can connect to. Please tag appropriately. — Parfait
Second, the code posted is not linking an external database table with ODBC but calling a recordset in local Access database. Please add the problem section. Also, add DB.engine error in your error handle for more informative error message instead of generic "ODBC call failed." — Parfait

Kryptonian Kryptonian · Accepted Answer · 2015-08-18T07:07:45

You need to correct your quotes in query,

Set rs = CurrentDb.OpenRecordset(SELECT a.key, a.date 
    FROM ("SELECT gnr.key, gnr.date
        FROM gnr) a 
            INNER JOIN (SELECT key, MAX(date) AS maxtime 
            FROM gnr GROUP BY key) b 
        ON a.key = b.key AND a.date = b.maxtime WHERE a.key = '123456789'";)

should be

 Set rs = CurrentDb.OpenRecordset("SELECT a.key, a.date 
    FROM (SELECT gnr.key, gnr.date
        FROM gnr) a 
            INNER JOIN (SELECT key, MAX(date) AS maxtime 
            FROM gnr GROUP BY key) b 
        ON a.key = b.key AND a.date = b.maxtime WHERE a.key = '123456789'")

From SQL to Access linked table issues - ODBC fail

2 Answers