Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ODBC driver produces wrong SQL syntax

Status
Not open for further replies.

motoslide

MIS
Oct 30, 2002
764
US
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top