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.
I've also tried (as suggested by Tranman) to use a File DSN. Same result.
Any help is greatly appreciated. Also, please let me know if there is a more appropriate forum for this issue.
Thanks much.
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.
I've also tried (as suggested by Tranman) to use a File DSN. Same result.
Any help is greatly appreciated. Also, please let me know if there is a more appropriate forum for this issue.
Thanks much.