2

I moved from Access to MySQL (InnoDB)
frontend is Access and Backend is MySQL. InnoDb is used instead of MyISAM to use relation functions.

In my original Access application I had a working 1:1 relationships.You can see it here

This is how it looks in Access

For migration I used "Bullzip" for datas and this for the relations.(Ivan Cachicatari reply)

Because this code just gave me 1:N relationships, I had to manually edit the Model of the Schema. The Model is looking like this: Model of MySQL After I made the Model exactly look like my original Access relations, I Synchronized it with my Schema.

Description of the tables:
I have a StockChange(Bestandsaenderung) table.Primary Key BestandsanederungID When something is changing in Return (Rückgabe), Postions (Positionen) or Incoming goods (Wareneingang) it also gets registered in StockChange and the Primary key of StockExcahnge is automatically inserted into the tables (X-BestandsaenderungIdREF )

Code in MYSQL ( I just focus on these two tables. When one table has a working 1:1 realtion, the other ones are hopefully easy)

CREATE TABLE `tbl_lager_bestandsaenderungen` 
(   
`BestandsaenderungID` int(11) NOT NULL AUTO_INCREMENT,
`BestandsaenderungArtikelIDRef` int(11) DEFAULT '0',
`BestandsaenderungAnzahl` int(11) DEFAULT '0',
`BestandsaenderungDatum` datetime DEFAULT NULL, 
`BestandsaenderungVorzeichen` int(11) DEFAULT '1',   
`BestandsaenderungInventur` tinyint(1) DEFAULT '0',    
PRIMARY KEY (`BestandsaenderungID`),    
KEY `BestandsaenderungArtikelIDRef` (`BestandsaenderungArtikelIDRef`),   
 KEY `BestandsaenderungID` (`BestandsaenderungID`),  
 CONSTRAINT `tbl_Lager_Artikeltbl_Lager_Bestandsaenderungen` FOREIGN KEY (`BestandsaenderungArtikelIDRef`) REFERENCES `tbl_lager_artikel` (`ArtikelID`) )

CREATE TABLE `tbl_lager_wareneingang` (
`WareneingangID` int(11) NOT NULL AUTO_INCREMENT,
`WareneingangLieferantIDRef` int(11) DEFAULT '0',
`Einkaufspreis` decimal(19,4) DEFAULT '0.0000',
`WareneingangBestandsaenderungIDRef` int(11) DEFAULT '0',
PRIMARY KEY (`WareneingangID`),
UNIQUE KEY `WareneingangBestandsaenderungIDRef_UNIQUE` (`WareneingangBestandsaenderungIDRef`),
KEY `WareneingangLieferantIDRef` (`WareneingangLieferantIDRef`),
KEY `WareneingangID` (`WareneingangID`),
KEY `fk_tbl_lager_wareneingang_tbl_lager_bestandsaenderungen1_idx`(`WareneingangBestandsaenderungIDRef`),
CONSTRAINT `fk_tbl_lager_wareneingang_tbl_lager_bestandsaenderungen1` FOREIGN KEY (`WareneingangBestandsaenderungIDRef`) REFERENCES `tbl_lager_bestandsaenderungen` (`BestandsaenderungID`),
CONSTRAINT `tbl_Lager_Firmentbl_Lager_Wareneingang` FOREIGN KEY (`WareneingangLieferantIDRef`) REFERENCES `tbl_lager_firmen` (`LieferantID`)
)

For checking if everything is still okay, I again created a Model through 'Reverse Engineering' to see if the 1:1 realtions still exists, but it's a 1:N relation again.

My problem is, that in my Access form, the PrimaryKey of the stock change isn't getting inserted into the Incoming goods table ForeignKey WareneingangBestandsaenderungIDRef.

For a better explanation I made a video.

In pure Access it worked perfect. Just after migration all 1:1 relations aren't working anymore.

EDIT:

When I enter datas into into the subform (Wareneingang) , the PK of the MainForm ( Bestandsaenderung) gets inserted into WareneingangBestandsaenderungIDREf.LIke this

Additionally if I try to commit that second child record I get

"ODBC -- call failed. Duplicate entry '1' for key 'WareneingangBestandsaenderungIDRef_UNIQUE' (#1062)"

... so that shows me that the MySQL engine is enforcing the 1:1 relationship. But in my Form it still dont get entered automatically how you can see in my video.

Red_Baron
  • 140
  • 1
  • 9
  • So did you move the *back-end* from Jet/ACE (*.mdb, *.accdb) to MySQL and continue to use MS_Access as the front-end? That seems to be suggested by "in my Access form, the key of the stock change isn't getting inserted", but you also talk about "synchronized the model to the Schema" and "testing 'Reverse Engineering'" which is unusual terminology for a split Access database application. For the "Reverse Engineering" test are you talking about looking at the Relationships view in MS_Access? If so, then see [this answer](https://stackoverflow.com/a/16379532/2144390) for a possible explanation. – Gord Thompson May 27 '19 at 06:02
  • Access frontend - MySQL backend. In MySQL there are Models ( Tables and there relationships) like in Access and Schemas are the whole database structure with all datas in. When I do "reverse engeneering" all before defined foreign keys gets automatically mapped into the Model. In Access as frontend I cant directly make relations, because I have to do it in my Backend (MySQL). – Red_Baron May 27 '19 at 06:31
  • edited whole post for better understanding. – Red_Baron May 27 '19 at 07:03
  • Try adding an Access "Relationship" between the linked tables in your front-end file, like [this](https://i.stack.imgur.com/UlPkV.png). – Gord Thompson May 27 '19 at 12:00
  • Thanks for your answer, but its like just drawing lines between the tables without making any effect...unfortunatly. I am on this problem for 2 days now :( [This is how i done it](https://imgur.com/FxOpQwP) EDIT: But very strange that he is recognizing the 1:1 relation but isnt acting like a 1:1 – Red_Baron May 27 '19 at 12:08
  • At least for me, the Access Relationship lets me [add a child record via a subdatasheet](https://i.stack.imgur.com/ynsog.png) and the Primary Key from the parent table is automatically added as a Foreign Key in the child table. – Gord Thompson May 27 '19 at 12:28
  • I dont see any of thesse :O Is your backend MySQL?If yes, do you have exactly my SQL Code? I doubt about myself... Maybe its a query error..mhh.. – Red_Baron May 27 '19 at 12:34
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/193999/discussion-between-felix-and-gord-thompson). – Red_Baron May 27 '19 at 12:34
  • Issue might not have anything to do with backend setup but simply the Access GUI interface as main form/sub form may synch better with related Jet/ACE tables instead of related, linked ODBC tables. Try setting the [subform's default value to parent form's primary key](https://stackoverflow.com/a/28999646/1422451). This solution only works with forms not tables. – Parfait May 28 '19 at 21:24
  • This isnt workin becuase the parent key will be set up first, when the child form has been saved.. parent and childkey can just be created simultaneously. If not the classid is getting a NULL from the parents form – Red_Baron May 29 '19 at 07:49

0 Answers0