MS Access FE SQL Server BE Save record from form

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.

I am using SQL Server table as linked BE table

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, ...link them.
If so, ...then...
Create a main form based on the main (Linked SQL serve)r table.
Now, ...open this form.
If you edit a record in the form (then close the form), ...is 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, ...you 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...

JeffCoachman

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.

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?

JeffCoachman

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.

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.

The linked table updated fine.  The form is not bound to a query. . .it is bound directly to the linked table.

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.