Garry Robinson, Contributing Editor

In the August 2004 issue Access 2006—Have Your Say, Peter and I threw the challenge to the readers of Smart Access to come up with ideas for the next version of Access. At the start of a new year, it seems appropriate to look ahead to what the Access community wants to see in our product. I’m glad to report that the response for this was great—so great that we actually received more than 200 separate ideas about what you folks are really interested in. After sifting through all the suggestions and bundling them into appropriate categories, I have to say there’s a definite message for the Access development team at Microsoft: Get back to the basics—improve the Access we love and stop padding the edges to keep the marketing team happy. Put another way: The job’s not done yet—don’t give up on us.To illustrate how I arrived at this conclusion, have a look at the breakdown of requests shown in Table 1. If you compare the number of requests for the “Back to Basics” areas such as improvements to the Jet Engine, forms, and reports against the “New Age” requests such as .NET integration, XML, ADP, and SQL Server, you’ll find that requests for old fashioned items weigh in at 110 while the New Age requests come to a meager 15. If Microsoft’s initiatives for switching everyone over to SQL Server were finally kicking in after four years, you’d expect this ratio to be at least 50-50 by now.

Table 1. Easily categorized requests, by category. (*New technology requests.)

Category Comments

MDB Container and Jet 13

Tables 21

Queries, SQL, Query Editor 18

Forms/Controls 27

Reports 18

Jet VBA programming 13

OLAP, XML, .NET, DAP * 5

ADP, SQL Server * 10

Total 125

As well as these requests, there were a lot of other requests (another 80!) that could have been classified into either the New Age camp or the Back to Basics camp. Reading between the lines, I doubt you could actually infer that many of the requests were for New Age improvements, but I didn’t want to introduce a bias. Instead, I’ve categorized these requests into Table 2 so you can see the other things that people thought it was worthwhile to request.

Table 2. Hard-to-categorize requests.

Category Comments General issues 17

Error handling 12

VBA IDE 12

Security 8

Menus/Switchboards 5

Office compatibility 10

Runtime 5

Help systems 7

Comments on this list 5

Total 81

To give a broad understanding of your wishes, I’m including some samples from all of the request categories. I’ll describe some of my favorite requests from each of them. I encourage you to go to the Smart Access Web site and download the PDF file where I’ve categorized and listed all 220 requests (it’s available with this month’s downloads). It makes interesting reading if you’re a dedicated Access developer.

General Access issues: 17 requests

This collection of requests includes all those things that relate to wide-ranging Access issues that can’t be categorized. These are my favorites:

• “The ability to have multiple versions of Access work and play well together on one machine.” This was my request, also. I have a computer with Access 97 to Access 2003 working (mostly) together. I have a lot of niggling problems, but I currently just grin and bear it (I assume that a search for a cure will probably be more time-consuming than living with the problems). • “Decompile as a menu item and with full MS support.” This was requested a number of times. Since decompile works successfully as a hidden option, I can’t see any reason not to bring it out of the closet.
• “Why is usage of Citrix and Terminal Server/ Remote Desktop not promoted more?” I totally agree. I recently fixed up some issues with a database in central Australia using remote desktop through the Internet and across a satellite link from the Gobi desert in Mongolia. Access forms and reports ran very happily in this mode, as the screen paints are usually simple (so very little data travels across the network). I’d like to see a wizard that could analyze our interfaces for performance issues relevant to remote services such as these. It sure beats an expensive Internet conversion.

Database Container: 11 requests

The requests related to the Database Container include improvements to the way Access handles naming of objects and a number of requests to finish off the half baked object Groups initiative started back in Access 2000. The following request sums it up well: • “A few things that would improve the Groups usability include the ability to use groups to import and export objects, the ability to import/ export group definitions, and the ability to use Groups to limit the objects you see in other places in the database.”

Tables: 21 requests

