Articles and links about Microsoft Access or Microsoft Office Programming.

Friday, June 18, 2004

The Next Version Of Microsoft Access - Have Your Say

This weblog is run in conjection with an Editorial published in the August 2004 edition of Smart Access. Read more at

http://www.smartaccessnewsletter.com
or at my popular website
http://www.vb123.com/access2006/

The purpose of this web log is to encourage you to blog your ideas here about what you want to see in the next version of Access. We will even review some of most interesting ideas in a future issue of Smart Access. More importantly, though, we’ll collate all of the ideas and send them to the Access development team at Microsoft. After that, who knows? Your great idea might actually make the production version of Access 2006. If you think that you have a good idea, or if you simply want to embellish (or castigate) my ideas blog to this post or visit http://www.vb123.com/access2006 There you can see the latest contributions from our readers or find an e-mail address to send your contribution. Otherwise blog direct to this page to instantly see your idea published.

To help fire up your creative juices, here are my ideas for our list. My first request is to improve the basic forms interface. In my humble opinion, Access in its basic state needs to become a whole lot easier for the naïve user to interact with. For example, if you were to create a standard form that worked with a table, and then give it to someone who has never used Access before, that person wouldn’t have a clue what to do. The default form generated by the form wizard comes with navigation buttons way down at the bottom of the form, a well-hidden record selector, and some complex toolbars and menus that are designed for both developers and users and don’t really work well for either. I’ve yet to come across a typical user who looks at the menu bars and toolbars to figure out what to do next (and even if users did that, they probably wouldn’t pick anything that would actually help them). Generally, the inexperienced user likes big buttons that say Save or Find or Add Record. I believe that the form wizard should include these as a default option. In my humble opinion, Microsoft needs to take Access back to the usability labs and rework the form wizards and related items to produce forms that make sense to mere mortals.

My second request is that there should be a database-specific option that prevents people from using reserved words, words with spaces, and special characters (such as percent signs) to name database objects such as tables and fields. This should be turned on by default in new databases. This would make both Access development and upgrading to SQL Server much easier. I also really doubt that inexperienced developers would object to these restrictions. While Microsoft is at it, this option should also be responsible for stopping the control wizards from producing meaningless names for controls like Command17. The wizards should either generate meaningful names (like cmdExit) or give the user a chance to enter a meaningful name. I recognize, by the way, that this complaint probably reflects a good part of my work: fixing the work of other, less experienced developers. These changes would allow me to concentrate on new development rather than fixing up weird and wonderful names -- work that makes programming a chore.

Another area of Access that really needs a lot of work is security. Having spent many months on this topic for my book, my detailed list would run many pages. There are simple things that should be changed: The bypass key (Shift) needs to be controlled, the database and VBA passwords need to be encrypted into a better place in the file, and shortcuts shouldn’t reveal the location of the database or the workgroup file. If security is to remain in its current rickety state, developers need new functions that take advantage of Windows 2000 user and group names and .NET encryption functions. These need to be callable without reference to complex Windows functions. These should work like the intrinsic Office File Dialog and the File Search VBA functions that were introduced into Access/Office 2002. Other goodies I’d like to see include a direct link to SQL Server from MDB files. ODBC is just a complication that one could do without. I’d also love to see Excel become the second report writer for Access. I use Excel through a combination of automation and templates all the time for my Excel-mad customers, and I sure would like to see a lot more integration in this area.

My big plea, though--and one that would probably increase the adoption of Access enormously--is to include Access with Office standard or the Office Small Business edition. Even if this were only a "lite" (read: "crippled") version of Access that was easy to use, it could increase the groundswell of Access users quite considerably. Eventually this would translate into a lot more Access work for developers as the lite databases that actually prove valuable have to be converted into multi-user business databases.

69 comments:

Right now, moving data in and out of tables in Access using the XML facilities is far too hard to do--you really have to know XSLT if you want to convert data between its original format and the format that Access requires for the data to be correctly imported into a table. It would be great to have a set of tools (maybe like the BizTalk mapping tools) that would support generating XSLT to handle the conversion. It would really open up Access' XML facilities to the masses.

To introduce myself::I am developing with Microsoft Access since version 1.1 and my mainstream application is now in use by more than 200 customers in Austria.Currently I use Access 2002 with Office SP3, Jet SP8, DAO and ADO. Details see at http://www.windent.at

