Access ADP Form Sort on ComboBox Text

Here is BenchmarkCategory table. Notice the BenchmarkCategoryName is not sorted alphabetically when sorted by BenchmarkCategoryID.
 
BenchmarkCategory Table  

Here is another table Benchmark with a BenchmarkCategoryID column. I built a form on the table and added a ComboBox displaying the BenchmarkCategoryName. Because users would like to see Names instead of IDs.

Benchmark Table Sorted on ID  

My question is how to sort the ComboBox column based on the names, not on IDs? So it looks like the below picture, not the above picture?

Benchmark Table Sorted on Name  

I did the above picture by changing the RecordSource on the Benchmark table to

select * from Benchmark c order by (select p.BenchmarkCategoryName from BenchmarkCategory p where p.BenchmarkCategoryID = c.BenchmarkCategoryID) desc

But this hack doesn't work in SubForms. Using Profiler, I saw ADP sending broken queries to SQL Server.
bo_dongAsked:
Who is Participating?
 
BitsqueezerCommented:
Hi,

in my current project I had a database which started on Access 2007 ACCDB and SQL Server 2000. I worked with it for nearly a year and the limits and problems you get with accdb for me are too big to ever use ACCDB with SQL Server again. For example, because JET is always in between it causes performance problems, conversion problems (you need to add a timestamp column to any table which should be edited with Access), you cannot use all datatypes of SQL Server, only those which can be converted to similar types of JET and so on. I moved this application to ADP and SQL Server 2005 with big success and work on it since the last years.

The article Nicobo linked above tells things about ADPs which are simply not true. Yes, you can use JET to link tables between different sources, that's a thing you can't do with ADPs, here you can open tables with SQL Server or use ADO to link to other sources (but you can simply create a linked server on SQL Server and have the same functionality at a better performance). Yes, you can use local tables - but in most cases you don't want them, not even for lookup tables. Because if you need to add a value to a lookup table you must rollout a new frontend. Or you must use a logic to download values from a server table and so on.
"contains many new features that are available in both MDB and ACCDB file formats, but only a subset of those features are available in ADPs" - but what are these features? Layouts? You can use them in ADPs, too. The sorting on display values on comboboxes? Yes, that's an advantage, but also an automatism. What if you really want to sort on the underlying IDs? - all database frontend programmers must live with the problem to do some things manually but there are solutions, not "nice" for the programmer, but working and fast. ACCDB can use Pass-Through-Queries...wow...but they are not updateable....but MS doesn't tell you in this article.
"Jet optimizes the local queries to send as much of the query to the SQL Server as possible to minimize client-side query processing"
That's a big lough. If you ever used SQL Server Profiler to see what "optimized" query JET sends to SQL Server then you know what I mean. It produces mainly dynamic SQL using some MS stored procedures with a lot of overhead which slows down the performance. Try to create a continous form with an optimized SQL Server view with 100,000 records and ADP and do the same with ACCDBs "optimized" JET queries.

"The ADP architecture was designed to create client-server applications. Because of this, there is a limit to the number of records that Access returns in any recordset. This limit is configurable, but you typically must build enough filtering into your application so that you do not reach the limit."

Yes, ADPs have the (great!) feature to limit the number of records (default 10,000) and let the user the option to open more or less records. You can switch that off in a form by setting the MaxRecords to 0. But where is the big advantage in ACCDB? There you can't limit the number of records, you MUST create a filter to stop downloading records. And because of the lazy loading behaviour ACCDB often blocks tables for other users if many people are looking at the same table.
If you want to use a lot of data you must ALWAYS think about how to let the user search and filter. And the Access methods are really not the best solution. Think on a search machine like Google. They find millions of result records but nobody would expect to get them all on one page. You must refine your search to get the result you want, you cannot scroll through all result records. But all people uses this kind of search, nobody asks for a download of all records like usual in Access continous forms - and in most cases the users really only want a subset of records. So this is the real job for us programmers: Present a GUI which let the user search through all data with a high performance and a quick result on the things they really searching. This is also possible with a good design in Access and you are really better placed using ADPs as you can really use the full power of SQL Server.
"Access uses OLEDB to communicate with SQL Server. To provide the Jet-like cursor behavior desired for desktop applications, Access implements the Client Data Manager (CDM) as an additional layer between Access and OLEDB.
Because of the layers required to get from Access to SQL Server in the ADP architecture, it is often easier to optimize MDB/ACCDB file solutions."
The next big lough. And what is JET? It is not only a layer, it crunshes all of the data to adapt data types and SQL commands...

