Subquery not working with Access linked tables

Question

I'm attempting to build a tool that pulls information from a database containing financial information, but I've hit a wall with this problem. The following query would give me some of the data I require, but as soon as I add in the sub-query, I get the error message

"ODBC--call failed".

If I replace the sub-query with a static "WHERE TRAN_NUMBER IN (0,1,2,3..)" it returns the expected values. The sub-query returns the expected values when run by itself.

I'm using MS Access and C# and the OleDB library to do this

Any Ideas what I've done incorrectly?

SELECT NOMINAL_CODE, DETAILS, AMOUNT, TYPE
FROM AUDIT_JOURNAL
WHERE TRAN_NUMBER IN
   (SELECT AUDIT_TRAIL_ID
    FROM PROJECT_TRAN 
    WHERE AUDIT_TRAIL_ID > 0);

A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: ODBC--call failed

Edit: Looks like Subqueries don't work when linked to another database via ODBC (The original database is a SageLine50 database). I've imported the tables statically and it seems to work. Does anyone know how I can make this work without statically importing the tables?

Does the entire query execute when ran directly against the db? — Dan, Feb 12 '14 at 23:39
Yup, the subquery runs correctly by itself, when added together it doesn't work. Could this be something to do with the database being linked by ODBC connection? — user3295060, Feb 12 '14 at 23:53
Do you mean the tables are not native on MS-Access but linked through ODBC from another database? — Steve, Feb 12 '14 at 23:54
Yes, I've linked another database (Sage line 50 v18) in to MS-Access via ODBC connection so that I can access it using the OleDB library — user3295060, Feb 12 '14 at 23:58
If you want to make a test you could try to copy the tables from the external database (not linking) and then check if the query works, but I am quite sure that it will work. — Steve, Feb 13 '14 at 00:17
Yup, that works.. Super frustrating. Now I need to see if it's possible to do the same thing while databases are linked — user3295060, Feb 13 '14 at 00:23
You are posting your query, but you have not posted the code that calls this query. I have had issues with code that when concatenated did not contain necessary spaces between the lines. `....type" + "FROM....` When put together = `....typeFROM....` — wruckie, Feb 13 '14 at 00:27
If you want to find out why a SQL query isn't working at least tell us what database it's running against - SQL Server? Oracle? — Nick.Mc, Feb 13 '14 at 00:50
@wruckie You're right, I've encountered that problem, but I'm sure that it's formatted correctly in code. — user3295060, Feb 13 '14 at 00:58
@ElectricLlama Sorry about that. It's a SageLine50 database linked to MS-Access by ODBC connection. — user3295060, Feb 13 '14 at 01:02
What is your MS Access ODBC linked table pointing at? I'm pretty sure Sage doesn't run on MS Access. What database is that query run against? — Nick.Mc, Feb 13 '14 at 01:45
I'm not sure what database Sage is running on, I just used MS-Access to import/link the Sage tables and now I'm doing everything through Access/OleDB driver. — user3295060, Feb 13 '14 at 01:53
So the query you have above is run in MS Access against tables that are linked to an unknown database. You'll probably have a lot more success if you run that query as a pass through query against your source database rather than running it in MS Access against linked tables. It would realyl help to find out what you are linked to. In all likelihood it is SQL Server. — Nick.Mc, Feb 13 '14 at 03:03

Gord Thompson · Accepted Answer · 2014-02-13T09:03:51.307

Instead of using an IN clause and a subquery you could try a JOIN

SELECT 
    aj.NOMINAL_CODE, 
    aj.DETAILS, 
    aj.AMOUNT, 
    aj.TYPE
FROM 
    AUDIT_JOURNAL AS aj
    INNER JOIN
    PROJECT_TRAN AS pt
        ON aj.TRAN_NUMBER = pt.AUDIT_TRAIL_ID
WHERE pt.AUDIT_TRAIL_ID > 0

re: your comment in the edit to your question

Looks like Subqueries don't work when linked to another database via ODBC (The original database is a SageLine50 database).

That is certainly not true for all ODBC linked tables. I created tables [AUDIT_JOURNAL] and [PROJECT_TRAN] in SQL Server 2008 R2, created linked tables in Access 2010, and your original query works fine in Access. (Mine does, too.) Your issue is almost certainly caused by a deficiency in the SageLine50 ODBC driver.

Subquery not working with Access linked tables

1 Answers1