Primary key missing in the secondary table in sql server 2016

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
LVL 3
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John_VidmarCommented:
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 LLCCommented:
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.
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
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 !
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Dev-Soln LLCCommented:
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.
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
Thank you so much sir
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.