Hi all,
Summary: The problem is the sort order in a view created from within Access 2003 to a SQL 2000 backend database does not sort properly on a SQL 2005 backend database.
Detail: I have an Access 2003 app that uses a connection to a SQL 2000 backend. Some views were made with the query designer in Access 2003. As you know in the designer you choose the fields you want to show, choose a sort order, it creates the SQL code, and you save the query. Since the app is a project with a connection to a database, it saves the query as a "View". One of the forms in the app has a combo box with a list of view names. When you choose a view name, it parses it into a variable and runs the command "DoCmd.OpenView <viewname>, acViewNormal, acReadOnly". This opens the view in the pre-sorted order that the view was saved with. It works OK and when you're done with the view you just close the window. Now I'm testing this all on a SQL 2005 backend. The problem is the sort order that the view was saved with is not working. I've been reading that views in SQL 2005 don't honor the ORDER BY clause like SQL 2000 did. I've tried deleting and recreating the view in SQL 2005 Studio Express and in Access 2007, but the sort never stays. All that's available are the designers in Studio Express and Access 2007. You choose the table, choose the fields you want to show, and choose a sort order, it creates the SQL code, and you save the query (view). Even if you open the view here it's not sorted right. But if you run the view while in the designer, it sorts OK. What's needed is a saved view that shows the records sorted properly when you open it. How can you create a view that will display the sorted records properly when opening the view?
Thanks much,
Tom.
Summary: The problem is the sort order in a view created from within Access 2003 to a SQL 2000 backend database does not sort properly on a SQL 2005 backend database.
Detail: I have an Access 2003 app that uses a connection to a SQL 2000 backend. Some views were made with the query designer in Access 2003. As you know in the designer you choose the fields you want to show, choose a sort order, it creates the SQL code, and you save the query. Since the app is a project with a connection to a database, it saves the query as a "View". One of the forms in the app has a combo box with a list of view names. When you choose a view name, it parses it into a variable and runs the command "DoCmd.OpenView <viewname>, acViewNormal, acReadOnly". This opens the view in the pre-sorted order that the view was saved with. It works OK and when you're done with the view you just close the window. Now I'm testing this all on a SQL 2005 backend. The problem is the sort order that the view was saved with is not working. I've been reading that views in SQL 2005 don't honor the ORDER BY clause like SQL 2000 did. I've tried deleting and recreating the view in SQL 2005 Studio Express and in Access 2007, but the sort never stays. All that's available are the designers in Studio Express and Access 2007. You choose the table, choose the fields you want to show, and choose a sort order, it creates the SQL code, and you save the query (view). Even if you open the view here it's not sorted right. But if you run the view while in the designer, it sorts OK. What's needed is a saved view that shows the records sorted properly when you open it. How can you create a view that will display the sorted records properly when opening the view?
Thanks much,
Tom.
