UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Problems Linking Access To A MySQL View With No Unique Key, Access 2010    
 
   
PhilS
post Jul 14 2015, 01:47 AM
Post#21



Posts: 404
Joined: 26-May 15
From: The middle of Germany


QUOTE
The picture shows an example of the data and how finding a unique key is not possible without some database changes

You could include "Cases" and "Weight" in the Primary Key in Access and replace those NULL-Values in the key-columns with 0.
- But I don't think the missing PK is the reason for your trouble.

I rather guess the ODBC-Driver is messing up the Metadata of the View or is returning weird data with the results.
I recommend you try upgrading or downgrading the driver and see if that makes any difference.
Unfortunately it's been a while since my last Access+MySQL-project, so I don't know the quirks of the current MyODBC-Drivers.
Go to the top of the page
 
PeterK
post Jul 14 2015, 11:58 AM
Post#22



Posts: 957
Joined: 31-December 06



Thanks for continued input on this.

Try 5.3 driver ? - yes this is on my list.

Thanks BR for the Unique identified document - laden with sample SQL to try which I really love. ( By the way BR , and this really is off-topic so feel free to ignore, I searched recently for your post with examples of how your use MySQL log data ( or something like this ) to explore ODBC errors - it was an extensive post as I remember where I only scratched the surface at the time - I'd like to re-visit this but can't find the post ! If you have a moment and can easily find the material that would be great. )

QUOTE
How do you get identical TransferID and StockTranID when both are 'Transfer Out'?
A tblStockTrans entry is a stock item that can exist over several pallets - it is therefore possible that stock being moved from one place to another will involve more than 1 entry for the same stock item. ( StockItem + Pallet ID is unique ). Whilst unlikely it is possible that two transfer records could have identical cases (units) and weights (PhilS's point). Not all stock has a pallet id.

QUOTE
Does tblStockMoves have another field as PK that isn't included, or is it because of the second join to tblStockmast2?
The PK on tblStockMoves is MoveID and this indeed may be a good field to add to include to make a record unique. StockTranID + MoveID is unique.

Stored Procedures - yes my experience is light here and exploring this option is a good idea.

MySQL client warnings - running the query in phpMyAdmin produces a warning that there is no unique key and therefore updating is not possible.

So at the moment I can rest with the Access Union Query but I intend to follow up these other points, including tests with 5.3 and will post again with what I find.
Go to the top of the page
 
BananaRepublic
post Jul 14 2015, 12:05 PM
Post#23


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


OK, I'll bet you that the warning is actually gumming up the works here.

Try this as a test:

CODE
SET GLOBAL max_error_count = 0;


Then try to link & run the view. Does it work?
Go to the top of the page
 
PeterK
post Jul 14 2015, 03:09 PM
Post#24



Posts: 957
Joined: 31-December 06



Thanks. I ran this code in phpMyAdmin but the error message still appears in MySQL. Re-linking the view still causes the crash.
Go to the top of the page
 
cheekybuddha
post Jul 14 2015, 03:20 PM
Post#25


UtterAccess VIP
Posts: 9,282
Joined: 6-December 03
From: Telegraph Hill


Try adding a concatenated StockTranID + MoveID to avoid the warning and see if it helps.

hth,

d
Go to the top of the page
 
BananaRepublic
post Jul 14 2015, 03:23 PM
Post#26


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


The fact you got a warning still indicates that the setting had no effect -- what I was recalling is that if a warning was produced, it somehow got treated as a resultset of its own when it shouldn't be. If David's suggestion removes the warning in phpMyAdmin then that's certainly something to try out in Access.
Go to the top of the page
 
PeterK
post Jul 14 2015, 04:07 PM
Post#27



Posts: 957
Joined: 31-December 06



I've just realised that a tblMoveID entry does not apply for Transfers In, so it is of no use to generate a unique key. However I went back to my Random number and adding this removes the error message in phpMyAdmin. This time I tested the new view in a brand new Access database - Access does not crash but shows the correct number of records, all marked as deleted.
Go to the top of the page
 
BananaRepublic
post Jul 14 2015, 04:13 PM
Post#28


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


Using random # would be kind like trying to grab sands by clenching your fists hard. It'd just leak out of your fingers anyway because as Access scrolls around, it'll cause the expressions to be re-evaluated and you would get different results every time. Also, just to check what is the data type of random output? How is it cast in the Access?

What about the row_number() cheat that was earlier suggested? That might be more consistent.
Go to the top of the page
 
cheekybuddha
post Jul 14 2015, 04:24 PM
Post#29


UtterAccess VIP
Posts: 9,282
Joined: 6-December 03
From: Telegraph Hill


Peter,

Are you able to attach a backup dump of:
tblStockTransfers
tblStockMoves
tblStockTrans
tblStockmast2

There might be a way of restructuring the view's SQL to make it work better, but it's easier to see the whole thing than bit by bit.

Having said that, if you can build the view as an Access query using the original linked tables, then perhaps it's not necessary to pursue the issue further (though I hate having niggly unsolved issues!)

d
Go to the top of the page
 
PeterK
post Jul 15 2015, 02:21 AM
Post#30



Posts: 957
Joined: 31-December 06



The random number - my mistake - didn't spot the blue warning above the green ok bar in phpMyAdmin. BR is quite right - neither Access nor MySQL is fooled by my random number trick to get a unique key. Sorry.

BR - Adding a row number to data associated with a stock transfer is probably the correct approach from a database design viewpoint - as I do for several other master detail tables like PO's, SO's etc. But implementing this now is not a 5-minute change !

David - yes, though I have a solution I'd also like to know what is causing the problem. I should be able to post some SQL ( I assume you can upload zip files ) in the next day or so - a cut down and maybe data adjusted version of the tables that are causing the problem. If you have the time to look at this then that would be good.
Go to the top of the page
 
cheekybuddha
post Jul 15 2015, 05:25 AM
Post#31


UtterAccess VIP
Posts: 9,282
Joined: 6-December 03
From: Telegraph Hill


Yes, Peter, please post some dumps (Create Tables and Inserts) if you get a chance - I'm curious too!

d
Go to the top of the page
 
PeterK
post Jul 15 2015, 10:07 AM
Post#32



Posts: 957
Joined: 31-December 06



Happy ending.... No problems with MySQL ODBC 5.3 Driver ANSI version. So, as many suspected, it was a driver problem. But I still plan to revisit the database design here in due course. When creating the linked table I picked TransferID as the unique key even though it isn't. The view returns data almost instantaneously.

If I try and change the data, just for testing purposes, I get a warning message that the table is not updateable for views where I have no random key. With the random key, as BR said, all the random numbers change and I get a message that someone else has changed the data. Either way the view is not updateable - which is as it should be.

So I think I might cautiously switch from 5.1 to 5.3 !

Thanks again to everyone who has contributed to solving this problem. My apologies for not trying 5.3 earlier.
Go to the top of the page
 
PeterK
post Jul 17 2015, 12:56 PM
Post#33



Posts: 957
Joined: 31-December 06



Earlier in this post I mentioned some work that BananaRepublic had done on MySQL linked tables.Here it is in case anyone was intrigued. I think it contains some very interesting information.
Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 11:40 AM