Query using ODBC incorrectly parsed.

[motoslide]

I think this is an issue with the ODBC (BBj ODBC)driver,
but the vendor thinks otherwise, so I'm asking some experts:

We are trying to use ODBC to extract data from a Business Basic application. I've created a System DSN as needed
and am able to view tables and extract records using Excel. When I try the same basic steps in Access, the query is mis-written.
In the example below, I'm just trying to retrieve a listing of Vendors from the AP Master file.
I've enabled ODBC logging and this illustrates the issue quite plainly as follows:
Log entry using "Excel":
SQL BEFORE: SELECT APM_VENDMAST.FIRM_ID, APM_VENDMAST.VENDOR_NBR, APM_VENDMAST.VENDOR_NAME, APM_VENDMAST.PHONE_NUMBER
FROM addon6.APM_VENDMAST APM_VENDMAST

INDEX INFORMATION FOR TABLE: APM_VENDMAST
APM_VENDMAST - Key Number: 0 - UNIQUE KEY
0 - FIRM_ID ASCENDING
1 - VENDOR_NBR ASCENDING
Opened Table: APM_VENDMAST -- Num Records: 1088
SQL AFTER: SELECT APM_VENDMAST.FIRM_ID, APM_VENDMAST.VENDOR_NBR, APM_VENDMAST.VENDOR_NAME, APM_VENDMAST.PHONE_NUMBER FROM ADDON6.APM_VENDMAST APM_VENDMAST;
SQL BEFORE EXECUTE: SELECT APM_VENDMAST.FIRM_ID, APM_VENDMAST.VENDOR_NBR, APM_VENDMAST.VENDOR_NAME, APM_VENDMAST.PHONE_NUMBER FROM ADDON6.APM_VENDMAST APM_VENDMAST;

Same log entry after performing a failed query from Access:
SQL BEFORE: SELECT Config, nValue FROM MSysConf

INDEX INFORMATION FOR TABLE: APM_VENDMAST
APM_VENDMAST - Key Number: 0 - UNIQUE KEY
0 - FIRM_ID ASCENDING
1 - VENDOR_NBR ASCENDING
Opened Table: APM_VENDMAST -- Num Records: 1088
SQL BEFORE: SELECT "FIRM_ID" ,"VENDOR_NBR" ,"VENDOR_NAME" ,"ADDR_LINE_1" ,"ADDR_LINE_2" ,
"ADDR_LINE_3" ,"ZIP_CODE" ,"PHONE_NUMBER" ,"PHONE_EXTEN" ,"CONTACT_NAME" ,"ALT_SEQUENCE" ,
"OPENED_DATE" ,"HOLD_FLAG" ,"FEDERAL_ID" ,"VEND_1099" ,
"VENDOR_ACCT" ,"FAX_NUMBER" ,"FOB" ,"AP_SHIP_VIA" ,
"RESERVED_STR" FROM "mas"

In this case, the "mas" is actually the USER ID I've got configured for the DSN.

Any help is greatly appreciated. Also, please let me know if there is a more appropriate forum for this issue.

Thanks much.

 

[Tranman]

motoslide,
There is an ODBC forum which might be a more appropriate venue for this post.

On a technical note, I had a sort of similar problem to yours once, with a Flex database (flipping the uid/pwd), and as I recall, I fixed it by setting up a file DSN instead of a system DSN. It had to do with the ODBC driver plugging information into the wrong keys in the registry.

This was back in '93 or '94 when ODBC was fairly new, so I don't remember the exact particulars, but I do remember that apparently identical functionality worked with file DSN, but not with system.

At least that's something you might try...

Tranman


"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain