Primary key missing in the secondary table in sql  server 2016

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
The primary key is not being inserted in the secondary table while other data is being inserted in SQL server 2016 , the strange thing is that when tabbing or using tab the data from the child form will go or disappear leaving the data from the main form . I'm using the same tables , same form , same queries as in ms access , I tried to recreate the form still nothing is working out , I have just re exported the tables again to sql server still nothing is being sorted out.

I have not recreated the relationship in sql server since the export went with all the links except the date which I had to normalise to datetime

What should I do now????????????


Regards

Chris
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If you are inserting records into a primary-table whose primary-key is auto-generated (identity-column) then you need additional steps in your application to get that primary-key so you may use it in secondary-tables.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Was this in an Access subform previously and you are now using something else?  If so, you have to remember that in access, when you link a subform to a parent, the linking fields will automatically populate with the. Alues from the parent form, this is probably not the case in your current construct, so you need tomanually set the value when you write records in your child table.
This is the same application working very well in ms access all what has changed is the movement of the same tables into sql server but rest remain in ms access, that is why it is confusing me !
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

The solution here is to re-link the the child tables to the parent tables in sql server and then recreate the subforms to bind the new link , what is happening is that when you export the tables from ms access 2016 using ssma and runtime 2013 it loses the the entity relation links and the forms connections that where the problem is.

This has taken me about 10 hours to figure it out , but it has paid!

now let me go and sleep

Regards

Chris
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Actually, the relationships in the Access mean nothing once you have migrated your tables to SQL Server, although they will help you to create queries as those joins will be automatic.

And as long as the tables names of the linked SQL tables are the same as the table names of the original Access tables, you should not even have to reset the child/master relationships on your forms, but if your linked SQL tables have a prefix (usually the schema name dbo_) then you would have to redo those links as well.

Glad you were able to get it working.
Thank you so much sir

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial