Results 1 to 8 of 8
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Non Update View in Project (WHY) (Access 2000 with SQL Server 2005)

    I have a view in SQL server 2005 and an Access 2000 Project

    The Syntax of the SQL is as below


    SELECT TOP (100) PERCENT cbd_TransNo, cbd_CoreID, cbd_BranchID, cbd_HOID, cbd_Places, cbd_DateRequested, cbd_CreatedBy, cbd_Year, cbdYearNo,
    cbd_EditedBy, cbd_LastUpdated, dbo.GetBranch(cbd_BranchID) AS Branch, dbo.GetBranchPriority(cbd_BranchID) AS bra_PriorityCurrent
    FROM dbo.CORE_BID_REQUESTS

    where GetBranch and GetBranchPriority are Scalar Functions and CORE_BID_REQUESTS is a Table

    IN SQL the View is Updateable BUT in the Access Project it isn't

    Any Ideas ? Is it the functions, Is it the SQL 2005 OR is it just a Dear Bill thing.

    Worst comes to the worst I'll write the functions in VB, they are only used as a lookup anyway on a form.

    Thanks
    Andrew

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Non Update View in Project (WHY) (Access 2000 with SQL Server 2005)

    I don't use adp's so I cannot really help, but what is the purpose of the SELECT TOP (100) PERCENT ?

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Non Update View in Project (WHY) (Access 2000

    It is the new recommended way to write the queries in SQL 2005 (according to Microsoft)
    It is the same as just using SELECT and not including it at all BUT Microsoft adds it anyway.
    They say it is better to include it, and if you miss it off it gets added back when you try to modify the view in the query designer.

    It could also be associated with the fact that Access does not like SQL 2005 that much.
    Even with all the so called patches installed it still does not permit direct design changes to the queries.
    This has to be done through the SQL management suite.

    I'm going to also check and see if Access 2007 is any better.



    I also tried the query without this and it was still read only.

    Strangely I have other views that use custom functions and they appear to be updateable.

    Maybe it is just a glitch.

    I will try it again but start the query from scratch and also try it as a proc.
    Andrew

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Non Update View in Project (WHY) (Access 2000

    As you can see from my previous reply, I'm very out of my depth here. I'll leave this to others.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Non Update View in Project (WHY) (Access 2000

    Any top values query in Access is not updateable, so including that in the SQL will automatically make the view non-updateable. However, SQL views are not updateable from Access unless they include a datestamp field, if I remember correctly, or at least not if they contain a memo field. <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15>
    Charlotte

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Non Update View in Project (WHY) (Access 2000

    A top values query in itself can be updateable.

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Non Update View in Project (WHY) (Access 2000

    It's a Bill thing!
    Andrew

  8. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Non Update View in Project (WHY) (Access 2000 with SQL Server 2005)

    RESOLVED!

    I must now apologise to ALL who wasted time on this ( inc Bill)

    All the queries work just fine and can be updated.
    At least they would have done had I made the Identity field in the table a Primary key.
    There MUST be a primary key for Access to allow updates (Even in a project)
    SQL Server does NOT require this.

    Sorry folks!

    I normally always set the primary key when the table is made BUT I was obviously in a rush
    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •