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

fabi2004
fabi2004 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Data Analyst
Commented:
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.

Author

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.

Author

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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
And you probably need to include that timestamp (row version) field in the view as well.
Mark EdwardsChief Technology Officer
Commented:
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.
Mark EdwardsChief Technology Officer

Commented:
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.... :-)
Mark EdwardsChief Technology Officer

Commented:
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....

Author

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.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.

Author

Commented:
Thanks Dale.  I'm still playing around with it to see what else I can do.
Mark EdwardsChief Technology Officer

Commented:
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.....

Author

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!
RayData Analyst

Commented:
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

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial