List:General Discussion« Previous MessageNext Message »
From:Colin McKinnon Date:January 13 2000 3:28pm
Subject:Access keeps asking me for parameters.
View as plain text  
Am I doing something stupid here?

I want to run a pass-thru query on a Mysql table (definition follows) using
MS Access:

SELECT branch_number, stock_take, Sum(quantity) AS tot_qty,
Count(orig_barcode) AS tuples
FROM unknown 
WHERE stats=0
GROUP BY branch_number, stock_take

but MS-Access then asks me for a value for .tot_qty and .tuples then
displays the results, giving what I expected for the first 2 columns, and
what I typed in for the latter 2!

The odd thing is, if I don't consolidate it workds fine:
SELECT branch_number, stock_take,  quantity, orig_barcode FROM unknown
WHERE stats=0

Even if I create the aliases:
SELECT branch_number, stock_take,  quantity as totqty, orig_barcode as
totcnt FROM unknown WHERE stats=0

The queries all work fine if I paste them into into a mysql session in a
telnet window. I've tried setting and unsetting the "Return table names in
SQLDescribeCol" option - no difference.

I have a mysql (3.22.25) database. 
I have MS-Access 97. 
I have MyODBC 2.50.1900

mysql> desc unknown;
+---------------+---------------+------+-----+---------+----------------+
| Field         | Type          | Null | Key | Default | Extra          |
+---------------+---------------+------+-----+---------+----------------+
| dummy         | int(11)       |      | PRI | 0       | auto_increment |
| orig_barcode  | varchar(15)   |      | MUL |         |                |
| branch_number | varchar(4)    | YES  |     | NULL    |                |
| stock_take    | int(11)       | YES  |     | NULL    |                |
| quantity      | int(11)       | YES  |     | NULL    |                |
| reject_reason | varchar(25)   | YES  |     | NULL    |                |
| md_barcode    | varchar(13)   | YES  |     | NULL    |                |
| description   | varchar(50)   | YES  |     | NULL    |                |
| ticket_price  | double(16,4)  | YES  |     | NULL    |                |
| sell_price    | double(16,4)  | YES  |     | NULL    |                |
| stats         | tinyint(4)    |      | MUL | 0       |                |
| date_added    | timestamp(14) | YES  |     | NULL    |                |
| area          | int(11)       | YES  |     | NULL    |                |
+---------------+---------------+------+-----+---------+----------------+
13 rows in set (0.00 sec)               

Thread
Access keeps asking me for parameters.Colin McKinnon13 Jan