Included in these requests are some very sensible suggestions that would improve our clients’ databases while also improving the documentation of our systems. Both of these goals are very worthwhile pursuits. • “Why can’t I copy data out of the table design window? This looks and behaves like a datasheet, but if you click in the upper left corner of the design window to select all of the rows and columns, you still can’t copy that text. Why not?”

• “Make the Linked Table Manager more useful—for example, allow us to size the window so we can see long paths, and allow us to group the links by back end so we don’t have to hunt for the oddball back ends. These two alone should be relatively simple to code and would be greatly appreciated, I’m sure.” I’d have to say, “Hear! Hear!”
• “Triggers or some other such event tree on Jet tables and Access queries that fire for data manipulation. Most of this currently can be kludged in forms, but this is the wrong place to do a lot of this kind of stuff.” Anything that improves data reliability should be encouraged.

Queries/SQL/Query Editor: 18 requests

It’s amazing to think that this wonderful Query Editor has now been with us since Access 2 and it still does a good job. Still, Smart Access readers came up with a number of great ideas to challenge the Access development team, including:

• “On the SQL Editor, wouldn’t it be great just to be able to do a find and replace and a few other normal text editor features?” No more cutting and pasting of huge blocks of SQL into a real text editor—that sounds like removing one of life’s great challenges.
• “Support for theta-joins joins using (<>, >, >=, <, <=) arguments and Union queries in the query design window.” I suspect that most developers don’t even know that Access actually supports these extensions, so this suggestion would be a worthwhile way to bring these concepts into the mainstream.
• “The ability to add comment lines to SQL statements when viewed through QBE.”

Forms and controls on forms: 27 requests

The most requests in this category were for forms and the controls (textboxes, combo boxes, and so forth) that you can place on forms. These requests varied across user interface issues, productivity issues, and improvements to the way that you develop your forms. My favorites included:

• “I’d like to see a container control such as the VB6 frame control to ease making groups of controls visible or not visible.”
• “I’d like the ability to manipulate the same controls in different rows independently of each other. Now when you try to manipulate a control on a continuous form (for example, disabling it), it applies to all instances of the control (all the rows of the form).”
• “The Insert ActiveX Control list should only list controls that work on Access forms.” I’d go one step further and couple this with better documentation of the additional controls that Microsoft actually does feel are worth supporting in Access. We might even see some of the more worthwhile ActiveX objects adopted by Access developers.
• “Colored command buttons.” I completely concur with this request, as simple as it is. After 10 years of slugging away on boring forms, I’d like to get something new to jazz up my forms. Just rounding the button corners as was done in Access 2003 wasn’t enough!

Reports: 18 requests

The quality of these requests shows that the Access reports are still a key factor in Access developers’ minds.

• “The ability to fine-tune report layouts in a preview window by dragging/bumping fields and labels with data. The one feature I like that Crystal has and Access doesn’t.”
• “Why is there still a 22-inch length limit on report and form sections?” I thought this was a good question. I wondered if it was worthwhile to go through all of my old Access code and find where I bumped up against those silly arbitrary limits. My personal favorite (at the moment): Why am I only allowed 32 joins to a single table?
• “Reports should have header/footer handling comparable to Word (allowing separate headers/ footers for the first page, and separate left and right headers/footers).”
• “Access should never print a blank page!” • “A printout command that tries to fit all the columns

on the page.” • “Why can’t we have a true Send to Word ‘doc’ file

that maintains graphics? RTF is way outdated.” Again, I felt this contributor’s pain: I have a client who will fire anyone who gives him an RTF report.

Programming VBA: 13 requests

There was a good smattering of VBA-related requests, the best of which included these: • “Wizards should stop generating DoMenuItem code

that refers to Access 95 menu items.” • “Add an event to prevent someone from closing the

application by closing the Access window. There are OnClose events for forms, but it’s pretty limiting if there’s no OnClose event for the whole application. Seems like that would be a more important event to trap.” This new event could be tied in with an application Open event that would precede the Startup form and the AutoExec macro. An event like that could prove to be a real boon in implementing security. I’d also like a no activity event, complete with a timer property just like a form. Getting people to leave a system after periods of inactivity is an important management function.

