petroc88
asked on
Sage Line 50 ODBC unusual SQL anomaly
Querying Sage tables using Access and Excel
SELECT INVOICE.ACCOUNT_REF, INVOICE.INVOICE_NUMBER
FROM INVOICE INVOICE
WHERE (INVOICE.ACCOUNT_REF = "SMITH")
This works fine from Excel 2003 Query and from Access 2007
SELECT INVOICE.ACCOUNT_REF, INVOICE.INVOICE_NUMBER
FROM INVOICE INVOICE
WHERE (INVOICE.ACCOUNT_REF <> "SMITH")
Works fine from Excel, returns "ODBC call failed - invalid filter in Where clause" from Access
Same results using like/Not like instead of =/<>
Would be grateful for any insight on this.
SELECT INVOICE.ACCOUNT_REF, INVOICE.INVOICE_NUMBER
FROM INVOICE INVOICE
WHERE (INVOICE.ACCOUNT_REF = "SMITH")
This works fine from Excel 2003 Query and from Access 2007
SELECT INVOICE.ACCOUNT_REF, INVOICE.INVOICE_NUMBER
FROM INVOICE INVOICE
WHERE (INVOICE.ACCOUNT_REF <> "SMITH")
Works fine from Excel, returns "ODBC call failed - invalid filter in Where clause" from Access
Same results using like/Not like instead of =/<>
Would be grateful for any insight on this.
and this is a SQL statement in a query def in Acces? If not, how are you using this statement?
JimD.
JimD.
ASKER
Correct - causes failure in Access but not in Excel query. Both programs use the same ODBC driver.
ASKER
Correct - SQL statement is copied from a query def in Access. I have also copied the SQL frm Excel query into Access just to cover everything.
Try creating a new querydef and construct it manually with the GUI or type the SQL statement manually. Don't cut and past from Excel.
Keep it simple with only the INVOICE.ACCOUNT_REF field being selected and the <> WHERE clause check.
JimD.
Keep it simple with only the INVOICE.ACCOUNT_REF field being selected and the <> WHERE clause check.
JimD.
ASKER
Thank you for your suggestion which I have tried both from the GUI and separately by typing in the SQL but it gives the same result I'm afraid.
<<Thank you for your suggestion which I have tried both from the GUI and separately by typing in the SQL but it gives the same result I'm afraid.>>
Occasionally we'll get someone with non-visible character in a SQL string and it will cause problems, which is why I wanted you to try that.
There's nothing wrong with your SQL statement, so I checked a bit further and this doesn't look to be an Access problem per say, but a buggy ODBC driver from Sage. I found numerous instances of posts on the net with exactly this problem. Sage's comment to one user was that anything outside of their report writer was unsupported and therefore were not interested in fixing it.
What's weird is that it works for Excel. We could poke around on that and see what the issue is. First would be to enable ODBC logging and see exactly what is getting sent to Sage from Excel and Access.
Before you do that though, one quick thing that might work is trying to use ANSI92 compatibility mode.
In the database, click the Office Button, then the Options button at the bottom. Click "object designers" off on the left and then under queries, check the "SQL Server Compatible Syntax (ANSI 92)" box.
Close the options and then create the same query as you just tried (just the one field and the <> WHERE check) and see if that works.
JimD.
Occasionally we'll get someone with non-visible character in a SQL string and it will cause problems, which is why I wanted you to try that.
There's nothing wrong with your SQL statement, so I checked a bit further and this doesn't look to be an Access problem per say, but a buggy ODBC driver from Sage. I found numerous instances of posts on the net with exactly this problem. Sage's comment to one user was that anything outside of their report writer was unsupported and therefore were not interested in fixing it.
What's weird is that it works for Excel. We could poke around on that and see what the issue is. First would be to enable ODBC logging and see exactly what is getting sent to Sage from Excel and Access.
Before you do that though, one quick thing that might work is trying to use ANSI92 compatibility mode.
In the database, click the Office Button, then the Options button at the bottom. Click "object designers" off on the left and then under queries, check the "SQL Server Compatible Syntax (ANSI 92)" box.
Close the options and then create the same query as you just tried (just the one field and the <> WHERE check) and see if that works.
JimD.
ASKER
No change with the ANSI 92 box checked. How do I go about enabling ODBC logging?
<<How do I go about enabling ODBC logging?>>
Settings, Control Panel, Administrative Tools, Data Sources (ODBC). Click the "Tracing Tab" . Set a path for the log file, check "Machine wide tracing for all user identities". Click apply. Then click "Start Tracing Now".
Run the Excel query and then the Access query.
Then go back and click "Stop Tracing Now".
Have a look at the log file.
JimD.
Settings, Control Panel, Administrative Tools, Data Sources (ODBC). Click the "Tracing Tab" . Set a path for the log file, check "Machine wide tracing for all user identities". Click apply. Then click "Start Tracing Now".
Run the Excel query and then the Access query.
Then go back and click "Stop Tracing Now".
Have a look at the log file.
JimD.
ASKER
I attach the two SQL statements produced first by Excel and then by Access for your perusal! I have used a different table to query because the original tables were huge. These queries are still showing the same problem however.
EXCEL 1244-124c ENTER SQLExecDirectW
HSTMT 053E1830
WCHAR * 0x08940B74 [ 130] "SELECT SALES_LEDGER.ACCOUNT_REF, SALES_LEDGER.STATUS_NUMBER
MSACCESS 12a0-10f4 ENTER SQLExecDirectW
HSTMT 08C818B0
WCHAR * 0x0D8E2840 [ -3] "SELECT 'ACCOUNT_REF' ,'STATUS_NUMBER' FROM 'SALES_LEDGER' WHERE NOT(('STATUS_NUMBER' = 0 ) ) \ 0"
SDWORD -3
DIAG [37000] Syntax error: Invalid filter in WHERE clause (0)
EXCEL 1244-124c ENTER SQLExecDirectW
HSTMT 053E1830
WCHAR * 0x08940B74 [ 130] "SELECT SALES_LEDGER.ACCOUNT_REF, SALES_LEDGER.STATUS_NUMBER
MSACCESS 12a0-10f4 ENTER SQLExecDirectW
HSTMT 08C818B0
WCHAR * 0x0D8E2840 [ -3] "SELECT 'ACCOUNT_REF' ,'STATUS_NUMBER' FROM 'SALES_LEDGER' WHERE NOT(('STATUS_NUMBER' = 0 ) ) \ 0"
SDWORD -3
DIAG [37000] Syntax error: Invalid filter in WHERE clause (0)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks for your assistance. Of course it would be nice to know why some people do not have this problem but you have given me the tools to investigate this further.
JimD.