Accessing ADO

ActiveX Data Objects is the successor to Data Access Objects, and with Access 2000, ADO finally goes mainstream. Here’s a quick look at some neat things that Access 2000 and ADO can do together.

What really brought home to me that ADO was the future of data access came when I loaded up Access 2000. After I started Access 2000, I added a Basic module and went to the References list to see what was listed. Jet 4.0, the new version of DAO, wasn’t even listed. The current version of Jet (3.5) was listed, but it wasn’t checked off. What was both listed and invoked was ADO. For at least the first beta of Access, if you start writing code to get at your database, you’re going to start using ADO. Continue reading

Posted in Old Material | Leave a comment

Access 2006 The Community Speaks

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. Continue reading

Posted in Old Material | Leave a comment

Access 2006 – Have Your Say

The purpose of this editorial is to encourage you to put forward your own ideas about what you want to see in the next version of Access. We encourage you to send in your ideas, and we’ll publish them (in full) on my Web site. We’ll 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 (or into the pipeline for 2008).

If you think that you have a good idea, or if you simply want to embellish (or castigate) my ideas, visit There you can see the latest contributions from our readers, find an e-mail address to send your contribution, or even participate in some anonymous blogging on this topic at

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, the standard form that you create for a table using the Access wizard is a mystery to someone who hasn’t used Access before. 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 the default option. 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 editions. 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.

Finally, remember this: If you don’t participate, don’t complain.

See the responses here


Posted in Old Material | Leave a comment

Access 2000 – An Opinion

This month’s issue includes one of our “A Day in the Life” articles detailing what it’s like to earn a living working with Access. In that article, Michael Kaplan talks about a variety of topics, including the benefits of specialization (which was a topic in last month’s editorial). Michael also reviews the problems that can arise when working with a client that you disagree with (a topic that, coincidentally, was also discussed in the round table in last month’s issue of Information Systems Consultant). What makes Michael’s article especially interesting is that his client was Microsoft and the disagreement was with the direction that Access 2000 was taking.

I occasionally get asked if developers should upgrade to Access 2000. I always respond with a resounding “Yes, developers should upgrade to Access 2000!” However, the next question, “Should I be upgrading my clients to Access 2000?” is more difficult to answer. The easiest answer to give is the one that applies to all upgrades: “If you need some specific feature that the product provides, you should upgrade. Otherwise, you should always stick with your current toolset.” This answer ducks some important issues, though.

Access 2.0 was a terrific developmental tool. If you were an Access 1.x user, the features that Access 2.0 provided were so compelling that almost any application that you were building would have benefited from the upgrade. Access 95 was a different matter, though. While some of the features were compelling, (such as replication), there was another compelling “feature” that kept developers away from the tool: Access 95 didn’t actually work—at least, not reliably. Among the problems this created for developers was a lack of tools. Great developers need great tools, and the various companies that produce Access developer tools simply couldn’t get Access 95 to be stable enough to create tools to work with the product.

Then there was Access 97. Access 97 added the feature that Access 95 was missing: reliability. Not surprisingly, Access 97 came to dominate the Access development market, eventually pushing out Access 2.0. That’s not to say that, in its time, that Access 95 didn’t have a role to play. Access 95 provided a technology preview for where the Access team was taking our development tool. Looking back, that was really the role that Access 1.x provided: A look ahead at what Access 2.0 was going to be.

I’m afraid that, after living with Access 2000 since its release (and talking to end users, tool makers, and developers), Access 2000 is fulfilling the same role: A technology preview for the next version of Access.

That’s not to compare Access 2000 to Access 95. The product is more reliable than Access 95 was. Though the tool vendors are having their fair share of problems converting their products to Access 2000, the products are starting to appear. My personal feeling is that Access 2000’s ADP products herald the arrival of Access as a true, rapid-application development tool for client/server applications. I still don’t think that Access will ever be an i*net development tool, but Access 2000 makes me willing to believe that it can be part of the solution. As Michael points out in his article, if you’re considering creating an international application, Access 2000 might be your best development platform. Most importantly, Access 2000 points the way to integrating a powerful data access technology—ADO—into a wonderful development platform. I’m learning a lot from Access 2000 and am excited about the possibilities.

Service Release 1 (SR-1), just out as I write this, also fixes some of the problems with Access 2000. In this accompanying Download file, you’ll find Hardin Brothers’ report on SR-1. Hardin lists all of the fixes included in SR-1, along with all of the Knowledge Base articles about problems with Access 2000. Where the service release listed a Knowledge Base article, Hardin matched up the fix with the article. As a result, Hardin’s report links the fixes that have a Knowledge Base article number with the original problem report. More importantly, Hardin also lists all Knowledge Base bug reports for which no corresponding fix seems to exist.