Error handling and the VBA IDE: 24 requests

In some ways, coding in Access and Access’ error handling has just achieved parity with other code environments in the Office suite. However, Access is an application development platform, in a way that Word and Excel are not. Hopefully, this part of Access will be swept along at a faster pace than the rest of Access. Anyway, here are the gems from the requests in this area: • “A nicer method than the ugly Erl method to get the

line of the error would also be nice. Code with line numbers is not very readable.”

• “Bring back the expression builder for use in code.” • “When entering If or Select Case statements in the

VBA IDE, why can’t the editor automatically insert the End If or End Select (similar to the automatic End Sub and End Function)?” This could also apply to Set statements.

DAP, .NET, XML, and OLAP: 5 requests

The silence was deafening in these areas, but I thought that this .NET quote summed it up for all us programmers who want to stay in work: “Let us be part of the future and let us use our current code (with reasonable effort).”

Another quote that summed up developer frustration was: “Data Access Pages that work on Windows and the Web like File Maker Pro.”

Security: 8 requests

Despite the rather antiquated state of Access security, this area wasn’t inundated with requests. Maybe my book has given everyone enough to get on with. One quote is worth a mention: “I would like to see field-level encryption using a variety of available technologies without paying a fortune to license third-party technologies.” I could see this being a good role for the .NET integration. And, please, let’s not pretend that macro security introduced into Access 2003 was a security enhancement.

Office compatibility: 10 requests

This is an area where just the product name “Office Access 2003” screams out for greater improvements. I thought the quote that best summed up the decayed state of this supposed integration was: “Fix importing data from Excel. In text data files you can define the column’s data type. Currently Access decides this for you and then fails when it’s wrong. This is classic for Social Security numbers, which Access views as numbers and drops the zeros on the left. Also for ZIP Codes, which Access also decides are numbers and then fails to import when it encounters a long ZIP Code with a hyphen. You’d think they would have figured this out by now. At a minimum, allow the user to define the data types of columns.”

Runtime access: 5 requests

Being forced into delivering a runtime version of Access is a problem that I’ve never found myself facing, but many do and seem to have a variety of experiences with the process. Maybe this request is a solution to this long-time bugbear: “The runtime installation should be delivered as a free feature of Standard Office. This will save me from all the hassles of building an installation job, which I at least have no interest in—I only want to solve my customers’ problems, not create new ones.”

Help system: 7 requests

I thought I would leave what I think is the biggest problem in Access 2003 for last: the Help system. The Help system has evolved into a system that’s akin to a human trying to sweat out malaria. It looks and sounds like madness. I think the following partial Help request best sums it up: “Speaking of Help—in its current state it is almost unusable. It is certainly frustrating. I’m an experienced developer (more than 30 years) and have been working with Access since version 2.0, and all I can say is that with each new version, Help gets worse!”

This brings me to the summary of this monster list, but before I do, special thanks go to Helen Feddema for more than 30 excellent contributions and Richard Olsen from Finland, who also filled his sleigh with many good ideas. So was this a worthwhile exercise? Mike Gunderloy, a fellow contributing editor of this publication, had this to say: “You’re joking, right? Assuming that there will be an Access 2006, and given a normal Microsoft product cycle, the specs are already locked down and coding has started. Any major feature changes you suggest now will be pushed back to the following version or beyond. This would be a good time to make suggestions for Access 2008. I’ve seen this time and again; those outside the process almost always underestimate the amount of lead time that an Office release takes.”

Mike said his piece early on the blogger board, and yet still another 200 contributions flowed in, all from folks who probably read Mike’s piece. So, if nothing else, we’ve provided an opportunity for you to have your say, and if your suggestions don’t see the light of day in the very next version of Access, there’s a good possibility that they’ll make it to an important desk at Microsoft. You never know.