"However, there are some scenarios where a report might be generated significantly faster in an ADP file. To add these performance improvements and retain the flexibility of SQL Server, you can build the majority of the application in an MDB or ACCDB file and have the file load reports from a referenced ADP file."
Oh..MS says that there are "scenarios" where a thing in ADP is faster than in ACCDB...wow... to use the real flexibility of SQL Server exactly the other way is better: Using ADP with better performance in reports AND forms (my personal experience of the last three years) and creating an external ACCDB if you really need some local tables and open it through ADP.

"One advantage that ADP files have over files in MDB or ACCDB format is the ability to make design changes to SQL Server objects. ADP files include graphical designers for tables, views, stored procedures, functions, and database diagrams."
That's indeed exactly NO advantage of ADP. An ADP is strongly linked to SQL Server and it only "knows" about the features of any older SQL Server. You can edit objects of SQL Server 2000 in A2003 ADPs or edit SQL Server 2005 in A2007 ADPs and Sql Server 2008 in A2010 ADPs. But you can't edit objects for example from A2003 with SQL Server 2005 and not only editing of objects is limited but also you can get in trouble if you try an older ADP with a newer SQL Server. The editing of objects is really bad in Access and all developers using SQL Server has the powerful SQL Server Management Studio or Business Intelligence Studio so you simply don't need this "feature" - and you have ALL possibilities using them while ADP can only edit a subset.

"You cannot directly modify the design of Linked Tables. You must use an ADP file or Enterprise Manager included in SQL Server to make schema changes or design changes."
OK, so what's the advantage of ACCDB? But the thing is even bigger: You not only cannot edit linked tables in ACCDB, the really BIG thing is that you are forced to delete all linked tables where you changed something in the design and relink them again! Because views are handled like tables, the same for views! It is a big problem because if you are in design phase you will VERY often relink tables, I made a VBA function to do this (not to mention that Access doesn't support DSNless tables with the GUI, you must do that with VBA if you want that). In ADP I simply click on the navigation bar and press F5 and I have anything I want. Oh, and did you ever try to use a stored procedure as record source for your form? Try it, you will have a lot of fun with ACCDB. In most cases you are forced to use tables or views - that's not what I think about "maximum flexibility" and "using the full power of SQL Server".

Please - don't believe anything which was written by Microsoft only because it's from Microsoft. Try it out by yourself and you see the real advantages/disadvantages. MS often write things because of marketing strategies.
My personal experience is: ADP is the real professional way of working with SQL Server and at the same time it gives you the possibility of rapid application development. You will have to live with a little bit more work as programmer to create professional results but whoever works with SQL Server should have already reached this level, the typical Access "programmer" doesn't work with SQL or SQL Server, only uses the mouse for "programming". For this people ACCDB was made and if you can live with the disadvantages, why not. This is the target group to sell Access, MS wants to sell .NET and Visual Studio for professional programmers so they are no longer interested in ADPs - but they work again in Access2010. Think about that by yourself. Take a look at Access 2010, the two big new features are not a better ADP and not a .NET language instead of VBA, not even a better VBA editor. The two big new features are a very much better macro editor to give temporary "programmers" the possibility to create functions in Access where you otherwise woul need VBA (and you really must learn the programming language) and creating web services so you can upload the complete ACCDB to a web server so that you have a web application from your Access frontend - not telling you that for this you need to use macros and can throw away all your VBA - oh, but you now have a better macro editor......

