*********************************************************
Welcome to Pinnacle Publishing's free monthly eNewsletter for professional developers. Thank you for
requesting this service from our online sign-up form.

*********************************************************
SMART ACCESS eXTRA
Microsoft Access Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com
Issue 3.5 May 23, 2002

*********************************************************
TABLE OF CONTENTS:

1) Another Month, Another Update
2) Web Services in Access: A Worked Example
3) Survey Results
4) Developer's Bookshelf: WRITING SECURE CODE

---------------------------------------------------------
Back issues of SMART ACCESS eXTRA and previous survey
results are available at
http://www.larkfarm.com/sax/smart_access_extra.htm .

---------------------------------------------------------
1) ANOTHER MONTH, ANOTHER UPDATE
---------------------------------------------------------
We may have reached the point where it's completely impossible to determine what version of which files are
installed when you're running Microsoft Access. The latest evidence: Knowledge Base article Q321076, "Updated
Version of Jet Replication Files Available." You can read this at
http://www.uptilt.com/ct.html?s=5bo,k44,4o2,1zm0,kc45,1e8r,f7ou

if you like. The gist of it is that they apparently forgot to ship two files with Jet 4.0 Service Pack 6, so
there's a little download that you can grab to install them. However, the files WERE in Jet 4.0 Service Pack 5.
So, unless you went straight from SP4 or earlier to SP6, you should be fine.

And also on the subject of obscure updates: The Universal Data Access site now has the "MDAC 2.7 RTM Refresh." Even
after reading the site, I'm not sure whether there's anything more to this one than a change of version
numbers for some files; if you'd like to look for yourself, check out http://www.microsoft.com/data/download.htm.

Between service packs, security updates, refreshes, bug fixes, QFEs, and who knows what else, you could probably
configure 500 different versions of the data access files on a Windows computer without half trying. That's too many.
Unfortunately, I don't see any break in this pattern until we get some new drivers written from the ground up,
and probably not even then.

---------------------------------------------------------
2) WEB SERVICES IN ACCESS: A WORKED EXAMPLE
---------------------------------------------------------
If you're still puzzling about how to integrate XML Web Services with Microsoft Access (assuming you care, that
is), there's a new article on MSDN that will help. "How to Create and Deploy XML Web Services Using Visual Studio
.NET and Office XP" http://www.uptilt.com/ct.html?s=5bo,k44,4o2,jd1a,m88d,1e8r,f7ou
is from Microsoft's Frank Rice. Using Visual Studio .NET, Office XP, and the Office XP Web Services Toolkit, he
walks you through the entire process, from understanding the architecture to building both the server and the client.

So, if you can do this today, why am I still pushing for full .NET integration in the next version of Office?
Because, as it stands, Office is positioned only as a *consumer* of Web Services. Those of us who launched our
development careers in Access would really like to see Access as a *supplier* of Web Services as well. After
all, some huge number of Web Services are going to offer data as their product. Right now, it's fairly easy to
build those in Visual Studio .NET, using the ADO.NET data objects. But imagine what a future version of Access
could bring to the table: a Web Services Wizard resembling the current form, report, or data access page
wizards, that could produce a Web Service with minimal user intervention. Now *that* would be cool.

Well, I can dream, can't I?

---------------------------------------------------------
3) SURVEY RESULTS
---------------------------------------------------------
Last month, I asked you about database size. For a change, the questions were pretty simple, so here's how
the numbers came out:

What's the largest number of objects (tables, forms, reports, and so on) you've ever worked with in a single
Access database?

11-50 -- 11
51-100 -- 21
101-500 -- 74
501-1000 -- 26
Over 1000 -- 12

How large is the largest Access database you've ever worked with?

0-100KB -- 2
101KB-1MB -- 7
2MB-10MB -- 23
11MB-100MB -- 60
101MB-1GB -- 40
Over 1 GB -- 12

How large is the largest back-end database you've ever worked with from Access?

0-1MB -- 9
1MB-100MB -- 45
101MB-1GB -- 48
2GB-100GB -- 25
101GB-1TB -- 7
Over 1TB -- 2

Have you "hit the wall" in terms of database size and been forced to switch from Access to another database?

Yes -- 35
No -- 109

Not surprisingly, there are some pretty large databases out there; you folks do an adequate job of disproving the
perennial slander that Access is only suitable for tiny jobs (and I'm jealous of those of you playing with those
terabyte databases). And there was a bunch of interesting comments, too. Here's a sampling:

"I have had good success using a 100-200MB Access database with about three concurrent (with lots of
activity) users. We had both the backend and frontend (both MDBs just split out) on a file server. Although
probably not directly related to the size of this database, we had a series of about 40 nested action
queries that we needed to run sequentially to process a batch. These were initially taking from 5-10 minutes each
to run (yes, each query). We switched from using nested queries to creating temporary tables and then joining
them appropriately, and this reduced the time to run to between two and 15 seconds each. We deleted (using code)
each of the temporary tables following each batch and averaged about five batches per day, and did not
experience much database growth due to the use of these temporary tables."