Independently of Access 2000, the new Jet 4.0 database engine still has a role to play. To quote Stephen Forte, “Sometimes, I just want to mail a database,” and Jet lets you do that. Only with Jet can you link multiple databases in a single query (and to the SQL Server users who say that their database engine can, let me point out that it’s only because SQL Server 7.0 ships with a version of Jet). Jet’s ability to create a local database on the fly from code is a feature that I’ve taken advantage of in several applications. The Jet 4.0 engine also eliminates the scalability problems that prevented Jet from being used in i*net applications.

Nonetheless, Access 2000 has some significant stability problems, and its new file format makes it very difficult to have multiple developers working on a single large project. Does this mean that Smart Access won’t be covering Access 2000? No—in fact, this editorial introduces an issue with two articles on Access 2000. We’ll continue you to give you in-depth coverage on the important new features of the latest version of Access. You might find in these articles the feature that compels you to upgrade, or, having already upgraded, you might find a solution to a specific problem that you’re facing. While Access 2000 won’t be the focus of our coverage, we intend to remain your best source for information on ADO and all versions of Access.

But I’m having my clients stick with Access 97, for now.

Posted in Old Material | Leave a comment

A Movable Feast

One of the benefits of being the editor of Smart Access is that you get to read a tremendous variety of great information. One of the great pleasures of being the editor of Smart Access is being able to pass it along to you. But it also makes me realize how much an Access developer has to keep track of in order to succeed.

This month’s issue is a great example of why I love this job so much and why being an Access developer is so daunting. The articles range from technical discussions like George Esser’s piece on handling attachments to application-oriented ones like Mark Davis’s article on “query-based applications.” We’ve got business-oriented pieces like David Irvine’s introduction to EDI right beside Russell Sinclair’s look at Access and the Windows API in his first crack at the Access Answers column. But that’s only one side of the variety of material that we have to provide.

First and foremost, Smart Access is a newsletter to help you be more productive. After reading each issue of Smart Access, you should be able to create better applications with Microsoft Access. Sound simple? It turns out that making you more productive means a wide range of things.

For instance, Mark Davis’s article shows a novel approach to building applications that lets him (and his department) deliver new data analysis packages quickly. Mark has found a technique that lets him sharply reduce the maintenance costs for these applications. Since maintenance costs are typically six times development costs, this is a significant savings. More importantly, as new applications are added to a company’s inventory of systems, the maintenance burden on the Systems department increases. Eventually, a point is reached when no new systems can be added without an increase in head count. By holding down maintenance costs on new applications, Mark’s group can deliver and support many more systems than departments that aren’t using his techniques.

Making you more productive also means understanding more about how Access and Windows works. Russell Sinclair’s exploration of setting Windows’ default page size is a perfect example of that. Russell ends up leading you through the intricacies of the Windows API, showing off some features of VBA that you might not be aware of, and exploiting a property of Windows reports that I was completely unaware of.

Making you more productive means providing you with the tools to write better code faster. George Esser’s article provides insight into attached tables, a set of routines for working with them, and a novel use for them based on his tools’ ability to switch attached tables on the fly.

Finally, Access doesn’t exist in a vacuum. Most of us build Access applications to meet some business need. When David Irvine wanted to show how to use Access in an EDI environment, we realized that we first had to provide you with the background on this business area. That led to Part 1 of a two-part series on using Access in EDI — all the stuff you need to know before you sit down to write the code.

All of these articles are essential. I’m continually impressed with the variety of material that the successful Access developer must be aware of. In this month’s issue, we “cover the waterfront” for you. Over several months, we’ll also provide the depth that you need to succeed. Coming up in the next few issues are articles on using Access to create bar-coding applications, a three-part series on creating wizards, add-ins, and builders, and an in-depth look at Date/Time functions in Access (among other goodies).

For me, it’s like Christmas every month — and we’re going to make it the same for you.

Upcoming events

In May, I’m going to be presenting at two conferences. From May 17-19, I’ll be in Holland at the SDGN “Conference to the Max 98.” From May 21-23, I’ll be at the “Office DevCon” in Orlando, Florida. Both conferences look to be excellent resources for developers interested in getting the latest information on using Access. Many of our regular Access contributors will be at these conferences, including Paul Litwin, Andy Baron, and Mary Chipman. More importantly, if you’re going to be there, look me up. There’s nothing I get a bigger kick out of than a chance to talk to Smart Access readers and learn what you want and need.

Posted in Old Material | Leave a comment