Primary Key issue affecting Where clause?

[VBAjedi]

The "Where" segment of the following query is not working. Specifically, while it does return rows that match, the result set also includes rows with totally different values in them ("2005", "2007", etc):

SELECT SFMSPUSR_INDEX_NO.APPN_YEAR, SFMSPUSR_INDEX_NO.INDEX_NO, SFMSPUSR_INDEX_NO.INDEX_NO_TITLE, SFMSPUSR_INDEX_NO.ORG_CODE
FROM SFMSPUSR_INDEX_NO
WHERE (((SFMSPUSR_INDEX_NO.APPN_YEAR)="2011"));

SFMSPUSR_INDEX_NO is a linked table to an external database I don't control, and has no primary key. I'm guessing that's at the root of the unpredictable "WHERE" behavior, but I don't know how to fix it. No column in the data can serve as a true key (some whole rows in the table appear to be blank?!?!?). And with a linked table I can't add a column to serve as the key, can I?


VBAjedi

 

[lameid]

Two thoughts...

If it is an Access database, try compacting and repairing the database containing the file.

If it is not an Access database, look for updated ODBC drivers and search for ODBC issues with the product.

 

[VBAjedi]

Hey lameid,

It's a DB2 database. I'll check into whether there's an issue with the ODBC driver, but it's worked fine for me in the past in querying other tables in that database.

In case my original hunch was right and this is related to the lack of a proper primary key, I'm also trying the whole "append all records from the linked table to a local table which mirrors all columns but also adds an Autonumber column" approach.

VBAjedi

 

[lameid]

Unless you are trying to run an action query, Access shouldn't care if you have a PK or not on linked ODBC data. Now whether it gives you a quick result or not is another matter.


Have you considered a SQL Pass Through query?

In short you write the SQL in the native datbase format, put it in a query, change it to Pass-through and specify a connect string.... Access hands the SQL statement to the database and gets the result.

As Jet is parsing the query it shouldn't matter but try changing your double quotes to single quotes.

 

[VBAjedi]

Well, I tried using a different version of the ODBC driver for my data source. At first it appeared to work (the query I was working on started working correctly), but then the behavior resurfaced when I built a new (similar) query.

Then a coworker suggested I turn on the Totals row in Query Builder to make sure there wasn't something funky going on in there. That row displayed "Group By" for all fields, so it looked fine. But just the act of turning the Totals row on made the query work right, at least for now. Huh?!?

Does that give anyone a clue as to what the problem might have been?

VBAjedi