Help with Access MySQL pass through queries!

[glavster]

Hello all, I have an Access DB connected to a MySQL DB using linked tables. In access I've got several various update and append queries and a local products table that help populate the MySQL DB. Everything works fine but it takes forever to run the queries. I was told that if I use SQL pass-through queries would help speed things up but I don't know how to do this. I have set up the DNS and connected to the MySQL but that's it.

I was wondering if I can use the existing queries by just pasting them into a pass-through.

Any help appreciated.

Paul

 

[DonQuichote]

In the query window, right-click and choose "SQL specific" > "Pass-through query" (from the top of my head).

Now, you can edit the SQL itself. You will probably have to do so, as you need MySQL's dialect and not Access' dialect.

If you don't have the proper indexes in place, your queries can still be very slow. In MySQL, you can use the EXPLAIN command to see how efficient a query is executed.

You can mark the queries as "pass-through" and just see what happens. You will hopefully get a decent error message telling you what is wrong and therefore what to change. One thing, for instance: identifier quotes are backtics in MySQL (or optionally double quotes if your server runs in ANSI mode), not square brackets.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[glavster]

Hi, thanks for the reply. My queries are quite small and simple. But I was wondering if I should put the local control table in the mysql db first before making any pass-through queries.

Here is one of my queries:

INSERT INTO va_items ( item_code, manufacturer_code, item_name, trade_price, buying_price, stock_level, issue_date, preview_url, manufacturer_id, item_type_id, item_id )
SELECT [Enhanced-GB].item_code, [Enhanced-GB].manufacturer_code, [Enhanced-GB].item_name, [Enhanced-GB].trade_price, [Enhanced-GB].buying_price, [Enhanced-GB].stock_level, [Enhanced-GB].issue_date, [Enhanced-GB].preview_url, va_manufacturers.manufacturer_id, va_item_types.item_type_id, [Enhanced-GB].item_id AS Expr1
FROM ([Enhanced-GB] INNER JOIN va_manufacturers ON [Enhanced-GB].manufacturer_name = va_manufacturers.manufacturer_name) INNER JOIN va_item_types ON [Enhanced-GB].Sub_SubCategory = va_item_types.item_type_name;
[code]

The table Enhanced-GB is the local table in the access DB.

I read Converting the SQL Server code from Access to T-SQL typically comes down to replacing the underscore from the table name with a period (dbo_Orders in our example will become dbo.Orders) and replacing double-quotes, signifying a text value, to single-quotes. But it's probally not that easy.

Paul

 

[DonQuichote]

If your tables are in the same database, you do not need to give it, as far as I know. furthermore, replace [] with `` (or better, use table names without special characters), and you are done.

However,
If one of the joined tables is in Access itself (a local table), there is no way that MySQL can perform that join. So either you cannot make it a pass-through query and let Access perform the join (potentially needing the entire table from the server), or put that table in MySQL also.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[glavster]

Hello DonQuichote, 2 day later I get around to reading your reply which I appreciate very much. That said I don't fully understand your reply. Either you’re extremely cryptic or you've made a few typos.

What exactly did you mean by this;

"If your tables are in the same database, you do not need to give it, as far as I know."

So basically I should start by changing,
[] to ''
_ to . for table names
" to ' for text values

And hope for a good error message.

Thanks for your help,
Paul

 

[glavster]

Hello again DonQuichote, I did what I mentioned above and I get an error which I've attached. I was wondering if the '_' in field names shoud be replaced with a '.' also.

The query I'm trying to run is;

INSERT INTO va.categories ( category_name, category_path, is_showing )
SELECT First(va.c2k.Category) AS 'Category Field', '0,' AS category_path, '1' AS is_showing
FROM va.c2k
GROUP BY va.c2k.Category
HAVING (((Count(va.c2k.Category))>1));

could you have a quick look at point me in the right direction. All the tables are in the same MySQL database now.

Thanks,
Paul

 

[DonQuichote]

When you entered the link to MySQL in the table definition in Access or in the connection setting, you probably provided a database name in that connection. Therefore, you can omit the database name. If not, you should give it. Assuming that your database is called "va", you could try omitting it, but it can not hurt to keep it.

I think MySQL has no "first" aggregate function, as there is no defined order of a table. So you could use MIN() instead, but in this case there is no need because you are already grouping by that field. Furthermore, quote string values with apostrophes and identifiers with backticks:

INSERT INTO va.categories(category_name,category_path,is_showing)
       SELECT va.c2k.Category AS `Category Field`, '0,' AS category_path, '1' AS is_showing
              FROM va.c2k
              GROUP BY va.c2k.Category
              HAVING COUNT(va.c2k.Category)>1;

Assuming your database can be omitted, this query can be as short as:

INSERT INTO categories(category_name,category_path,is_showing)
       SELECT Category, '0,', '1'
              FROM c2k
              GROUP BY Category
              HAVING COUNT(Category)>1;

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[glavster]

Hello, the DB name is 'geminus' and va_categories is a table name. I change the '_' to a '.' because I taught I was supposed to do that. I'll give your first code snippet a try.

Paul

 

[glavster]

Ok, I managed to get your 1st code snippet to work but I changed the '.' in tables names back to '_'.

I've got stuck on the second query;

INSERT INTO va_categories (category_name, parent_category_id, category_path, is_showing)
SELECT va_c2k.SubCategory AS 'SubCategory Field', va_c2k.cat1, '0,' & 'cat1' & ',' AS category_path, '1' AS is_showing
FROM va_c2k
GROUP BY va_c2k.cat1, '0,' & 'cat1' & ',', va_c2k.SubCategory
HAVING COUNT (va_c2k.Category) >1 AND COUNT (va_c2k.SubCategory) >1;

Something wrong with the last line, FUNCTION geminus.COUNT does not exist. It's much like the first query but with with 2 COUNT(s)

Thanks,
P

 

[glavster]

Sorry, I got the above to work. Just had some silly spaces to remove.

Paul

 

[glavster]

OK I give up I can't figure out what to do with this code.

INSERT INTO va_categories (category_name, parent_category_id, category_path, is_showing)
SELECT (va_c2k.SubCategory) AS 'SubCategory Field', va_c2k.cat1, '0,'cat1',' AS category_path, '1' AS is_showing
FROM va_c2k
GROUP BY va_c2k.cat1, '0,'cat1',', va_c2k.SubCategory
HAVING COUNT(va_c2k.Category)>1 AND COUNT(va_c2k.SubCategory)>1;

It's this part that's got me stumped, '0,'cat1',' AS category_path. I shoud be getting a value for cat1 but all I get is 0,cat1,. How do I change that?

Paul