You see what I mean? MS sees Access clearly as a typical Office product for every typical Office user and the old macros where too complicate and VBA is a real programming language and no Office user wants to learn programming but they all want to program a database (you can read exactly this intro in a lot of forum discussions..."I cannot program VBA but I want to create my personal SAP application with Access for my business...help me!") - so MS decided to create a better macro editor for the temporary "programmers" and give them the possibility to also create web applications with this. The professional programmers get really nothing from the new Access 2010, some nice graphic enhancements like better conditional formatting, round buttons and so on. That's all. ADP feature is best hidden like in A2007 so you must really know what it is and how to create it. All the power of programming is moved to .NET/Visual Studio where you get the full professional support from MS. And the full price. (To be fair, you can get Visual Studio Express and SQL Server Express for no cost, that's really good.)

So back to your problem it depends on your personal decision of what you want to do. If you want to be future compatible as long as possible and get really ALL possibilities of programming and database features you must use VisualStudio/.NET (the professional version, not the Express one, mainly for learning purposes). You can buy it once and it will be future compatible with no limitations. But you get more work to do as it is really no rapid development, this is the REAL world of programming - you already know that, you come from C#.
If you want to create applications with a known interface and fast application changes (because business rules changes faster than you can program it) and you can live with some limitations and workarounds, in my personal opinion there is no better solution than Access and especially ADPs for SQL Server at the moment. Try to create a frontend with Adobe Flex and Eclipse and you will run away from this buggy software in comparison to Visual Studio or Access...

There is no solution on the market which is able to fulfill the need of most comfort for users and most comfort for programmers. You can only select the best one from your personal needs, the need of your users and the business need.

Cheers,

Christian
0
 
Nico BontenbalCommented:
Try this as the RecordSource:
select Benchmark.* from  Benchmark c left join BenchmarkCategory p on p.BenchmarkCategoryID = c.BenchmarkCategoryID order by p.BenchmarkCategoryName
0
 
bo_dongAuthor Commented:
My question is how to make picture 2 look like picture 3 without changing RecordSource?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Nico BontenbalCommented:
Did you try the Sort propery of the subform. Set it to:
(select p.BenchmarkCategoryName from BenchmarkCategory p where p.BenchmarkCategoryID = c.BenchmarkCategoryID) desc
If that doesn't work I'm afraid I'm out of options. If Access send wrong queries to SQL Server I'm afraid it is a bug in Access and that might be quite hard to work around.
0
 
bo_dongAuthor Commented:
Howdo you set Sort property on the subform? Using OrderBy? Can you write SQL query in there? Using RecordSource? I already know that. Maybe RecourdSource is the only way. Thanks for trying.
0
 
Nico BontenbalCommented:
Yes sorry. The property is called OrderBy. There is also OrderbyOn and this has to be set to true. But I think you can only set the OrderByOn through VBA. I always thought this was very confusing so I always ended up setting the entire RecordSource. You'll enter the part of the SQL that you would normally enter after the "ORDER BY" clause in the SQL (without the Order By part).
0
 
BitsqueezerCommented:
Hi,

I would recommend that you do not use the Filter or OrderBy properties of Access forms in an ADP. Both properties results in getting all the data from server and then doing the filtering and ordering in the local machine. That causes big performance disadvantages and if you are in a continous form with many records (for example more than 50,000) it sometimes crashes Access completely.
You are in an ADP - so you should always use the power of SQL Server which is always faster in filtering and sorting. Try to use ServerFilter (And ServerFilterOn) in forms and ORDER BY only on the server. So create a view on the server which does the sorting (in SQL Server 2000 add "TOP 100 PERCENT" to create a view with ordering, since SQL Server 2005 you need an absolute value, like "TOP 1000000" or some similar expression). Instead, you can also use stored procedures which don't need the TOP clause, but with stored procedures you cannot use the ServerFilter property in Access forms.
You should never use any direct SELECT command like changing the RecordSource with a VBA SQL string or something like that - this produces always dynamic SQL which is not so fast like saves views/SPs on the server. (By the way: You should also never use "SELECT *", create views which only contains exactly the columns you need for your purpose, no more.)
Next thing is that you don't need such hack - simply JOIN the two tables together so the text column is added to the query result, then you can order by this column. In Access forms you can add a resync command (like "SELECT * FROM MyTable WHERE MyIDColumn=?" (here "*" is OK and needed by Access, also the question mark) so Access can synchronize the data correctly, and you can also specify the name of the table which should be updated with the form (because you have more than one table). It normally should also work if you add both columns, the ID column and the text column of the foreign table to the view to keep it updateable.