My whishes for the next version of Microsoft Access:

.NET

Let us be part of the future and let us use our current code (with reasonable effort).

Total Independency of the runtime from full Access

For Setup I use Wise and modified Sage scripts.At current it is not possible to distribute a runtime version which is totally independent from a full version of Access on the same machine.The /profile switch for example is not working for user dependent options so all things defined by Access in the HKEY_CURRENT_USER hive are shared between runtime and full version. The possibility to make a .exe as runtime would be very appreciated.

Security aware functions in VBA

At present you have to do rely totally on the Windows API when it comes to security. This is quite a nightmare. Perhaps .NET could solve this.

Error handling:

Centralized error function

A function defined at database level (property for example) that should get control in case of an error not handled at individual functions or subs.Currently I am using error handling in every function and sub, but almost 80% of the error handlers are the same - it's just to much of redundant code.

Err object extensions

The Err object should have new properties to get the following information:- Name of the function or sub where the error occurred.- Invocation stack (we can see it now but not get to it by code).A nicer method than the ugly Erl to get the line of the error would also be nice. Code with line numbers is not very easy readable.

AutoKeys Macro:

Give us a solution without a macro.AutoKeys is the only macro we (must) have and I want to get rid of it.

It would be really good if the Switchboard could be tied in with Windows User names. It would also be very useful if the Switchboard offered more options such as open forms in datasheet mode or readonly mode.

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.

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.

1. Extend the functionality of continuous forms. Be able to manipulate the same controls in different rows independently of each other. Now when you try to manipulate a control on the continuous form like disabling, for instance, it applies to all instances of the control (all the rows of the form). Also a setting to highlight a secified row by changing its backcolor independently of the rest of the rows (goes along with my first point).

2. Improve the Jet engine and "Linked Tables" to behave more like a true "Client/Server" architecture. Most developers split the database in to a front-end/back-end approach anyway. It would be nice if the server did some of the processing work instead of sending an entire table-set for the client to deal with. I know this may be stepping on the toes of SQL Server and may be bad marketing but from my selfish stand-point it would a good thing.

3. Just want to second the comments made about error handling and security made by Peter Lienhardt.

One of the main annoyances I have was the same in both Access 2000 and 2002 (XP). In the Form and Report Design Mode, When I copy and paste an element between forms/reports, the pasted elements come in at the top of the section of the form I'm in. Why can't the element be placed where my cursor is when I right-click and select paste.

Also, Why is there still a 22" length limit on report & form sections. In a report this is only about 2 pages. I had to go to a tabbed interface for my 53-question survey results instead of presenting them as one continuous form. Same problem with reports. I have a 53-question survey, some with multiple parts and some of those parts are multiple choice. For example, I have one question that has 10 different sub-questions, and each sub-question can have 3 possible answers. That's 30 line-items for a single question. It's a pain in backside to have to do this through a series of sub-reports.

I too vote for grouping of objects in a form or report. How about being able to name an object group and then being able to collectively set colors, enabled/disabled, and visible/invisible status.

Built-In, better global error handling would be nice. Then you could have One all-encompassing routine with say a case statement to deal with errors returned by different procedures, etc.

Built-In Objects for returning PC Name, Logged User Name, Domain Name, IP Address, Etc. Perhaps some of this is in 2003 but I haven't had any experiece with it and have had to use API calls to get at this information.

How about global search and replace across all controls of a specific type, such as text controls.

Ability to copy data out of the table design window for quick documentation.

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!!?

Add more OLAP -- I would like to be able to create/export a data cube directly from an Access database. I am using Access to hold valuable data and the ability to create the cube directly in Access instead of using Excel would be a productivity boost. It will also make OLAP more accessable to all Access users.

Eliminate the distinction between ADP and MDBs, and have it all in one database. Right now, the cost of migrating an application from an MDB to ADP is very expensive and the loss of local Jet tables or links to Jet databases seems unnecessary.

This has prevented us from migrating to SQL Server in several instances because simply linking in an MDB is too slow, but we can't afford the effort to take everyting to an ADP when we really only need to move a few tables initially.

Functions I would like on the wish list include.1: The ability to Export a spread sheet as Text file type.2: A standard button for adding a group of BIG navagations Buttons to our Forms. First/Previous/Last/New for example3: A Function Wizzard that can handle "text" field names4: A simple security wizzard to use as a starting point for setting up user login/security

Rob Sindle:(caveat, I have not used 2003 yet)1. Allow you to select multiple items from the database container using shift or control and allow you to delete/open, all at once.2. Make the LInked Table manager more useful (Allow you to size the window so you can see long paths, allow you to group the links by back-end, so you don't have to hunt for the oddball back-ends, etc). These 2 alone would be relatively simple to code and would be GREATLY appreciated, I'm sure.3. Allow you to copy both a button and its code from one form to another. Make it an option.4. Allow you to paste an object onto a form where you click, rather than at the top of the section.

1. From an occasional programmer, but full-time database project manager: Menu for printing tables in design view (the documenter is too cumbersome), with the ability to select one or more tables for printing.

2. Ability to tag a short form date into the file name upon exit. One can't rely on the file attributes for version control.

Helen Feddema's Access 2006 Changes (posted by Garry)August 1, 2006Basic Edition1. Query column size sticky2. Automatically name objects and controls according to LNC (you suggest something close to this)3. Zoom for form and report design4. "Pin" controls or groups of controls so they can't be moved or resized inadvertently.5. Margins visible on reports, and controls can't be moved or resized outside of margins.6. Click in sequence to set tab order of form controls.7. Optional standard error handler in procedures.8. Make wheel on Microsoft mouse work in module window.9. Bring back the Expression Builder for use in code.10. An option that, when set to Yes, makes it possible to go to the code (or control, or whatever) that caused an error, for all error types (no more mystery "Invalid use of Null" error messages or parameter popups). This option would be set to Yes for developer use, and No for end-user use.11. Conversion of Nulls to zeroes or zero-length strings should be the default; you should have to use a function to preserve Nulls.12. Full Search & Replace in Zoom window for expressions.13. Click on a source query or table in the top pane of the Query Designer to open it in another window.14. When opening a query or SQL statement from a RecordSource property, the window should not be modal.15. Never, ever get a message about not being able to save something because another user has the database open when you are the sole user of the database.16. A way to totally suppress Write Conflict messages with a default action.17. References to other Office object libraries should automatically upgrade or downgrade to the Office version in use when a database is opened. (Word and Outlook references in particular don't downgrade.)18. Allow opening a subform when the main form is open (if necessary, close the main form – but do it automatically).19. Add a Form Save event with a Cancel parameter that would fire whenever a record is saved. 20. Macro recorder, that saves macros as VBA code (for automation of end-user actions).21. Reports should have header/footer handling comparable to Word (allowing separate headers/footers for the first page, and separate left and right headers/footers).22. Reports should support many more paper types and sizes, similar to Outlook.23. Regardless of how report groups are set up, and new pages forced, Access reports should never print blank pages.24. Allow selection of different printer trays for different report sections, say Tray 1 for the first page of a report, Tray 2 for the remaining pages; or Tray 1 for a letter, Envelope Tray for its envelope.

Developer Edition and Office Links1. Can create .exe files2. Packager can handle standard Office tasks automatically, such as putting Word or Excel templates in the correct folder, creating Outlook folders, publishing custom Outlook forms, etc.3. Built-in boilerplate code storage for easy access when working in VBA code.4. The Insert ActiveX Control list should only list controls that work on Access forms. It should be possible to open Help for each of these controls from the Insert dialog, and from the control itself. All events of the controls should work in Access, and (where relevant, such as the TreeView control) they should be bound to data.5. Include a set of well-documented ActiveX controls (with all supporting files) that work on Access forms, which can be distributed with Access applications. Note: Documentation for these controls should be Access-specific, i.e. it should explain how the controls work on Access forms.6. Sell the ActiveX control sets separately, similar to Font Packs, so people who want to use them can purchase them without having to have the Developer edition.7. The Exchange/Outlook Wizard should set up two-way links between Access tables and Outlook folders, including all standard and custom Outlook fields, with tools to set up relationships between linked Access tables and flat-file MAPI databases.

A: Better communication with other office products:1. Excel: If I set up an Access report that looks like a well designed Excel worksheet, I should be able to export to Excel and have it look exactly the same.2. PowerPoint: If I create a graph using Access data, which I would like to show in a PowerPoint presentation, there should be an easy way to export the graph, either as a graphic (e.g. emf, bmp, tif), or as a PowerPoint slide or presentation identical to the Access report. Some of my reports have up to four graphs on them, and lines and boxes, and snapshot format totally destroys most of it. However, users want this report on a page of their presentation.

B: I want to be able to double-click a sub-form or report and have it open in a new window, change it, save it and continue with the main form or report. It used to work this way. Now my tiny .25" tall 'can grow' sub-report can't be edited without closing the main and opening the sub, then closing the sub and reopening the main to see if my change was satisfactory.

C: Larger text fields (> 254 char), or more options for memo fields, e.g. sorting, viewing the entire memo, not truncated, on reports. Format-able memo field info, e.g. my memo might have ten lines, one of which I would like to store as bold, underlined text, maybe in a different font or size.

D: More information about API calls. Everything I have learned about these came from user-groups or sites they pointed at. I think a section in Access Help should contain info about these. Where does everyone learn this stuff from?

E: More user-friendly database templates. e.g. the music collection template requires so much re-design for a book, coin, stamp, etc collection you have to start from scratch. Access is not intuitive in any way for beginners.

F: A better Help menu. Now if you enter a search word, too many options come up, not in alpha order, and some very remotely connected...Way back in an earlier version of Access (2 or 95??) you would get a short list like "Do you want to see help about?" and it would list 6 or 8 general areas, e.g SQL, Report Design, VB Code, etc, so you could narrow your choices to something more relevant to what you were searching for.

G: Some way to view bottlenecks - I have some forms that open very slowly - some way to analyze the form and say what it was doing and how long it was doing it when it opened, or ran a function.

H: Redesign the GUI in the Northwind database to show off some of the more interesting form and report features, instead of the usual generic boring column oriented look. e.g have some unusual graphs, tabbed forms, creative use of color, ActiveX, etc. Some of us are good with data, but have zero creative ability in form and report design. A good example is a great start.

A well considered list will have far more value than no input at all. For me, the main interest in this list is seeing what is important to others. That said, I am realistic enough to know that we are mere mortals in a product with a large user base. Anyway we will submit the list to Microsoft and that can gleam what they may from our input.

Very simple: I want to be able to zoom in during form design. We actually purchased Crystal Reports not for any of the advanced querying or what have you, but because some of the very complex forms with lotsof boxes and such were simply too difficult to create and manage within Access. Multiple zoom levels, PLEASE.

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.

Maybe replace Jet internally with Cloudscape (http://www-306.ibm.com/software/data/cloudscape/), or just make Jet a compatability layer on top of MSDE.

Oh, and while we're at it, add some true OO to the hierarchy. You know, like inheritance. It would be nice to make a form that has some generic functionality, and make new forms inheriting from that form...

1) Would like to see field level encryption using a variety of available technologies without paying a fortune to license third party technologies.

2) Ability to have multiple versions of Access work and play well together on one machine.

3) Better Database and VB Project stability. All too often we spend a fair amount of time recovering from (if possible) database corruption issues that are not always well documented in Microsoft's Knowledge Base

I would like to see the long neglected Macro language be made more useful.

1. Add a surpress if empty argument to Printout2. Add a CloseAll command3. Don't require an error if you attempt to delete a non-existent object4. An empty table command5. A resize all columns command (that uses the entire recordset)6. A printout command that tries to fit all the columns on the page7. A transfer text command that allows you to specify the qualifier and delimiter8. A sendobject command that allows the object to go into the message body instead of an attachment9. Allow multiple attachment on e-mail10. A useful report wizard

