Okay, one last time: Access isn't going away. I just read another article from another pundit who, having noticed that a version of SQL Server is going to be included in the enterprise version of Access 2000, proclaimed that this means the end of Access. Let's get it right: Access isn't a database system. Access is a tool for creating database applications (and one of the best on the market). In order to make Access useful right out of the box, Access ships with an ISAM, desktop database system called Jet (also one of the best on the market). However, Access can be used to create front ends for any kind of database, including Excel spreadsheets, as Garry Robinson demonstrated in last month's issue (see "Adding Database Tools to Excel Spreadsheets").
Nor does the eventual dominance of ADO mean that Access is going away. ADO isn't a database, and it isn't a tool for creating applications. ADO is a means for accessing data in any format from any application development tool. And, from what I've seen, the best tool on the market for working with ADO is going to be Access 2000.
However, Access is going to change, and there might come a time when it's very difficult to point to one thing and say, "That's Access." The villain here (if there is one) is COM and Microsoft's drive to componentize everything. If you convert to Access 2000, one of the first things you'll notice is that the code development environment that we've grown to love/hate is gone. What you'll find, instead, is a separate application that allows you to create Basic modules, Class modules, and all the other components of VBA. If you want to see what this environment looks like, start Word or Excel or PowerPoint and press Alt-F11 to start its VBA editor. There you go. The VBA engine and editor is just one more component that Microsoft can put in a package with other Microsoft products and sell.
Microsoft's component strategy shows up most when you look at the References list for any of its development tools. What you see is a list of objects, some of which are automatically checked off when you start Access (or Word or Excel or . . .). The box on the Egghead (virtual) shelf that's labeled "Visual Basic" contains the same VBA engine as Access. But it also includes some special runtime objects, some data access objects (RDO), a standalone editor, and a compiler. Presumably, any of these objects could be repackaged with any of Microsoft's other development tools. What Microsoft has done is create an inventory of tools and objects that can be put together in any combination for which marketing can identify a buyer.
One of the latest additions to this inventory is Visual Data Tools (VDT). Like the VBA editor, this is a standalone component that ships with a variety of products (Visual InterDev 1.0 and 6.0, and Visual Basic 6.0, among others). VDT works with a variety of database backends to create queries, design tables, and manipulate the data in those tables. Hmmm, sounds like Access's various data design tools. It's not hard to imagine, then, that Access's specialized data design tools could be replaced by the snap-in functionality of VDT.
So what's left? Well, there's Access's report writer. But then, Visual Basic just got a banded report writer. Well, then what about Access's Form object, which contains more data-related events than the equivalent VB form object? Well, what about it? Why couldn't Access and VB share an enhanced Form object that had all the features of both tools? Microsoft has announced a Form+ project already.
In the end, Access might turn into a marketing product that describes a particular set of objects and tools in a box labeled "Microsoft Access." If it does come to this, the question will be how successfully Microsoft can deliver a tool with the productivity and ease of use of version we have come to love (and hate).
Erratum: I managed to do the unthinkable last issue and, in editing my Access Answers column, I edited some errors into the article. The last paragraph in the second answer in the column should have read:
The Column property accepts as a parameter the column of the entry in the listbox whose value you want to retrieve. The Column property then returns the value for the current row in the listbox (you can determine the current row by using the listbox's ListIndex property). Since you know that the GuestId is in column 1, the code becomes:
dbs.Execute("Insert Into tblAssignments Values(" & _
lstGuests.Column(0) & ", " & frmTable.TableId & ");")
Fortunately, I didn't go back and correct the sample database, which contains the correct code.