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    
 
   
PeterK
post Jul 10 2015, 09:44 AM
Post#1



Posts: 957
Joined: 31-December 06



I have a MySQL view involving two selects linked by a UNION ALL statement. The view displays fine in phpMyAdmin. The view can be added as a linked table in Access. However every time the view is opened in Access it crashes.

I tried adding CURRENT_TIMESTAMP to the view in MySQL and adding the TIMESTAMP as a unique key when linking the table in Access but this has no effect. Is it the absence of a unique key that is causing this issue or is there some other reason ? Thanks.
Go to the top of the page
 
PeterK
post Jul 11 2015, 02:47 AM
Post#2



Posts: 957
Joined: 31-December 06



I tried adding a random number as a unique key and then specifying this key as the unique identifier when setting up the linked table. Here is the SQL for the union query that runs fine in MySQL but causes Access to crash...

CODE
select `tblStockTransfers`.`TransferID` AS `TransferID`,
       `tblStockMoves`.`StockTranID`    AS `StockTranID`,
       `tblStockmast2`.`Description`    AS `Description`,
       'Transfers Out'                  AS `Type`,
       `tblStockMoves`.`Cases`          AS `Cases`,
       `tblStockMoves`.`Weight`         AS `Weight`,
       FLOOR(RAND()*(99999-10)+10) AS R1
from   (((`tblStockTransfers`
          join `tblStockMoves`
            on(( `tblStockTransfers`.`TransferID` =
                 `tblStockMoves`.`StockTransferID` )))
         join `tblStockTrans`
           on(( `tblStockMoves`.`StockTranID` =
        `tblStockTrans`.`StockTranID` )))
        join `tblStockmast2`
          on(( convert(`tblStockTrans`.`ProductCode` using utf8) =
                    `tblStockmast2`.`StockCode_Sage` )))
union all
select `tblStockTransfers`.`TransferID` AS `TransferID`,
       `tblStockTrans`.`StockTranID`    AS `StockTranID`,
       `tblStockmast2`.`Description`    AS `Description`,
       'Transfers In'                   AS `Type`,
       `tblStockTrans`.`CaseCount`      AS `Cases`,
       `tblStockTrans`.`Weight`         AS `Weight`,
       FLOOR(RAND()*(99999-10)+10) AS R1
from   ((`tblStockTransfers`
         join `tblStockTrans`
           on(( `tblStockTransfers`.`TransferID` =
              `tblStockTrans`.`TransferID` )))
        join `tblStockmast2`
          on(( convert(`tblStockTrans`.`ProductCode` using utf8) =
                    `tblStockmast2`.`StockCode_Sage` )))
Go to the top of the page
 
cheekybuddha
post Jul 11 2015, 05:04 AM
Post#3


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


Hi Peter,

Presumably `tblStockTransfers`.`TransferID` is an indexed field (PK?)

Are you able to open the view in a recordset without crashing Access (with the view unlinked from the db)?

What version of MySQL are you using?

Can each TransferID contain more than one 'Transfers Out' and 'Transfers In'?

How different are tables `tblStockMoves` and `tblStockTrans`? Should they be one table with a field for In/Out?

d
Go to the top of the page
 
PeterK
post Jul 11 2015, 04:57 PM
Post#4



Posts: 957
Joined: 31-December 06



Hi David

Yes tblStockTransfers.TransferID is the PK.

No, attempting to connect to the view containing the union queries via a pass-through query causes Access to crash.

MySQL version 5.5.39 ODBC Driver 5.1

A Transfer can have multiple Transfer In's and Out's.

tblStockTrans and tblStockMoves are very different. tblStockTrans holds details of instances of a particular product. tblStockMoves records instances when stock is used in a sale or a process to generate further stock. At any time the tblStockTrans balance for an item is the original stock value ( held on tblStockTrans ) less the sum of the stock movements held on tblStockMoves.

