Form no longer allows add/edit after converting Access db to SQL Server db.

I have a form that I used to be able to add/edit records on.  Since I moved the Access tables to SQL Server and converted the Access queries to SQL Views, the form no longer allows adds/edits.

I had the Access form's Recordset  Type set to "Dynaset (Inconsistent Updates)".

The SQL looks the same on the prior Access DB and the now SQL DB.  It's a left join on two fields.

Can anyone help or send me in the right direction please?  I thought I was finished with the conversion but this issue popped up and it's likely going to affect user data entry on many forms/tables/views.
LVL 1
fabi2004CIOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

RayData AnalystCommented:
First thing that caught my eye is that you cannot write to a view.  You can only write/update tables in SQL server.  

If you are indeed attempting to write to a table .... There could also be permissions issues within SQL server for the users in question.  You not only have to add the users, but set the privileges a couple levels deep sometimes to be safe.
0

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
fabi2004CIOAuthor Commented:
Hi Ray, thanks for responding.  I am trying too write to a table and it's a trusted connection (Windows Authentication) between Access and SQL Server.  I still  have it on a test environment, so even if I log in using SA, I still have the same problem.

The form is based on a join between a table and a view.  The data to be added/edited would be on the table side of things.
0
fabi2004CIOAuthor Commented:
I'm going to close this because I reverted back to the Access query.  The table stayed on SQL Server but the view didn't work for my purposes.

Ray, thanks for responding.  I always appreciate all the experts here.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Other thing: make sure you have a timestamp field in the SQL Table.   This turns on row versioning in SQL.   JET will use that field to help with keyset operations.

make sure you refresh the table after adding it.

Jim.
0
Dale FyeOwner, Developing Solutions LLCCommented:
And you probably need to include that timestamp (row version) field in the view as well.
0
Mark EdwardsChief Technology OfficerCommented:
By default, you can't edit a view because linked views have no local unique index, which makes them non-updateable.
You can put a key index on a linked view with code like this:

Dim strSQL as string
strSQL = "CREATE UNIQUE INDEX UniqueIndexNameHere" _
    & vbCrLf & "ON LinkedViewNameHere" _
    & vbCrLf & "(Field1NameHere ASC, [Field2 NameHere] ASC)"
    CurrentDBNameHere.Execute strSQL

Then, technically, the view is updateable.
0
Mark EdwardsChief Technology OfficerCommented:
p.s.  Same goes for linked tables.  By default, if a table has a unique index on SQL Server, then linking it will automatically add a unique index on the Access link, so the table is updatable.  However, if there is NO unique index on the table and you link it to Access, the table won't be updateable either, unless you link it using the linking wizard and it pops-up a list of fields from which to choose your unique index and you create one that way.

Try it.... you'll like it.... :-)
0
Mark EdwardsChief Technology OfficerCommented:
p.s.s  The timestamp field on liked tables is a good idea too, just on general principals.
I'm surprised no one else has mentioned putting an index on views to make them "updateable".  Is this some ones' taboo practice?
Where are the experts....
0
fabi2004CIOAuthor Commented:
Lots of great comments and suggestions.  Thank you all so much!  I do have a timestamp field in the table and in the view.


Mark, I'd read online about adding an index to the view, but I didn't understand that it would make the view updateable.  Thank you.
0
Dale FyeOwner, Developing Solutions LLCCommented:
Well, simply adding an index to a view will not necessarily make it updateable, there are other conditions which would prevent it from being updateable as well, but not having a unique index is one of them.
0
fabi2004CIOAuthor Commented:
Thanks Dale.  I'm still playing around with it to see what else I can do.
0
Mark EdwardsChief Technology OfficerCommented:
As Dale indicated, the view has to be a type of query design that would be updatable if it were an Access query, and must also be a view that is updatable in SQL Server.  No amount of unique index on a linked view is going to make it updatable if it wouldn't be updatable as an Access query or SQL Server view updated in SQL Server.

I figured that would already be apparent, but sometimes you just have to spell it out.....
0
fabi2004CIOAuthor Commented:
Thanks again Mark.  I'm going to go through and check all the primary keys on the tables, add timestamp fields if they're missing and create unique indexes on the views.

I'm really not looking to redesign this whole thing, I'd hoped the migration to SQL Server would be straightforward.  Maybe just working out a few more kinks will get it there.

Thanks again!
0
RayData AnalystCommented:
Likely a silly comment, but wouldn't it be considerably easier just to rewrite the query supplying data to the 'form' to not use views?
-from an admittedly SQL Query person, not Access person
0
fabi2004CIOAuthor Commented:
Ray,  I don't want to say no, but ... probably not.  This is an Access database with tables and queries migrated to SQL Server for performance enhancement.  The Access queries all became SQL Views.  38 of them, which are in turn linked back to other tables for forms and reports.  Really hate the thought of rewriting all of that. It would be a spider web of code.  I don't know, maybe I'm way off base.  I don't have a deep skill level with this.
0
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.