1. Vertical alignment properties on labels (really important for lining up column labels when some are multi-line and others aren't). Plus it's helpful when you're changing label text at runtime and don't know if the text will take up one or more lines. It's available in Word tables, Excel and PowerPoint -- why not Access?

2. Useable and useful groups (I haven't used A2K3 yet, so maybe this has been improved already but for A2K and A2K2...). A few things that would improve their usability: Ability to use them to import and export objects. Ability to import/export group definitions. Non-static object information (including the ability to rename and delete objects from within groups). Ability to use limit the objects you see in other places in the system based on groups (e.g. Add Table/Query to a Query dialog box).

3. Ability to scroll in the Access VB IDE. (Note: there is a solution to this -- look at MS KB article 837910 and the starposts on wopr.com for how to implement it, but this really should be built into the IDE.)

4. Usable form and report templates (and the ability to use more than just one).

5. No automatic default value for table fields! (Whoever thought that every number field should have a default value of zero was a good idea, and didn't provide a way to turn that "feature" off, should be taken out back and...) It's a complete waste of my time to have to delete something that Access puts in automatically when I'm trying to create a table.

6. Better... no, usable documentation and help. Searching for information in the help files is impossible. Documentation links are only occasionally helpful (especially in code). And just try to find instructions from Microsoft on how to use the Treeview control or how to programmatically drive the charting control...

7. Supported decompile. So many problems in Access databases lead back to compilation problems in a specific object (usually a form, module, or report), that it's ridiculous that the only solution to the problems is an "unsupported, undocumented (well, barely documented since it can be found in the MSKB, but it isn't easy to find)" command-line option. What would be nice is a way of identifying which object is causing a crash and the ability to force a decompile of the database (and complete removal of all compiled code) from within Access.

8. Nice, but much less important than the above, would be the ability to create certain types of queries from within the query editor, such as union queries.

9. A SQL view window with usable capabilities, such as find and replace, help file integration, etc.

10. A built-in RTF memo field. Yes, it can be done using 3rd party controls, but that shouldn't be necessary.

Access could do with a database wide activity event so that the database could be shut down if there is no activity by a user after a set period of time. Also a database method to contact people using a database would also help with management. Shutting down a database is important for upgrades, compacting and backups. Peter

Since you asked...I use Access 2000 and I'm not familiar with Access 2003, so some of these might already be handled.

1. The VBA subs are supposed to sort alphabetically, but they get out of order. Would like a way to resort the subs into alpha order.

2. When picking an object in the VBA window, a sub in created automatically before I can pick the procedure I want. Then I have to delete the insert and pick the procedure I want. Can't it just wait until I pick both?

3. Custom menus are great, but more options are needed. That is, there should be a better way to open and work with the menus (some menu lines can get long and when opening the properties it gets hidden behind the menu and there's no way to get to the form to view the data). Also, if I make a mistake in editing/adding/deleting, there should be an undo option. It would also be helpful if menus could be copied and pasted easily both within a database or from one database to another.

4. In the QBE, there needs to be a way to drill down to another query used as a data source for the existing query. It's a real pain to have to close the current one, open the other query and make changes, then come back and finish the first one.

5. When entering If or SelectCase statements in VBE, why can't the editor automatically insert the End If or End Select (similar to the automatic End Sub and End Function). This would save considerable time and frustration.

6. The linked table manager is a joke. This needs to be redesigned to work better with databases that use multiple back-ends. There should be the ability to select by a common backend so you don't have to scroll down the list and pick out the ones you want. Alternatively, there should be a sort option by source. Also, the window needs to be sizable. Too often, the pathnames are too long and it's impossible to read which tables come from what backend file. If I didn't have a written list, I'd never be able to use this feature (and often don't simply because it's too cumbersome).

7. In crosstab queries, why can't the program insert zeros in the fields where no data exists?

8. I can't tell you how many times I've created a new report that prints fine on my printer, but when one of my clients tries out the report, one or more lines will be truncated (I assume due to a font incompatibility). Isn't there a better way to manage fonts substitutions so this doesn't happen, or a way to package the fonts with the database?

9. I'd like the program to automatically and gracefully cancel report requests where there's no data, rather than having to program for this.

10. Every new sub and function should be created with automatic error-handling, similar to what's provided with the command button wizard.

11. When importing objects from another file, there should be an option to replace, rather than import and tack on a "1" at the end if there is another object of the same name. I frequently use numbers at the end of my tables and queries for my own tracking and when I need to import it's very confusing which object is my original and which is the import. It would be better if the program would tack on the word "copy" at the end when it encounters another object of the same name. This suggestion also applies to situations where users drag objects from one window to another (where each window is a separate database).

12. Please, please, please provide a list of table and query names in the VBA window so when writing code I can look it up (and even better - copy), and I don't have to keep switching back and forth or writing the names down on scratchpaper.

13. When creating update queries, in the update to field when I type in a field name without the object name, it gets converted to a text string in quotes. If I don't catch it and take out the quotes and put in square brackets, I end up updating all rows to the text string, rather than the value I wanted. Please, can't this be fixed to assume users want the field name?!?

14. The Groups feature is great, but I occasionally have to import all my objects into a new database and I lose all my groups. I'd like to see these as import items as well.

This way when you receive a file from somebody else, you know by the name what type of thing your dealing with. For example if you see a field on a form and it's name is intOther you at least know it's an integer.

1. Automate more functions. Why do I need to spend so much time dinking around with little bits of code?

2. Improve deployment by the ability to create a true .exe file. Include all referenced files. Eliminate the runtime version hassle on customer machines.

3. Show the help system index listings in alphabetical order. It takes way too much time to find anything.

4. Make the creation of shortcut menus easier.

5. Being able to group controls and to freeze them in their locations would be quite helpful.

6. I would really like to see a merging of Access and VB.Net. Access would gain the power of VB and VB needs the user-friendly development tools of the Access forms, queries, and reports. For instance, VB has no subform control, which I use all the time in Access. True, it has a grid control, but my Access subforms are often not grids.

New version of Jet Suggestions (or a replacement that provides the same file base db with no required server instance)--------------------------------Real Database security (actually in the DB and with more granularity).Larger max database size.Stored Procedures and TriggersEncrypted field support64 bit integer supportboth ASCII and Unicode character supportLess difference between SQL Server SQL statements (for instance not having the empty string restriction on by default).Better transaction handling.Compacting through SQL.

Both Jet & SQL Server------------------------------------bcp supporting both Jet and SQL ServerThe ability to generate Database Setup and Update Utilities or scripts. This would greatly ease deployment (especially for updates).Support for Identity Set Primary keys (where the key is (server or site id, autonumber incrementing unique to each site id rather than autonomously).The ability to insert into Identity (Autonumber) columns or let a value be generated.Support Replication using Identity sets and collision handling rules.

Access------------------------------------The ability to write queries to access Outlook PST files (or even better across multiple files to get around the search limitation currently in Outlook). Alternately PSTs could be added as a supported Jet type.The ability to do anything DB modification or query related through SQL that you can do with the UI, and actually have the statements documented.Improved Query Editor.More powerful programming environment.

Fix importing data from Excel. In text data files you can define the column's data type. Currently Access decides this for you 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 figure this out by now. At minimum allow the user to define the data types of columns.

I'd like to see the following, in no particular order:1 Sort out the VBA password bug - if you protect your code with the VBA password feature then compact and repair, when you close your app an error occurs unless you first re-view the code by entering your VBA password again.2 Allow Access to iterate through a form/report controls collection in the order they appear on screen not in the order they were created.3 Decompile as a menu item and with full MS support.4 Coloured command buttons.5 In the Properties box allow the ALL tab to be sorted in Alphabetical order rather than the jumbled manner as at present.6 Return to the quality/style of Access 97 Help files.7 Much better menu item icon editor tool.8 EMail replication option for remote occasional users as well as current indirect/direct/internet methods.9 Ability to add REM statments to SQL statements when viewed through QBE.

First of all my and all others praise to this brilliant initiative, even if the outcome of it could be discussed. Instead of all of us sitting and getting angry and mad on the new face of Microsoft, lets do something about it, at least tell them our thoughts. I don’t know if it is possible but I think a listing ranked on a voting on this page would be of greater value to Microsoft so they could feel the enthusiasm here. Another observation on the wish list so far is that I don’t see any vote for Internet-functions or more virus protection and only a few on upsizing and .NET. Strange when this is the way Microsoft wants us to go…

In my world MS Access is one of the tools I use when delivering solutions to my customers, but it is the far most important. To me the splitting of development of the Office products seems strange and hard to understand. Could anyone tell me why there is three tools for developing an SQL-statement delivered with Office: MS Query in Excel, QBE in mdb and the stuff looking almost like SQL Enterprise Manager in adp. And most strange of it all, the best tool of them all, QBE in mdb, seems to be stopped when it comes to new functions. The lack of corporation between the different development teems – it seems more like competition, at least when it comes to the products called ‘Developer tools’. So that is my first point on the wish-list: Try to corporate more at least inside Office, but also to the other divisions like SQL-Server and .NET.

The second most important of my wishes is compatibility, compatibility and still compatibility. If new functions are included they must be compatible with all my efforts both in code and knowledge. If not the functions are doomed to be failures. At least until I learn how to convince a customer who has invested 100’$ in a project and I tell him that it was all waste – now Microsoft has decided we shall through this money away and do it all over. And please tell me how I keep my trust with such a customer…. The conversion from DAO to ADO has been a struggle tough enough which I am not through yet, so I see no way of doing the ‘.NET-thing’ with my customers. I do agree it is technically superior but so was also OS/2, which failed because of incompatibility, and the history tells a lot of more such cases as well.

Third on my list is Citrix and Terminal Server. This is the answer most of my customers have chosen to solve DLL-hell, virus-protection, cost of ownership and cost of implementation, instead of choosing the incompatible .NET-way. It also solves the possibility of running applications over Internet without having to design them as WEB-applications – you develop an Access-application that will run well over Internet! Try my Access97-runtime on : http://www.pa-access.com/paaccess/PA-Access%20Demo.ica , if you have the Citrix ICA-client installed. If not you probably first have to visit: https://workonline.donator.se/. You have to excuse it is all in Swedish, my native language though. If you have trouble please let me know, my son runs it successfully from Sydney so I know it works. This new technology together with Virtual-PC gives so many new possibilities in system development that they must be accounted for in the future development of Office. The current statement at ‘best efforts’ (http://support.microsoft.com/default.aspx?scid=kb;en-us;209161) is just not good enough, although it has been changed recently from ‘not supported’. If Microsoft doesn’t want to follow their customers it is their choice, I have no choice. Don’t be blind there are other protocols than HTTP, RCP for example. Instead of moving data it is possible to move keystrokes and mouse movements…

Forth on my list is the ability to easier read data in the most common ERP-systems today (SAP, Movex… ). I know most of them are closed, very closed. In the past this has not been a problem for Microsoft, I remember read and update Paradox and Dbase-files in the first version of Access better than in the original product. The initiative taken by Information-Bridge Framework (http://msdn.microsoft.com/office/understanding/ibframework/default.aspx) is a step in the right direction – though it leaves all the tough problems to us. What we need is not a framework it is something that works right out of the box. I don’t see the big use in updating, 95% of the applications I see would be well off with just reading. I suppose the rumours of Microsoft trying to buy SAP would have solved this issue a little bit, if it has been successful. And of course compatible with my earlier efforts, else…

Fifth on my list is around the runtime. I think 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. It will also solve my problems of convincing the people responsible for the operation of the company network that my application is not a virus or something else that could compromise network uptime. I have failed many times to install my applications in big companies for the lack of what the call ‘Company certified solutions’. I don’t know if this problem is local in Sweden, but today it is almost impossible to run an install-exe without it has been certified by the IT-department of the user. If not so no runtime… And to get that certificate is a big timewaster. I often have to get the CEO:s signature on a paper before starting to do something useful for my customers. If Microsoft wants to charge me double for the ability to deliver mdb-files that could be run in the runtime-environment is more than OK. I will just pay the bill because it saves me a lot of time and troubles in the installation area were I have no interest of being. Today I don’t deliver runtimes, I tell my customers to install Access and if the customer hesitates I deliver an Excel-solution working with mdb-files instead. I also avoid OCX:es for this and DLL-hell reasons.

Some things that would be nice......1) More understandible error messages2) Better way to document tables, queries, modules, etc...something similar to what FMSInc offers.3) Easier use of forms / subforms4) Ability to generate and/or save more types of reports i.e., create PDF reports, etc.5) Remove the inclusion of a default printer - that's a pain when you're developing an application.6) Easier compatibility with other Office products.7) Include "web-style" buttons, menus, etc. That way, you can develop in Access, condition your users to what an Internet appearance will look like, and then switch your app. to the Net.

1. Option to display the report footer at the bottom of the last page rather than following the last detail line.2. Exporting a report to Word or Excel should maintain formatting. Both products support graphic elements so Access should not strip them.3. Wizards should stop generating DoMenuItem code that refers to Access 95 menu items. Even worse is the fact that the generated code uses numbers rather than constants so unless you have A95 installed, there is no way to figure out what the generated code actually does. Most users don't understand that they should give their controls proper names so I get to look at code for Command117 that runs item 5 on some menu from a 9 year old version of Access and figure out what it is intended to do.4. Update queries should be smarter. They should allow updating whenever specific records can be identified. Queries that use Max(), for example, to find a record in one table in order to use the key to find a record in the table they want to update won't work. It would be fine to place the onus on the developer when implementing this and restrict the updating to the outer query.5. All multiple versions of Access to co-exist without conflict. Users don't need to do this but it is extremely important to developers. Changes made to A2K2 and A2k3 are a step in the right direction but saving in an old format is not quite enough. I need to know that I am not using something not supported by the previous version.6. Support the concept of Sections for reports similar to Word documents so part of a report can be created as portrait and part as landscape.7. There needs to be a separation of "general Access development environment" setttings and settings for a particular db instance. Custom menus, for example, need to be separated into what the customer will "see" and what the developer will "see". Perhaps a db open setting that identifies "customer" or "developer" mode could be used to implement this.8. The customer/developer mode setting could be used to filter help along with which window was active when help was requested. When a user asks for help on "open form", he should see things that refer to the GUI. When a developer asks for help on open form, he should see programming help and the OpenForm Method/Action should be at the top of the list.9. Speaking of help - in its current state it is almost unsuable. It is certainly frustrating. I am 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! You really need to get help (no pun intended) on this issue from people who are new to development as well as experienced people. Your development people may "create" Access but they don't "use" it. They use C++ or whatever. You need people who "use" Access to set you back on the right path with help. For starters there is no reason that I should not be able to get a consolidated list of VBA functions ordered either alphabetically or by type. But I can't!!!!! How can someone who doesn't know the name of a function ever find it? Just because help contains syntax entries for all functions doesn't mean that that is the end of the obligation. Also, help examples should be more real world. When writing VBA, noone needs to see how to use a literal. They need to see how to use a variable name or a control name or something from a recordset. And finally - help should NOT be different depending on which window is active when I ask. The active window can be used to filter the request so from the container window OpenForm Action shows up before OpenForm Method and vice versa when the request comes from the VBA window but both entries should always show up. SQL, DAO, and ADO help should be integrated. And GET RID of the help for Windows forms! It simply does not belong in Access.10. Ability to export the DDL for a table or set of tables to facilitate rebuilding tables.

