Re: [Help-glpk] ODBC Connection to MS-Access - WHERE Clause Syntax problems

[Meketon, Marc]

The clause in the error message that says .[Manuf Code]=[0012LBLK] leads me to believe that it thinks 0012LBLK is a field name, and not a string constant.

 

Try switching double quotes and single quotes (I'm not sure that will work, but it's worth a try):

 

        "WHERE qProductsUnique.[Manuf Code]='0012LBLK'":

---

From: help-glpk-bounces+address@hidden [mailto:help-glpk-bounces+address@hidden On Behalf Of Alex Morelli
Sent: Saturday, June 25, 2011 2:30 PM
To: address@hidden
Subject: [Help-glpk] ODBC Connection to MS-Access - WHERE Clause Syntax problems

Hello,

I am using the following:

set psold dimen 3;
param qper{(i,j,k) in psold};

table products_table IN "ODBC"
        'DSN=glpk_Sales'
        'SELECT Product, MaterialDesc as pdesc, [Manuf Code] as prodman, QPer'
        'FROM qProductsUnique '
        'WHERE qProductsUnique.[Manuf Code]="0012LBLK"':
        psold <- [Product, pdesc, prodman], qper~QPer;

display psold;
------------------------------------------
qProductsUnique is a MS Access Query that returns unique records.  The data (0012LBLK) exists in the query.

I have tried many different syntax constructions for the WHERE clause and none work.  Removing the WHERE clause works fine, which suggests (to me the novice) that all of the field names are correctly specified.

For the above WHERE clause syntax the following error is received:

=====
The driver reported the following diagnostics whilst running SQLExecDirect
42000:1:-3100:[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query _expression_ 'qProductsUnique.[Manuf Code]=[0012LBLK]D'.

Yorke.mod:155: error on opening table products_table
=====
I don't know what the D means after [0012LBLK].
I really want to restrict the records to the following
        'WHERE qProductsUnique.[Manuf Code] Not Like "_*"':
But that would not work also - and that is why I tried something that I thought was simple.
I am not a programmer and do not understand the ODBC / SQL syntax.  I have spent a long time researching these errors and found nothing that I believe will help.

I have placed a space at the end of the FROM clause - I would appreciate if someone would also confirm if that is necessary.  When I remove the space at the end of the FROM clause I get a different error message --

=====
The driver reported the following diagnostics whilst running SQLExecDirect
07002:1:-3010:[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
Yorke.mod:155: error on opening table products_table
=====
I would appreciate any assistance.

Regards, Alex

The information included in this email and any attached documents from the Sender may be confidential and/or subject to legal privilege. This information is intended to be for the use of the addressee only. The information contained in this email may not represent
 the views or opinions of the Sender’s employer. If you are not the intended recipient, note that any disclosure, photocopying, distribution or use of the contents of the emailed information is prohibited. If you have received this email in error, please contact
 the sender promptly and delete the email from any system and destroy any hard copies. Thank you.

---

This e-mail and any attachments may be confidential or legally privileged. If you received this message in error or are not the intended recipient, you should destroy the e-mail message and any attachments or copies,
 and you are prohibited from retaining, distributing, disclosing or using any information contained herein. Please inform us of the erroneous delivery by return e-mail. Thank you for your cooperation.