I hope this provides you with the detail you wanted. If you need more information then please let me know. Thanks for your input on this.
Go to the top of the page
 
BananaRepublic
post Jul 11 2015, 09:06 PM
Post#5


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


PeterK, if I understood, the view does links fine but you just can't open it.

In this case, can you open the linked table object of that view in design view and see if the data types as shown in the design view makes sense?
Go to the top of the page
 
PeterK
post Jul 12 2015, 03:08 AM
Post#6



Posts: 957
Joined: 31-December 06



Hi Banana - yes the view shown in design view has all the right datatypes and the random number is correctly marked as the primary key.

Since I added the random number as the pk, displaying the view in Access lists all the rows as deleted - it does not crash immediately. If I refresh the view then Access crashes.
Go to the top of the page
 
PhilS
post Jul 12 2015, 06:04 AM
Post#7



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


Hi PeterK!
can you post the exact datatype definitions of all the columns in the view in MySQL?
And please add which datatypes are used in the linked view in Access.
Go to the top of the page
 
cheekybuddha
post Jul 12 2015, 06:23 AM
Post#8


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


In addition to Phil's questions,

I'm a bit stuck for ideas to help here. frown.gif

One longshot to investigate: The MyODBC Driver - are you using ANSI or Unicode version in your DSN (I'm guessing you're using a DSN?) Also, have you tried upgrading to version 5.3?

Do you specify any options in the DSN?

Can you post the connection string for the linked table?

d
Go to the top of the page
 
BananaRepublic
post Jul 12 2015, 09:27 AM
Post#9


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


PeterK,

QUOTE
Since I added the random number as the pk, displaying the view in Access lists all the rows as deleted - it does not crash immediately. If I refresh the view then Access crashes.


Well, random is *random*. Access is likely expecting the primary key to be stable, especially between calls. A random function would be called again and thus generate entire new row which makes it hard to work with.

Earlier, David asked if there is a PK and you said yes, there was but I didn't see whether you already tried using that PK as the unique key in Access? If it's not itself unique in the view, then concatenate it with the other PK from other table so that the column as a whole is unique and thus Access can use it.
Go to the top of the page
 
PeterK
post Jul 13 2015, 02:18 AM
Post#10



Posts: 957
Joined: 31-December 06



Thanks for your further suggestions.

Here is the MySQL view dictionary list:

Attached File  ua13072105a.png ( 15.09K )Number of downloads: 12


Here is the access table structure:

Attached File  ua13072015b.png ( 14.87K )Number of downloads: 10


Here is the connection string for the pass through query which also crashed:

ODBC;Driver={MySQL ODBC 5.1 Driver};Server=99.99.99.999;Database=MFW_200;UID=root;PWD=*********;FOUND_ROWS=1
;DYNAMIC_CURSOR=1;MULTI_STATEMENTS=1;NO_PROMPT=1


Here is the connection string for the view using the DSN in the test DB:

Attached File  ua1307c.png ( 6.17K )Number of downloads: 0



I am using a DSN for the test db although the production version is DSNless. The only additonal parameter checked in the DSN is Return matched rows instead of affected rows .

No, I've not tried 5.3 - is it stable ?

I'm using the 'default' MySQL ODBC settings apart from the one mentioned above - how do I tell if this is ANSI or Unicode ?

I hope this answers all points except Banana's last suggestion, which I'm currently looking at.
Go to the top of the page
 
cheekybuddha
post Jul 13 2015, 08:46 AM
Post#11


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


>> No, I've not tried 5.3 - is it stable ? <<
MySQL consider it 'GA', but MyODBC has always had quirks in my experience!

>> how do I tell if this is ANSI or Unicode ? <<
With 5.3 you get a choice of driver when creating the DSN. Perhaps this isn't available in 5.1
My test DSN looks like this:
CODE
[ODBC]
DRIVER=MySQL ODBC 5.3 Unicode Driver
UID=abc
DFLT_BIGINT_BIND_STR=1
COLUMN_SIZE_S32=1
PORT=3306
SERVER=MySQL.xyx.com