Forget the things you learned with MDB/ACCDB, in an ADP you must always think of "SQL Server rulez", forget to manipulate RecordSets with VBA, use a stored procedure on the server instead, forget to write SQL commands in VBA strings, use stored procedures with parameters or views if you don't need parameters. Switch a form between stored views of the server, if needed, don't change RecordSources or RowSources with VBA SQL strings. You have a SQL Server now, don't waste the time with letting your slow frontend do the job, let the server do the job, it's always faster.
If you need a parameter based on a control on the form, use the property "Input Parameters" of the form and set the RecordSource fix to a stored procedure.

Cheers,

Christian
0
 
bo_dongAuthor Commented:
Christian,

Thank you for the lengthy reply. I agree with your general observation to fully utilize SQL server and use Stored Procedures whenever possible. I spent 99% of my time in SQL server and C#. That’s why VBA and Access is all foreign and a step backward to me.

Back to my example, Benchmark table may have many columns with IDs, like BenchmarkCategoryID, TypeID, CountryID, MarketID… Each ID column has a table with lookup names. The form has a ComboBox displaying names for each of the ID columns. User can sort on any ID columns and expecting it sorted alphabetically, but will be surprised to see the sort is on the underline ID. My solution to this user request is to build a SQL string sorting on the selected column names and feed that to the form RecordSource. Remember this feature needs to work on any table with ID columns, not just on one table. So creating views with all look up tables joined together for each of hundreds of tables in the database is not an optimal solution.

Any better ideas?
0
 
BitsqueezerCommented:
Hi,

you're right, from this view VBA IS a step backward (if not more than one step...:-)).

