I'm migrating a very old platform from a SQL Server 2000 backend and MS Access 2003 as its frontend to SQL Server 2012 backend and MS Access 2010 as the frontend. This obviously means a bit of headache, but at the moment I'm stuck at a "limitation" of the new database.

Apparently (according to this: http://jagbarcelo.blogspot.se/2006/09/identity-columns-in-view-treated-as.html) any compatibility level above 80 in SQL Server will treat identities as ordinary ints in relational views. This creates problems in my frontend as no new records are created (exists in both 2003 and 2010 version of Access).

My question is: is there any way to fix this in the database (backend) rather than changing the whole frontend?

Update: I'm still curious about this so an update from further testing..

Apparently my above situation is the result of a view containing relational tables. In a compatibility level of 80 it exposed a natural identity column (is_identity = true), but for later compatibility levels no identity was exposed.

Can this be forced in some way, by some sort of annotation perhaps?

  • Just a thought -- How about you drop and recreate the view ? Does it still have the same behaviour ? As a last resort, bcp out data from sql 2000 and create the base table & view on the new server from scratch and then bulk insert the data back. – Kin Shah May 20 '14 at 13:20
  • Can you explain exactly what is the problem? Does your insert from Access receive an error message? What is it? What does the insert statement look like? I created a table with an IDENTITY column, and a view that selected all columns, then INSERT dbo.view(all cols except identity) worked just fine... – Aaron Bertrand May 20 '14 at 13:22
  • In detail: When I open a specific form in Access, with datasource set to a view in SQL database, no new record gets created (since Access can't find any identity column in the SQL-view). With a compatibility level of 80 (only available in SQL Server 2008 and below) it works as expected. – Mackan May 20 '14 at 13:28
  • The link I provided explains the behaviour in better detail, and shows how to reproduce it. The issue is not that I can't insert to the view, but since Access can't find any identity column (SQL Server doesn't supply one in this case) it can't "auto create" new records. – Mackan May 20 '14 at 13:51
  • 1
    Did you try re-creating the view, as @Kin suggested? The view I created above does show is_identity for the identity column that comes from the base table. – Aaron Bertrand May 20 '14 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.