Visual Basic in Office 2000
This column appeared in the September 1999 edition of
EXE. It came about because I was browsing through Office 2000 and came across a
reference to Visual Basic 6.1 - I got digging...
It’s been at the back of my mind for a few months to
refresh my knowledge of the implementation of Visual Basic that ships with
Microsoft Office. This thought came to the fore again recently when I upgraded
some of my machines to Office 2000. I was aware from previous tinkerings with
the beta copies of the Office 2000 Developer Edition tools that there were some
new add–ins, and that a newer version of ADO had been made available (more
on this later). However it was only when I got around to installing the Office
2000 specific version of the MSDN library that I realised that I must have
taken my eye off the ball for a bit too long. When I was presented with the
setup dialog that asks me which MSDN components to install I noticed that
Visual Basic was described as being version 6.1. What new features had they
included to warrant the jump from 6.0? Absolutely everything got installed from
this point on.
The answer was a disappointing “not much really”.
Looking through the MSDN documentation reveals that everything is still
referred to as Visual Basic 6.0, including the About box for the tool itself.
However once I had managed to dispel the resultant disappointment at the
absence of a new version to play with I dove headlong into the developer areas
of the product. I can only assume that the 6.1 reference was a vestige of an
initial marketing decision that was then reversed but overlooked on this
particular dialog box. However, there are actually some new features which I
propose to cover because they are useful to Office developers, and hopefully we
shall be seeing them included in the next version of the full–blown Visual
Basic product.
New tools
There are several new tools provided to help the developer. A
simple but effective one is the VBA String Editor. This add–in removes the
tedium of trying to split large strings into smaller units that concatenate
together, which is often done in order to display the whole string on the
screen and thus prevent any extensive scrolling within the editor. Using the
tool prevents the problem often encountered where, for example, a meaty SQL
statement can be syntactically incorrect because of missing embedded quote
characters. To this end you are presented with a simple text box in which you
can type or paste the entire string. The action of pressing the update button
then breaks the string down using appropriate concatenation and
line–continuation characters.
A couple of related tools are the VBA Code Commenter and the
VBA Error Handler. These use predefined templates with macro expansion
facilities to paste standard header code into a single procedure, all
procedures within a module, or all procedures within a project. This is
achieved through the use of template files (with an .eht extension) which can
contain token characters which are expanded at paste–time into meaningful
values. For example $$A is expanded into the author’s name. While this is
a useful tool I find it disappointing that you need to use an external editor
such as Notepad to construct the template. This might sound like whingeing but
you are provided with a read–only window in which you can view the
template code, so why not allow for editing too?
Having switched to whinge–mode, I’ll follow up with
another one – you can’t use these add–ins within the
full–blown Visual Basic product because the VBA environment exposes a
different extensibility library to the Visual Basic 6.0 product. However, if
anybody does become aware of a workaround then please let me know and I’ll
post it on my web site.
One tool that I really do feel we’re overdue for is the
Code Librarian, shown in Figure 1. This application acts as a repository for
pre–existing code which can be pasted into your project. Each repository
is contained within an MDB file, and new ones can be created as necessary. The
repository file that is provided with the Code Librarian contains a whole host
of very useful routines, some of which are understandably geared at automating
Office 2000 objects, but there are many other categories of code such as File
I/O, sort routines, ADO manipulation, error handling, and encryption, to
mention a few. Corporate developers who can be sure that the end–users
will already have Office installed will be able to integrate the Office
Assistant character into their applications, using the provided code as a
springboard. The problem that I mentioned with the other add–ins, namely
that you can’t load them into the Visual Basic 6.0 environment, isn’t
so bad in this case because you can load the Code Librarian as a standalone
module and transfer the code via the clipboard.
The Office 2000 Developer Edition also includes version 1.2 of
the HTML Help editor environment. Visual Basic 6.0 only ships with version 1.1
so it’s worth copying this over if you’ve got access to it, otherwise
you’ll need to download it from the web. Finally, the Answer Wizard
toolkit allows you to create help topics that tie in with the natural language
query facility that the Office Assistant provides.
ADO 2.1
Something else that comes along with Office 2000 is a newer
version of the ADO libraries. Visual Basic 6.0 includes ADO version 2.0, but
Office 2000 includes version 2.1 and so will overwrite your “older”
files. At the time of writing (July 1999) post–2.1 service packs are also
available from the Microsoft web site at
http://www.microsoft.com/data/download2.htm.
A quirk of the Microsoft distribution strategy – though I
suppose they have their reasons – is that the lightweight (i.e.
connection–less) Recordset components of ADO do not form part of the
formal ADO package; new releases are shipped along with successive releases of
Internet Explorer. Therefore if you’ve got IE5 then you should already
have ADOR version 2.1, although from my own observations you might need to
re–register the msador15.dll file with the regsvr32.exe registration
utility in order to make it appear properly in the Visual Basic References
dialog.
Apart from a number of important bug fixes, ADO has been
extended in several areas to provide increased support for some of the
underlying OLE DB providers. Remember that OLE DB is concerned with providing
access to some of the more diverse, or at least non–typical, forms of data
and so the simplified object layer that is ADO occasionally needs modification
in order to keep up.
One such modification is a tie–in with the Microsoft OLE
DB Persistence Provider which saves and restores data to a file – the
lightweight Recordset concept again. Although this persistence was already
available in ADO 2.0 the facility now exists to save the data in XML format so
that it can be manipulated across Internet environments.
Also new are the Seek method and an associated Index property
to facilitate fast searches through Recordset objects. Once the Index property
has been set for the current Recordset object, assuming that the underlying
provider supports indexes that is, then the Seek method will make use of this
and move the cursor to the desired position (which can be just before, on, or
just after the matching record).
A new service component called the Microsoft Cursor Service
for OLE DB has also been introduced. This facility attempts to introduce a
greater degree of standardisation among the underlying data providers by
providing consistent CursorType, Lock, and Sort properties regardless of
whether they are actually provided.
ADOX Library
ADO 2.1 also introduces a new, additional library that is
concerned with the manipulation of schemas rather than the data held therein.
ADOX, or the Microsoft ADO Extensions for DDL and Security to give it its full
title, is a set of management objects that facilitates the modification of a
schema definition in a standard way. Basically, as before, its an attempt to
provide a uniform means of doing a job against different back–ends.
Although this shares the same 2.1 version index as the rest of the ADO set, in
reality this is the first release of this specific library and so the
implementation isn’t as comprehensive as it presumably will be. By this I
mean that there are a significant number of back–end environments that are
not properly supported yet. In fact the only provider that appears to have a
complete implementation in this release is the Microsoft OLE DB Provider for
the Microsoft Jet Database Engine. The only other three that are directly
supported (Microsoft SQL Server OLE DB Provider, Microsoft OLE DB Provider for
ODBC, and Microsoft OLE DB Provider for Oracle) only have specific areas of
implementation that are documented in the accompanying readme file. In very
general terms these limitations are of the nature of being able to create new
tables but finding that existing table definition objects are read–only.
ADOX appears to be encapsulated within a single file,
msadox.dll, and should be referenced from Visual Basic as “Microsoft ADO
Ext. 2.1 for DDL and Security”. In selecting this entry you will also need
to select the main ADO library too.
The implementation is headed by the Catalog object. This
contains collections of groups, procedures, tables, users, and views, all of
which represent the schema of the data source. In order to get a Catalog object
instance up and running you can either directly create a connection to a data
source, a la:
Dim adoxCatalog As ADOX.Catalog
Set adoxCatalog = New ADOX.Catalog
adoxCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\My Documents\mydata.mdb"
or you can hitch up to an existing ADO connection
Set adoxCatalog.ActiveConnection = adoConn
Having associated the Catalog object to the data source you
can then perform management (i.e. non data manipulation) tasks, such as
assigning security permissions to user accounts, creating and deleting tables,
adding procedures, and so on. Creating a new table, for example, is a case of
creating a new table object, adding columns to it, and then adding the new
table object to the tables collection:
Dim adoxTable As ADOX.Table
Set adoxTable = New ADOX.Table
adoxTable.Name = "Books"
adoxTable.Columns.Append "BookId", adInteger
adoxTable.Columns.Append "Name", adVarWChar, 60
adoxCatalog.Tables.Append adoxtable
Things to come…
So that’s the run–down on version 2.1 of the ADO
set. Beta versions of ADO 2.5 are already widely available, for example they
are present in beta 3 of Windows 2000 (including the lightweight RecordSet
library and ADOX). This forthcoming release extends the reach of ADO both
towards the Internet and towards the Windows file systems. In the case of the
former there is direct support for URL–type addresses for connection
strings. This facility is added to tie–in with the underlying OLE DB
providers that already manipulate URLs, for example the OLE DB Provider for
Internet Publishing can work with HTTP addresses. In the case of the Windows
file systems, new Record and Stream objects provide the ability to work with
file system entities such as files and directories, and e–mail components
such as folders and messages. Finally, the stream objects allow for the
manipulation of binary and textual data. This can either be in the form of a
simple document, or something more complex like a COM structured document.