You have MULTI_STATEMENTS turned on in your DSN - do you use it? I think it may have caused problems for me in the past (long ago with 3.51)

Again, no real answers. frown.gif

Maybe it might be an option to create a table specifically for this view, and use triggers on the view's source tables to update it (horrible solution, but at least you can specify a proper PK.) shrug.gif

Or last suggestion - try:
CODE
select CONCAT('1-',`tblStockTransfers`.`TransferID`, '-', `tblStockMoves`.`StockTranID`) AS DummyID,
       `tblStockTransfers`.`TransferID` AS `TransferID`,
       `tblStockMoves`.`StockTranID`    AS `StockTranID`,
       `tblStockmast2`.`Description`    AS `Description`,
       'Transfers Out'                  AS `Type`,
       `tblStockMoves`.`Cases`          AS `Cases`,
       `tblStockMoves`.`Weight`         AS `Weight`
from   (((`tblStockTransfers`
          join `tblStockMoves`
            on(( `tblStockTransfers`.`TransferID` =
                 `tblStockMoves`.`StockTransferID` )))
         join `tblStockTrans`
           on(( `tblStockMoves`.`StockTranID` =
        `tblStockTrans`.`StockTranID` )))
        join `tblStockmast2`
          on(( convert(`tblStockTrans`.`ProductCode` using utf8) =
                    `tblStockmast2`.`StockCode_Sage` )))
union all
select  CONCAT('2-',`tblStockTransfers`.`TransferID`, '-', `tblStockTrans`.`StockTranID`),
       `tblStockTransfers`.`TransferID` AS `TransferID`,
       `tblStockTrans`.`StockTranID`    AS `StockTranID`,
       `tblStockmast2`.`Description`    AS `Description`,
       'Transfers In'                   AS `Type`,
       `tblStockTrans`.`CaseCount`      AS `Cases`,
       `tblStockTrans`.`Weight`         AS `Weight`
from   ((`tblStockTransfers`
         join `tblStockTrans`
           on(( `tblStockTransfers`.`TransferID` =
              `tblStockTrans`.`TransferID` )))
        join `tblStockmast2`
          on(( convert(`tblStockTrans`.`ProductCode` using utf8) =
                    `tblStockmast2`.`StockCode_Sage` )))

It will create a text based ID, but should be unique - perhaps Access will work with that?

hth,

d
Go to the top of the page
 
cheekybuddha
post Jul 13 2015, 08:48 AM
Post#12


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


Just saw my last suggestion is pretty much what Banana was suggesting - I just thought without some other differentiation factor there was still the possibility for key collision, hence the slightly unwieldy text concatenation.

thumbup.gif

d
Go to the top of the page
 
BananaRepublic
post Jul 13 2015, 09:09 AM
Post#13


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


Just a FYI RE: MULTI_STATEMENTS - I use it all time and find that this is better overall because it allows for more functionality such as getting correct results of SHOW ERRORS even in a separate query call.

Also, FOUND_ROWS is essential for Access to work - without it, you can run into #Deleted problems much more frequently (it won't eliminate as there are several different causes/reasons you may encounter a #Deleted, but FOUND_ROWS does eliminate some of them).

Other options are pretty much optional.
Go to the top of the page
 
cheekybuddha
post Jul 13 2015, 09:21 AM
Post#14


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


BR, good to know MULTI_STATEMENTS are working reliably now! thumbup.gif

Which driver do you use?

d
Go to the top of the page
 
BananaRepublic
post Jul 13 2015, 09:23 AM
Post#15


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


Back then, I used 5.1 but keep in mind I don't have a current MySQL project so I cannot speak to whether 5.3 is an improvement or not.
Go to the top of the page
 
cheekybuddha
post Jul 13 2015, 09:35 AM
Post#16


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


