Link to home
Start Free TrialLog in
Avatar of deer777
deer777Flag for United States of America

asked on

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.
Avatar of ste5an
Flag of Germany image

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.
Avatar of deer777


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, 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...

Avatar of deer777


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?

Avatar of deer777


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.
Avatar of deer777


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.
Avatar of deer777


The linked table updated fine.  The form is not bound to a query. . .it is bound directly to the linked table.
Avatar of PatHartman
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of deer777


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.