2 comments: The number of different versions of Office is very confusing to users. Often a client will buy Office w/o Access and then have to buy Access separately. Access should be part of the basic Office suite.

As someone mentioned, the "Help system" has gotten worse with each release. I remmember when it was actually very good and I could find answers to questions and lists of objects, options, etc. easily. I want to emphasize that one because the current system i an absolute disgrace. MS should be ashamed of itself for that aspect of Access.

Surely Microsoft are not considering dumping the workgroup security stuff! In a large organisation you cannot trust the network permissions, so the only way I can get the security I need is through workgroup security. It may be difficult, but that's to those who don't use it regularly. Please don't give up on the workgroup security!

I'd love to see Access runtime as part of Office Standard or Business Editions. This would make it easier for us developers to distribute Access applications without having to screw around with the runtime, the installation of which is so dependent on Access versions and O/S versions.

Access needs to have an embed function for true type fonts. Especially for fixed items such as form titles. I for example like to use the splendid font for this purpose but when I install on client PCs it is substituted.

When installing a runtime installation on a PC that does not have office installed, it is necessary to run a file named OCONVPCK.EXE to install the required image filters to be able to display jpg images. This filter pack should be included automatically in any runtime installation.

I am here because of search results for blogs with a related topic to mine.Please,accept my congratulations for your excellent work!I have a alaska job search site.Come and check it out if you get time :-)Best regards!

I would love the ability to search thru forms via subforms. Also, the ability to copy a record AND it's subrecords would be nice. While I'm at it... Why isn't the "find a record based on selection in combo boxes" available for the main form when subforms are used?

About Me

I have been programming and supporting Microsoft Access databases since 1995. In 2007 I received the Access MVP award from Microsoft because I waste too much time writing newsletters and blogs. Why not sign up for one of my newsletters at http://vb123.com/news and then you will gain from my wastefulness.