thanks.gif
Go to the top of the page
 
PeterK
post Jul 13 2015, 03:21 PM
Post#17



Posts: 957
Joined: 31-December 06



Thanks for these latest comments.

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

Attached File  ua13072015a.png ( 44.27K )Number of downloads: 0


I split the two parts of the union query apart and found that the individual select queries caused Access to crash. I re-wrote the query to include LEFT joins..

CODE
SELECT tblStockTransfers.TransferID,
       tblStockTrans.StockTranID,
       tblStockmast2.Description,
       "Transfer Out" AS Type,
       tblStockMoves.Cases,
       tblStockMoves.Weight
FROM   (tblStockMoves
        RIGHT JOIN tblStockTransfers
                ON tblStockMoves.StockTransferID = tblStockTransfers.TransferID)
       LEFT JOIN (tblStockTrans
                  LEFT JOIN tblStockmast2
                         ON tblStockTrans.ProductCode =
                            tblStockmast2.StockCode_Sage)
              ON tblStockMoves.StockTranID = tblStockTrans.StockTranID
UNION ALL
SELECT tblStockTransfers.TransferID,
       tblStockTrans.StockTranID,
       tblStockmast2.Description,
       "Transfer In" AS Type,
       tblStockTrans.CaseCount,
       tblStockTrans.Weight
FROM   (tblStockTransfers
        LEFT JOIN tblStockTrans
               ON tblStockTransfers.TransferID = tblStockTrans.TransferID)
       LEFT JOIN tblStockmast2
              ON tblStockTrans.ProductCode = tblStockmast2.StockCode_Sage
ORDER  BY tblStockTransfers.transferid DESC,
          Type;


This code runs ok in Access and MySQL will create a view. However linking to this view in Access causes the same problems.

The Access query runs fast so a view is not necessary. But what is interesting is that this is first time I've ever encountered a view that runs in MySQL but not in Access. I always thought that you could get away with queries/views without unique keys as long as no updating was involved. It seems I was wrong.

One way to create a unique key would be to introduce a transfer line number or enforce the uniqueness of TransferID + StockTranID on Transfers Out ( it will always be unique for Transfers In) but both these changes will affect a number of transactions and as this query is only required for reporting I'm tempted to leave things as they are.

Thanks once again for everyone's help.
Go to the top of the page
 
BananaRepublic
post Jul 13 2015, 03:34 PM
Post#18


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


Peter,

Just so you know, I also don't expect Access to crash just because you used one crazy query. I'm more liable to blame the driver for somehow mismangling the metadata or something like that.

The other thing is that I should have realized you didn't expect to update any data on the view -- so the primary key discussion is actually kind of moot since it should be non-updatable. The only purpose for having a primary key is to enable Access to update data exposed via a view.

And just as a curiosity - do you get any warnings if you run the view in MySQL client?

But given that fails as a view, what if it was a stored procedure instead? This means you have to use a passthrough query to execute the stored procedure but it might be better at encapsulating the contents than a view?
Go to the top of the page
 
cheekybuddha
post Jul 13 2015, 03:55 PM
Post#19


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


How do you get identical TransferID and StockTranID when both are 'Transfer Out'?

Does tblStockMoves have another field as PK that isn't included, or is it because of the second join to tblStockmast2?

If the former, then try adding the PK. Otherwise, try joining the tables first and then doing a distinct join to tblStockmast2.

Although you should only need a unique field for updating I think Access can have issues when there isn't one even for read-only tables/views.

BR's suggestion of a stored procedure is probably with investigating too. If you only need the data for reporting then you can probably just create a table and populate it, then drop it straight afterwards.

d
Go to the top of the page
 
BananaRepublic
post Jul 13 2015, 04:39 PM
Post#20


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


A bit tangent but in regards to specifically the part where we need to generate a unique identifier as part of view, this might be helpful.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 06:26 PM