"I have developed many very large databases and have always got excellent results (includes real-time
applications such as reporting train locations for all trains in country area of New South Wales). Having
started developing database applications in 1972, I am astounded at the stupid way people go about designing
systems today -- they don't understand how to do a lot with a little (including comms rates of 300 baud). Access
97 is infinitely more powerful than the mainframes I used to work on, and is infinitely more stable. So let's cut
the crap about Access being a toy and forcing people to use SQL Server or Oracle. I haven't found anything I
can't do better with Access than other programmers working on 'industrial strength' databases."

"I am wondering why Access cannot go larger than 2GB or handle more users? Is Microsoft trying to force us to a
more expensive product? I do a lot of rapid development, and SQL Server is just not fast enough to develop in. In
addition, I cannot find the speed advantage using SQL Server. When running a query off a two million record
table and performing summation in the query, there was only a two-sec difference in speed. For the majority of
applications, Access is more than enough database. Sometimes you have to link several databases together to
handle the number of records you need, but that is not a big deal."

"In process right now. Replication gets worse as the size (both number of objects AND amount of data) of the
database grows. Also the larger the number of objects, the more strange things happen. Example when adding a new
record, the Memo field won't hold more than 1,500 characters, but previous records hold 4,000 characters
(yes, we have done the rebuild, compact, compress, held our collective breath, and most recently waited to do
these things until the proper phase of the moon was upon us). And, of course, the stability of the MDB (mostly
with Access 97) continues to degrade the larger the front-end database gets. And we are not talking about
near one-gig in size."

"I find it a bit weird that every time I make a small change to the database design, the database size grows by
some Mbs. I mostly configure Access to compact the dbase when closing Access, but why is this an option? I would
rather say it's a must!!"

"The problem with large databases in Access is that routine activities -- especially queries -- may take
hours to complete compared to seconds in other database products such as Visual FoxPro."

"Pushing a million rows over a network was NOT a good idea..."

"My current database has 1,341 objects (61 local tables, 60 linked tables, 879 queries, 75 forms, 178 reports, 10
macros, 78 modules). Compiled and compressed, it occupies 20 Mbytes. It is a front-end analysis and reporting
package for accounts data read through ODBC, so it has no significant data storage (just a few megs of
configuration data). I am worried that I am approaching Access's limitations but have no way of predicting when I
will hit 'the wall.' I am very pleased with the way it has coped so far." Many of you apparently prototype in Access but then
switch to an SQL Server or Oracle backend for production. I note that some of you switch when data is at 10MB, some
at 100MB, some at 500MB or more.

Now for this month's survey: I've been asked by the Access team at Microsoft to do another survey to help
them with future directions for the product. Congratulations! You folks have become a valued source of
opinions. This time around, the topic is reporting -- what do you like about Access reports, and what's
missing? Come over to http://www.larkfarm.com/sax/survey.asp to participate.
I'll be giving away a copy of ACCESS 2002 ENTERPRISE DEVELOPER'S HANDBOOK to one random respondent.
Congratulations, by the way, to Jan Borup Coyle, who was the random winner from last month. But wait; there's
more! Because this is an "official" survey, Microsoft is going to throw in one copy of MapPoint 2002 for every 75
respondents. See you there!

---------------------------------------------------------
4) A DEVELOPER'S BOOKSHELF: WRITING SECURE CODE
---------------------------------------------------------
WRITING SECURE CODE, by Michael Howard and David LeBlanc (Microsoft Press, 2002)

This book contains not a single line of Access code, or even of VBA. Nevertheless, I think it's essential reading
for developers in this security-conscious age. Once you get over giggling at the proximity of "Microsoft" and
"Secure," you'll find that this is quite a good book from a pair of guys who have put in much time trying to help
teams at Microsoft write more secure code.

The book takes a solid technical look at things like writing security-conscious C++ code, the right and wrong
way to implement cryptography, and protecting against denial of service attacks. But even if you're not going
to twiddle books at this level, the "security first" philosophy preached by this book is worth absorbing. It
really does boil down to a few simple things: think about security, ship in a secure state by default, and stay in
touch with the field. But it's the details that will kill you, and those details are well-covered here.

If you write code that ships to customers, read this book.

(Got a favorite software book of your own that you'd like to see in this space? Let me know, and perhaps I'll cover
it in a future issue!)

---------------------------------------------------------
IN THE MAY ISSUE OF SMART ACCESS...
---------------------------------------------------------
Have you tried our monthly print newsletter, SMART ACCESS? Here's just one of the articles you'll find in
the current issue. You can check it out (and learn more about SMART ACCESS) at
http://www.smartaccessnewsletter.com.