The problem is that Access was mainly made to work with MDB/ACCDB and Access databases. If you create a lookup table using ACCDB and try to do the same, the sorting will be based on the displayed value and not on the underlying ID. This also works if you create a linked table to SQL Server tables and lookup comboboxes.
Unfortunately MS seem to think that ADP is not worth to develop with such comfort, so here you must do it on your own. But this is not so bad as it sounds because you are not forced to use the Access methods of filtering and sorting because they are really not stable in ADPs, I often had problems with these functions and I can only again recommend not to use them. Try opening a continous form with 100,000 records and then (while the record navigator doesn't show the complete number of records) immediately try to filter or sort data. You will get an Access crash in some cases, depending on network speed, size of table and data and so on.

You now could create a SQL string which changes the RecordSource always the sorting is changed (with an ORDER BY hack like you used above) and this would surely work but I would recommend the following way:
1. create a stored procedure which contains a parameter to select the column you want to sort (the column name as string or a column count, whatever you like).
2. enter an IF ELSE... construct to choose the right SQL string depending on the parameter into the SP.
3. create one SQL command per ORDER BY column with the right ORDER BY clause. DON'T use a SQL string to assemble and execute with dynamic SQL because in this case you will lose the performance advantage of SQL Server optimization. It looks ugly to have 20 SQL strings with only different ORDER BY clause and our programmer's heart wants to create a universal solution but in SQL Server this is really the better way.
4. disable "Allow Filter" on the Access form, this also disables the sort menu as it is in the same menu. Add other possibilities to the frontend like one combobox to select the sort column or a button on each column header to set the ordering - whatever you find more comfortable in your case.
5. choose the stored procedure as RecordSource and use the property "Input Parameters" to set the parameter for the stored procedure. The easiest way is to use a VBA function or a hidden field or (in case of the order combobox) the value of the combobox. For example, if you used a column count as parameter and an order combobox this would be:

@intColumn int=Forms!MyForm!MyCombobox

Open in new window


In the "AfterUpdate" event of the order combobox the only code you need would be "Me.Requery".

Of course, this solution allows only one column to be ordered, you cannot create nested orders with this - but that Access also doesn't allow with the standard order menu, it also can only sort on one criterion.
This will of course also disable the possiblity to use custom filters on the form for the user. You can also don't use the ServerFilter property which is much faster because it only works with views and not with stored procedures.

If you want to use custom filters AND custom ordering the only (performant) way I see would be to create 20 views for the same table with 20 different ORDER BY clauses, switch to the right view by changing the RecordSource to the wanted view and using ServerFilter property on the selected view. This creates a lot of additional views and if you ever must change anything in the view you will also have to change all the 20 (less or more depending on your sort columns) views all the time...

You must decide: Do you really want to let the user do ANYTHING on the form and you want an easy life as programmer, then use VBA to set dynamic SQL strings depending on user selection to set the RecordSource - and lose any performance advantage of SQL Server because after custom filtering/sorting you will see that scrolling through the records will get extremly slow (especially if you use additional conditional formatting, the you can drink coffee before a page is completely displayed...) or you want a maximum of performance with a good comfort and the possibility to decide by yourself which columns are allowed to filter/to sort but have a harder time with programming this solution and don't lost overview with many views and so on...

I for myself find MS solutions for filtering and sorting in ADP as not acceptable for a stable solution, crashes, very slow display, that's not what my users expect from a database. I think it's better to lose a little bit comfort but have a stable application with maximum performance.

Cheers,

Christian
0
 
bo_dongAuthor Commented:
Christian,
Thank you for the proposed solution. I have posted the same question on all Access forums and to Microsoft employees. You are the first one to understand my question. I will consider your suggestions.
Thanks.
0
 
BitsqueezerCommented:
Hi,

I know, I saw your posts and the answers in these forums...:-)
I also found a blog entry from the Microsoft Access team from 2006 where the author said that exactly this problem will be solved with Access 12. This is true, in ACCDB it works...:-)
I guess they simply forgot to also create this solution for ADPs - maybe because it's in an old file format or whatever.

Cheers,

Christian
0
 
bo_dongAuthor Commented:
So what’s the replacement for ADP if Microsoft is giving up on it? I need a front-end to SQL Server for users to edit, sort and filter on data. And I don’t want to reinvent the wheels by creating form based solutions in WebForms, WinForms, VSTO, InfoPath, WPF, SilverLight…
0
 
Nico BontenbalCommented:
When you use an accdb and attach the SQL server tables (external data, more, odbc database) you can go to design of the Benchmark table (click yes on the warning that says you can't edit it) and select the BenchmarkCategoryID field. Then click the Lookup tab at the bottom. Configure this lookup to use the BenchmarkCategory table as you would on a form. Close and save the table. Now open the Benchmark table and sort on the BenchmarkCategory column. This should give you the behavior you are looking for.


MS says you should no longer use ADP's. Search for adp on this page to find out why:
http://technet.microsoft.com/en-us/library/cc178973(office.12).aspx
0
 
bo_dongAuthor Commented:
Christian,

Thank you for the lengthy reply. Case closed.
0
 
Nico BontenbalCommented:
@Bitsqueezer: I think you should write an article with the title "adp or accdb to connect to SQL Server". It's a shame that all this useful information will only be read by the few people that read this question.
0
 
BitsqueezerCommented:
Hi Nicobo,

thanks, maybe you're right, but the problem is that all this is based on my personal experience, on the other hand there are a lot of people who swear by DAO/MDB/ACCDB technology as the best (following Microsoft, too) and I don't want to start a big discussion what's better, this or that. I for myself have made the decision to follow the ADP way as long as it exists in any new Access version because it's fast and easy, but maybe others made other experiences in their environment - I think this is not enough to write an article.

Cheers,

Christian
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.