asked on

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.

ASKER CERTIFIED SOLUTION

Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fabi2004

ASKER

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.

fabi2004

ASKER

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.

Jim Dettman (EE MVE)

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 Fye

And you probably need to include that timestamp (row version) field in the view as well.

SOLUTION

Mark Edwards

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Edwards

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 Edwards

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

fabi2004

ASKER

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 Fye

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.

fabi2004

ASKER

Thanks Dale. I'm still playing around with it to see what else I can do.

Mark Edwards

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

fabi2004

ASKER

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!

Ray

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

fabi2004

ASKER

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.