 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Multiuser / Networking / September 2003View Recordset Not Updatable
I created a view and linked my front-end Access 2000 application to it, and used the linked view as the recordset for a form. When I noticed that the recordset could not be updated, I found in the help file that a view cannot be updatable if it uses aggregate functions. I do not need any aggregate functions in this view, but it keeps inserting TOP 100 PERCENT after the SELECT. I delete it, but every time I save the view, it automatically reinserts this function. How do I get rid of it?? I think I see the problem. I wanted the view to sort the records, so I included an Order By statement. But you cannot use an Order By statement in a view? > I created a view and linked my front-end Access 2000 application to it, and > used the linked view as the recordset for a form. When I noticed that the [quoted text clipped - 4 lines] > TOP 100 PERCENT after the SELECT. I delete it, but every time I save the > view, it automatically reinserts this function. How do I get rid of it?? Created a view in _what_, MS SQL Server, Access, ??? Could you have used a Query (is your Front End/Client/Application an MDB with JET and ODBC or is it an ADP with ADODB?). The rules for updateable Views will differ depending on the server DB. Please clarify here in the newsgroup, not by e-mail. Larry Linson Microsoft Access MVP > I think I see the problem. I wanted the view to sort the records, so I > included an Order By statement. But you cannot use an Order By statement in [quoted text clipped - 9 lines] > > TOP 100 PERCENT after the SELECT. I delete it, but every time I save the > > view, it automatically reinserts this function. How do I get rid of it?? The view was created using Enterprise Manager in SQL Server 2000. The front end is Access 2000 MDB. I created a link to the view using ODBC. Is it true that you cannot use the "Order By" statement in a view? How do you sort the records? > Created a view in _what_, MS SQL Server, Access, ??? Could you have used a > Query (is your Front End/Client/Application an MDB with JET and ODBC or is [quoted text clipped - 22 lines] > > > TOP 100 PERCENT after the SELECT. I delete it, but every time I save the > > > view, it automatically reinserts this function. How do I get rid of it?? I can't say about Order By in views in MS SQL Server, but perhaps someone who can, will step in here and answer. (That's part of the power of newsgroups, that your question is exposed to people with a variety of experience and skill.) The views I created and used were in Informix and were the basis for queries that would be the RecordSource of Reports, and, thus, ordering the query data was not an issue -- Report Sorting and Grouping overrides the order of the query data. But, I know that some of my colleagues have used the same approach with SQL Server. I've never experienced a need for so many joins that I needed to create a view _except_ for Reports. Often, that many joins would result in an un-updateable Query so it would not be suitable for Forms, other than Forms for viewing only. If you use the View as the data source for a Query, as I did, you could do the Order By in the Query, if you need ordering. Larry Linson Microsoft Access MVP > The view was created using Enterprise Manager in SQL Server 2000. The front > end is Access 2000 MDB. I created a link to the view using ODBC. [quoted text clipped - 31 lines] > > > > view, it automatically reinserts this function. How do I get rid of > it?? If you meant a Pass-Through Query (in Access) that calls the View then the result (i.e. Datasheet in GUI or Recordset in code) of a Pass-Through Query is NEVER updateable. The problem is not the ORDER By clause, it is the PTQ. -- HTH Van T. Dinh MVP (Access) > The view was created using Enterprise Manager in SQL Server 2000. The front > end is Access 2000 MDB. I created a link to the view using ODBC. > > Is it true that you cannot use the "Order By" statement in a view? How do > you sort the records? You don't, of course, have to use a Pass-Through Query to access a View. Access "sees" the View just as though it were a server Table. > If you meant a Pass-Through Query (in Access) that calls the View then the > result (i.e. Datasheet in GUI or Recordset in code) of a Pass-Through Query [quoted text clipped - 13 lines] > > Is it true that you cannot use the "Order By" statement in a view? How do > > you sort the records? Thanks, Larry. I read previous posts and somehow I thought Larry (S.) was calling the View through a PTQ. Also, I used ORDER BY in MS-SQL Server without any problem. -- HTH Van T. Dinh MVP (Access) > You don't, of course, have to use a Pass-Through Query to access a View. > Access "sees" the View just as though it were a server Table. Larry, You are right, the top 100% is being added because you have an order by. If you try to create that view in Query Analyzer and add an order by you would get an error message. Anyway, a view cannot have an order by clause unless it has a top clause When you link to an ODBC table Access will try to determine the key columns - it will look for a clustered index then a unique index. if it finds neither, it will prompt you to select the key columns, and create a pseudo index in access. So, if you find the recordset is not updateable even after removing the top clause, delete the link to the query and add it again, to get the prompt for the pseudo index. HS > I think I see the problem. I wanted the view to sort the records, so I > included an Order By statement. But you cannot use an Order By statement in [quoted text clipped - 9 lines] > > TOP 100 PERCENT after the SELECT. I delete it, but every time I save the > > view, it automatically reinserts this function. How do I get rid of it?? Assuming you are using the enterprise manager? Right click in the blank diagram area, or select the 2nd icon from the left (next to the save, hit the properties icon). It is the properties sheet for the query builder. Un-check the "top" box (that is why the top 100 is being placed in the query when you remove it in the sql text box). You also note that since you are using the Enterprise manager to create this view, it has little, or nothing to do with ms-access. No doubt asking this question in one of the sql server newsgroups would be better. Since I do use the Enterprise manager....I was able to answer your question, and have seen this behaviour. -- Albert D. Kallal (MVP) Edmonton, Alberta Canada kallal@msn.com http://www.attcanada.net/~kallal.msn |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.