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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Mysql 1-to-1 Updatable Query    
 
   
joolean
post Aug 1 2020, 12:24 PM
Post#1



Posts: 33
Joined: 13-July 16



I have the following two MySql tables representing a 1-to-1 link. They're linked into Access via odbc.

CODE
CREATE TABLE `p1` (
`PID` int AUTO_INCREMENT PRIMARY KEY,
`FirstName` varchar(20)
);

CREATE TABLE `p2` (
`FID` int PRIMARY KEY,
`LastName` varchar(20),
CONSTRAINT FOREIGN KEY (`FID`) REFERENCES `p1` (`PID`) ON UPDATE CASCADE ON DELETE CASCADE
);


In Access I have the following query
CODE
SELECT p1.PID, p1.FirstName, p2.FID, p2.LastName
FROM p1 INNER JOIN p2
ON p1.PID = p2.FID;


The select query works but when I try to add a new record by entering FirstName and LastName, it fails with the MySql error p2.FID cannot be null #1048.

Looking at the odbc logs, Access successfully inserts into p1(FirstName), but for p2(FID, LastName) it's inserting Null as FID which fails and then it rolls back.
Problem is Access is not retrieving the auto_inc primary key from p1 to insert into p2.

Is there something I need to or can do in Access to let it know the relationship between these two tables?
I linked the fields in Relationship manager but that didn't help.
Thanks
Go to the top of the page
 
June7
post Aug 1 2020, 12:34 PM
Post#2



Posts: 1,612
Joined: 25-January 16
From: The Great Land


I tested query with Access tables in a non-split db and it works.

Perhaps relationship needs to be defined on MySQL side. If this were an Access backend, I think relationship would have to be defined in backend, not frontend interface.

Build a form/subform arrangement. Master/Child Links properties of subform container will manage saving PK as FK.

Why are firstname and lastname in separate tables?

This post has been edited by June7: Aug 1 2020, 12:50 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
joolean
post Aug 2 2020, 02:25 AM
Post#3



Posts: 33
Joined: 13-July 16



Thanks for responding June.
Yes this works with linked Access tables, I'm just not sure why it doesn't work with linked MySql tables.
The relationship is defined on the MySql side via the foreign key from p1 to p2.

The example tables are just pseudo to represent the problem as simply as possible, not from my actual database. In reality it's a junction table.

I'm migrating a highly relational split Access database to a MySql backend and I'm surprised that so much of it still worked straight out of the box, at least on the SELECT side, but I suspect I'll run into a fair amount of issues when it comes to updating joined queries as the above demonstrates.

Weirdly as I was playing around with the p2 structure (added a separate auto_inc primary key) it actually started working in Access but after more playing, I lost the working setup and cannot get it to work again.

I could always find another way like master/child or stored procs but there's an enormous amount of work if I go down that path and I'd like to know if this 1-to-1 relationship should work or not before I abandon it.
Go to the top of the page
 
joolean
post Aug 2 2020, 08:12 AM
Post#4



Posts: 33
Joined: 13-July 16



Well for now I figured out how to insert a new record while still using my existing join queries.
CODE
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
        Cancel = True
        customSaveRecord    ' transaction save to both tables
        Me.Undo
        Me.Requery
        DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
    End If
End Sub


customSaveRecord saves to both tables within a transaction without using Access default save mechanism which fails.
It's ugly but doesn't require me to change any of my forms. Hopefully someone can still provide an answer to the first post though.
This post has been edited by joolean: Aug 2 2020, 08:13 AM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th August 2020 - 05:24 AM