You'll find a variety of articles from the print newsletter on the Web site. Right now, for example, you
can read Danny Lessandrini's "Create XML Web Reports from Access 2002," showing how you can use new methods in
Access 2002 together with the power of XSL to publish data to the Web.

(If you're not yet a subscriber to SMART ACCESS, see the bottom of this eNewsletter for information on how you can
get a FREE three-issue trial!)

---------------------------------------------------------
6) ON THE WEB
---------------------------------------------------------
Some more miscellaneous finds from my prowling around the Internet. Space precludes my listing everything that I
find here; if you're interested, I post more frequent notes and pointers over at http://www.larkware.com/ .

TightVNC (http://www.tightvnc.com/) supplies a faster than usual client for the popular VNC remote-control
package, by compressing parts of the desktop. Lossy compression is available if speed is more important for
your application than fidelity.

The WMI Extensions for Visual Studio .NET
(http://msdn.microsoft.com/downloads/default.asp?URL=/downloads/sample.asp?url=/MSDN-FILES/027/001/675/msdncompositedoc.xml)

give you an easy way to integrate your .NET projects with WMI, including a real-time interactive browser and easy
class generation.

Ghost-It (http://home.rochester.rr.com/artcfox/GhostIt/) lets you turn windows transparent under Win2K/XP. Useful
when you want to keep an eye on something without having it take up its own screen real estate.

Glint (http://scitechconcept.com/glint.html) is a freeware system activity monitor that presents lots of blinkenlights.

Anakrino (http://www.saurik.com/net/exemplar/) will disassemble .NET MSIL to C# code.

Developers may find the page of random quotes at http://www.habets.pp.se/quotes/english.html rather amusing.

And, finally, when you're ready to get out of the house for a while, check out PowerSkip (http://www.powerskip.de/mainpage.html).

---------------------------------------------------------
7) LITESPEED BACKUPS FOR SQL SERVER
---------------------------------------------------------
SQL LiteSpeed is a new software package designed with one thing in mind: fast backups to disk of SQL Server 7.0 or
2000 databases. Implemented as a set of extended stored procedures with a syntax very similar to existing SQL
Server back-up commands, SQL LiteSpeed compacts data as it's being backed up. This means that backups take up
less disk space, but, more importantly in many cases, it means that they can be completed sooner. If you're
dealing with large, active databases, that can be a significant benefit.

There are other great features here as well: cluster support, instance support, actual 128-bit encryption for
back-up files (native backups contain all of your data in clear text), and less blocking of database operations
that are going on at the same time as the backup. I could've really used this one when I was working with the
giant online database last year, where we did backups in the middle of the night and just prayed that not too many
users would be affected. Data compression (on the order of eight to one or better, depending on the data) is a
nice bonus here as well.

You'll find the SQL LiteSpeed Web site at http://www.sqllitespeed.com/slsdefault.asp, where you
can download a 15-day trial version. Pricing starts at $495 for a version that supports databases up to 5GB.

---------------------------------------------------------
8) KB ARTICLE: BIND DAPs TO XML
---------------------------------------------------------
The full title of MSKB article Q281863 is "HOW TO: Bind a Data Access 2002 Page to XML." Given the increasing
ubiquity of XML in our lives, that seems like a pretty good thing to get a handle on. The article shows you two
different techniques. One binds the DAP to embedded XML (also called an XML Data Island). The other binds the
page to an external XML file. If you're using XML at all, you can probably think of reasons why you need to know
this. If not, you might want to play with the techniques here and see whether they spark any interest. You can
only hide from XML for so long...

(You can read this article online at http://support.microsoft.com/Default.aspx?scid=kb;EN-US;q281863.)

If you're using Internet Explorer 5.0 or above, and you're in the USA or Canada, just type "MSKB 281863" into
the address bar and hit Enter. After a bit of churning around, the browser should be showing you the Knowledge
Base article.

---------------------------------------------------------
9) TIP: WHEN IS A CHANGE NOT A CHANGE?
---------------------------------------------------------
If you open a database created by one version of Access in another version (say, opening an Access 97 database in
Access 2002), you'll be warned that you can't change any objects in the database unless you convert the database
to the new format. It turns out that that's only part of the story. If all you want to do is see the results of an
ad-hoc query, go ahead and open the database without converting it. Then open any existing query in design
view. You can make any changes you like to this query, including removing or adding tables, and run it to see
the data. You just can't save the changes. But, if you're just hunting for data, this is a lot faster than
converting the database first.

---------------------------------------------------------
10) READER MAIL
---------------------------------------------------------
Ken Pearce wrote to follow up on his question from last time:

I was able to resolve the Link and Import issues by following the guidance from Knowledge Base article
Q242218: "Nothing happens when you try to start a Microsoft Access Wizard."

By carefully uninstalling and reinstalling both versions (several times) on both computers, I was
finally able to resolve the issue by "stumbling" across the Windows Installer Shortcut option
suggested in the KB article. (When I first read that option, I thought they must be joking!) Apparently,
once both versions were installed with the "proper" shortcuts from their respective program groups (as
per the article), all features worked fine. It's not like I had a choice of how to create the shortcut in
the program group during the installation. Or did I? Go figure?!?

Julie Seiger writes with a warning of a serious bug that has bitten people from time to time:

Here's a good Microsoft article for you to check out -- Q304548! I recently had Visio 2002 loaded on my
PC. The rest of my Office products are 2000. I was copying and pasting object in Access 2000 and then
closed the database. The next day, I opened it and got the following message when I tried to get to the
code window: "Error accessing file. Network connection may have been lost." After researching the
problem at the Microsoft Web site, I found that Visio 2002, and other members of the Office XP family,
update the Vbe6.dll to version 6.3.91.8. If this version of the .dll exists on your machine and you
copy objects in Access and close the database before you compile, your database will be ruined. According
to the article, "At this point, you have to revert to a known good back-up copy of the database." Not a
very pretty bug!

Isaac Mosseri writes with a problem to which I don't have the answer. Any ideas?

I have tried to print a report containing both text tables and pictures on a Xerox 750 and Canon 6100 jet
color printers, and, in both of them, I don't receive the printed material in the same colors as I see in
the preview screen. Most of them were black or sometimes red mixed with black monocolor.

I tried in same places with the same printer drivers to print the printer test page or a Word document and receive the right colors.

Is there a way to print in color from Access??

Gustav Brock passes on some experiments with the Nz() function:

Did you know that -- when used to return an expression in a query -- Nz() always returns a string
even if it's supposed to not do so?

Like this:

Expr1: Nz([fldNumeric])

or:

Expr1: Nz([fldNumeric], 0)

If you need a numeric value, you'll have to wrap it
in Int():

NumExpr1: Int(Nz([fldNumeric]))

Even if fldNumeric is a long, this will return a long when fldNumeric is not Null but an integer for Null.
If you wish a long in any case, the zero for Null values for some reason must be present:

NumExpr1: Int(Nz([fldNumeric], 0))

Alternatively, Nz() can be replaced with the good old
IIf() construction:

NumExpr1: IIf(IsNull([fldNumeric]), 0,[fldNumeric])

This is tested for Access 95, 97 and 2000. Don't know about 2002.

---------------------------------------------------------
UNTIL NEXT ISSUE
---------------------------------------------------------
I welcome your input, tips, suggestions, Web sites, and other Access news. If you send me something, please let
me know whether I can use your name with your comments. I can't offer personal replies to all questions, due to the
volume of your mail, but I'll get to as many as I can in the eNewsletter.

Mike Gunderloy, Editor
SMART ACCESS eXTRA

Feel free to forward this eNewsletter to your colleagues,
and tell them they can sign up for it at
http://www.FREEeNewsletters.com.

---------------------------------------------------------
TRY US FREE!

If you like this eNewsletter, you'll love SMART ACCESS
monthly newsletter!

For a FREE trial subscription to SMART ACCESS, visit http://www.pinnaclepublishing.com/SubscribeSA.

Smart Access is a monthly 24-page newsletter for developers who use Microsoft Access. Each issue offers
insightful articles, undocumented techniques, and links to additional useful content. This is a highly technical
newsletter, the kind most subscribers want to keep. Now in its seventh year, the newsletter's writing style makes
technical information accessible to the typical Access developer. Smart Access is a "must-read" for anyone who's
serious about developing with Microsoft Access, with articles by the industry's experts. Smart Access articles
are frequently quoted, recommended, and referred to by participants in the Access user groups. Essential reading
for anyone who wants to get the most out of Microsoft Access.

GET A FREE TRIAL SUBSCRIPTION NOW! http://www.pinnaclepublishing.com/SubscribeSA

---------------------------------------------------------
OTHER NEWSLETTERS BY PINNACLE PUBLISHING

View our entire list of high-quality developer newsletters, published monthly by Pinnacle Publishing.
http://www.pinnaclepublishing.com

---------------------------------------------------------
INTERESTED IN BEING A SPONSOR?

Email Howard Flint: mailto:hflint@pinpub.com

---------------------------------------------------------
PINNACLE EMAIL SUBSCRIPTION SERVICES

SMART ACCESS eXTRA
To unsubscribe, send an email to: unsubscribe-6050@uptilt.com

This eNewsletter is brought to you compliments of Pinnacle Publishing, Inc. Copyright(c) 2002 http://www.pinnaclepublishing.com All rights reserved.