MS Access FE SQL Server BE Save record from form

Need to save a record to SQL Server table from an Access form.  This form is holding multiple records based a product number which has multiple child records:


This form holds multple subforms.  Once I have updated the records shown on this form/subform I need to save back to SQL Server table.
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.

ste5anSenior DeveloperCommented:
Just create linked tables using the Link Wizard.

Then it works like a normal table.

p.s. your given number sample looks like a violation of 1NF. Maybe you have some data model issues.
Forms don't store data.  Tables store data.

Why are you not using bound forms?  Access is a RAD tool and one of the biggest time savers is bound forms.  In fact, if you are not using bound forms, you probably shouldn't even be using Access.  You are having to deal with all the baggage without getting any of the benefit.
deer777Author Commented:
I am using SQL Server table as linked BE table
Get Blueprints for Increased Customer Retention

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.

Jeffrey CoachmanMIS LiasonCommented:
This form holds multple subforms.
Not sure how you got to the point of having multiple subforms that don't work...

So, something seems to still be lacking in your explanation...

You never stated what you were actually trying or what the results were...
(or if the tables were actually set up and related properly, ..or how you set up your forms, ...etc)

So let's keep this simple.
Are the two tables (The main table and a child table) linked in SQL Server?
If not, them.
If so, ...then...
Create a main form based on the main (Linked SQL serve)r table.
Now, this form.
If you edit a record in the form (then close the form), the data updated in the table? Yes or No?

If no, ...then stop and tell us the specific of your environment.
If yes, ...then now create a continuous form based a linked "Child" table.  
Then open this form (on its own) and edit a record, ...
Close the continuous form.
In SQL Server are these records updated? Yes or no?
If no, ..then again, ...stop and tell us what you have.

If yes, ...then (in Access) open the main form in design view,
Drag the continuous (sub) form directly from the Navigation Pane into the main form.
(if you linked the tables in SQL Server, ...then these tables should now be linked in the access forms)

Now with a main/subform setup,....
Open the main form, should be able to update data in both the main form and the subform, and see the changes reflected in SQL Server tables.

Let us know...

deer777Author Commented:
Could it be that I don't have permissions to update the table?
In SSMS, open the table in edit view and try to change it.  If you can, it is not a permissions issue.

You've asked several questions about using an Access FE to do this work and I'm trying to figure out why you are even using Access if you are not using bound forms.  Bound forms are essentially a "no code" solution to do what you are asking.  In reality, there is always code but it is validation code to ensure that the data is present and valid but the mechanics of retrieving and saving and adding and deleting is handled transparently by the form.
Jeffrey CoachmanMIS LiasonCommented:
Could it be that I don't have permissions to update the table?
Perhaps, ...but it sounds more like a design issue.

Please respond to Pat as to why you are not using Bound forms.

What I posted is a test to see if you can create bound forms, and verify that they are working.

Again, ...not quite sure how you got to "multiple subforms", ...with a design that is not doing what you want...
If one subform is not working, ...then why add any others?

deer777Author Commented:
I am using bound forms.  Initially I was able to update the bound forms.  Now they are not saving the updates to the fields.  

i updated one record in SSMS and it retained the changes and implemented the trigger updating the update field in the record with todays date.
Now try to open the linked table in Access.  Can you update it?  Is the form bound to a query?  open the query, can you update it?

Access assumes linked tables are updateable (Except for Excel).  There are a couple of things that will override that.
1. Permissions - which we have eliminated.
2. No primary key or unique index - check this
3. The table contains a bigInt PK and you are using the default ODBC driver which does not support the BigInt data type.

Get back to bound forms as soon as we figure out what caused the problem.
deer777Author Commented:
I have two primary keys (composite)

Don't understand 3.
Did you try opening a linked table and updating it?   If the form is bound to a query, did you try opening it and updating it?

BigInt is a data type and the old, default ODBC driver for Access does not recognize it.  Having a BigInt PK will prevent Access from being able to update the table.
deer777Author Commented:
The linked table updated fine.  The form is not bound to a query. . .it is bound directly to the linked table.
Forms should NEVER be bound directly to tables, especially when the BE is SQL Server.  When you bind a form to a table, you are asking Access to retrieve every, single record from the server and bring it into memory on the local PC.  This is completely unnecessary and defeats one of the important reasons for using a "real" RDBMS and that is to get the server to do the heavy lifting.  Once you go into production and the tables grow, the network traffic will increase and if the DBA analyzes it, he will see your Access app sucking data with a straw from the server.  Forms should be bound to queries and the queries should use criteria that selects a specific record or a small number of records.  No user can work with thousands of records at a time on an update form.

If you can update the table data by opening the linked table, the form should also be able to update the linked table.  If it is not, you will have to look to the code behind the form to see how you are preventing this normal operation.  Make sure that AllowEdits on the Data tab of the form's PropertySheet is set to Yes.

Also keep in mind that if a control's ControlSource property starts with the equal sign, the control is NOT bound and therefore will never update the table.

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
deer777Author Commented:
BIGInt has been corrected throughout SQL Server objects.

AllowEdits is set to YES

Form is filtered to only pull in specific records.

Thanks much for all  your help in this matter!
You're welcome.
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

From novice to tech pro — start learning today.