Welcome to Larkware's monthly newsletter for Microsoft Access developers.

*********************************************************

ACCESS DEVELOPER NEWS

WHAT IS THIS?

This would have been issue 3.8 of Smart Access Extra. However, Pinnacle has cancelled that newsletter (and several others), and they didn't inform me in advance. Because I'd already written it. I'm sending you this newsletter myself.

I don't, unfortunately, have access to Pinnacle's subscriber list. So I'm starting from my own list of people who have answered surveys or e-mailed me in the past. I realize that some of you are getting this who are not current subscribers. My deepest apologies; you won't get any more from me in the future unless you act.

In fact, IF YOU WANT TO KEEP GETTING THIS NEWSLETTER, YOU NEED TO OPT IN TO THE NEW MAILING LIST!

To subscribe to Access Developer News, visit:

If you don't visit that URL and put in your e-mail address, you won't get another issue!

So, if you want to see this newsletter continue:

1) Visit that URL and subscribe

2) Please let anyone else who might be interested know about this! I'm losing 90% of the mailing list here instantly, and would like to get at least some of those people back.

And now, on to the newsletter...

Mike Gunderloy

---------------------------------------------------------

TABLE OF CONTENTS:

1) Missing the Boat Twice
2) A Blast From the Past
3) Survey Results
4) Speed Ferret 4.1 Ships
5) SQL Corner: Miscellaneous DDL
6) On the Web
7) SQL Server Security Rollup
8) KB Article: Unreplicate a Database
9) Tip: Keyboard Shortcuts
10) Reader Mail

---------------------------------------------------------

---------------------------------------------------------

MISSING THE BOAT TWICE

---------------------------------------------------------

MSDN has had a couple of interesting articles on Web Services and Office lately that are just not quite what we Access developers would like to see. But they're worth mentioning here anyhow, because they may get your creative juices flowing.

/html/odc_accmplxtype.asp) shows how you can hook up to a Web Service from Access and how you can parse data that comes back from the Web Service. But even though it says "complex", the types in this article aren't all that complex; they're basically just multi-field structures.

Second, "Working with ADO.NET Datasets in Microsoft Office"  shows how you can take a DataSet from a Web Service and consume it in Office - by turning the DataSet into an array and displaying it in Excel!
Click Here to read

Of course, what I'd really like to see is code to turn a DataSet delivered by a Web Service into a set of related Access tables. But apparently no one at Microsoft has thought to write that one yet.

---------------------------------------------------------

A BLAST FROM THE PAST

---------------------------------------------------------

You'll find a listing of every version of Access ever produced, starting with Access 1.0 for DOS (which was not a database product). Even more amazingly, click through the links and you'll discover that you can still buy copies of this obsolete software (with the caveat that licensing is up to you).

Exploring the EMS site reveals a host of other old versions of software for sale. I can see times when this would be a very useful site to visit...though I'm not sure I'll ever be in the mood to pay $495 for a boxed copy of SQL Server 4.21.

---------------------------------------------------------

SURVEY RESULTS

---------------------------------------------------------

You may recall that I tried something a bit different for last month's survey. The goal was to build up a list of Access solutions that had actually been implemented, so that the community would have some reference points to fight the "Access is a toy" crowd. I didn't get a whole lot of responses (I expect because most people don't care to take the time to share this sort of thing), but the ones that I did get show the breadth of uses of Access.

"An application to support high school business managers and school officials to manage textbook inventory and registration procedures such as fees and payments,lockers, bus passes etc." 30MB of data in Access 97.

An Access 97 solution: "Fleet Invoicing and managment. Program handles the invoicing of a very large hire company (800 items of earth moving equipment and 700 trucks). Program also provides management reports on things such as utilisation and projected hires for next financial year. Program provides a high degree of flexibility in querying data about specific types of items and/or customers."

Another Access 97 solution, this one handling 40 MB of data and 15 simultaneous users: "I developed an application for a nonwoven textile plant that handles receiving, production, converting, lab testing, downtime reporting, inventory control, and shipping. It pretty much runs the plant except for the financials. I did not want to reinvent the wheel in writing a financial package. I also wrote stand alone applications for training, human resources, and needling." Biggest challenge: "Getting management to stop changing the requirements."

This one's in Access 2000, using replication between 4 sites connected by a WAN: "An Environmental Issues Register that records actual and potential issues, incidents, locations etc. for an energy company. The solution required a rebuild of an existing application (don't they all...?!?), re-analysing the data structure and business processes, remodelling them in Access, performing the usual data transfer and cleansing, building a UI that conforms to standards, creating a versioning process (head office / power station) that allowed each data set to be the same to ease synchonisation whilst restricting user access, writing a bunch of operational and functional reports, writing a user manual and an admin manual, working out the delights of replication/synchronisation (hell, it's good...when it finally works!). Then stopping for lunch!"

An Access 97 document management system: "I noticed that most of our documents were just plain text. I put the template in an Access memo field. I set it up so that the users put in their own text by enclosing the variable info in [] like [Enter Amount Here]. If they need a field from our customer table, they just have to reference that field like [Location] and the system will go and get that info. I also created some generic functions like [Date], [FullName], [FullAddress]. Now all of our correspondence with our customers is in one Log table that everyone in the office can use. Here is a neat byproduct I didn't expect: Our CEO is now sending dictation to be typed in "Document Print". When he gets the letter back to check and sign, he makes the changes in "Document Print" and re-prints it. It doesn't have to go back to the secretary."

Over 100MB of data in Access 2000: "Complete Receiving, Purchasing, Production, Shipping system. Communicates to AccPac for retreiving Sales Orders, Item Master, etc. Also sends production information to AccPac. Communicates directly with TEC Label printers using Printer Code for speed and reliability. Retrieves weights from varies scales. The program has been running for almost 2 years with no major issues."

200 users on an intranet application: "The solution was a report server that ran MS Access on the server and created reports to a file (either Rich Text Format or Snapshot). The user created report request from IE using ASP. A small vp program running on the server would detect this request and launch MS Access to produce the report. Each report request would have it's on instance of access running.

Access 97 at work: "This was a Time Tracking application for temporary and regular employees doing product testing on multiple products." Greatest success of the system: "That it is still being used 18 months after they tried to replace it with a comercial product."

Another Access 97 application: "It was a work order tracking system for a large cable installation contractor. It allowed the dispatcher to generate run sheets for the following day, enter the call codes (work performed) in the W/O to track income (from the cable company) and expenditures(what they were paying their installers). The entry screen was a single form to allow the dispatcher enter the information quickly while the installer was on the radio."

Thanks to all of you who generously shared details for the benefit of other developers. I note that once again Access 97 is the "sweet spot" for many successful developers. I wonder what it would take for a new version to displace it in your hearts and minds?

---------------------------------------------------------

SPEED FERRET 4.1 SHIPS

---------------------------------------------------------

There's a new ferret on the block. More accurately, a new version of Speed Ferret, an excellent utility that started out in Access and is now an Access/Visual Basic/SQL Server search and modification tool. You can check the value of a single property across all objects in your database - handy if, for example, you want to make sure all form background colors are the same. You can change a field name in a table and have this fixed up everywhere the name occurs in a set of Access, VB, and SQL Server projects. And of course you can do search and replace in text, as well as in any object property.

---------------------------------------------------------

SQL CORNER: MISCELLANEOUS DDL

---------------------------------------------------------

there's even a collection of all the SQL Corner columns as a single file

there) discussed the CREATE TABLE statement in Access SQL. This is the first data definition language (DDL) statement that I've tackled in this space. As a reminder, DDL is the part of SQL concerned with creating and altering objects (as opposed to Data Manipulation Language, DML, which is concerned with actually retrieving and altering data). Now it's time to take a look at some of the other DDL statements that Access supports.

Access does not have a particularly rich set of DDL statements, especially as compared with high-end database systems such as SQL Server. That's because the Access GUI has been the primary means for creating objects, with DAO/ADOX being secondary and SQL DDL statements bringing up the rear. But there are a few statements available that you may find useful:

CREATE INDEX

ALTER TABLE

DROP TABLE

DROP INDEX

The CREATE INDEX statement is used to add an index to an existing table. For example, you could add an index to the Northwind Customers table with this statement:

CREATE INDEX CompanyIndex

ON Customers (CompanyName)

To index multiple fields, you specify all of the field names:

CREATE INDEX ContactIndex

ON Customers (ContactName, ContactTitle)

To disallow duplicate values in the column or columns, add the UNIQUE

keyword:

CREATE UNIQUE INDEX CompanyIndex

ON Customers (CompanyName)

You can use the IGNORE NULL clause to keep Jet from making index entries for rows with Null values in the indexed field (useful if there are many nulls and you want to search for actual data):

CREATE INDEX CompanyIndex

ON Customers (CompanyName)

WITH IGNORE NULL

Or you can prevent Null values from being entered at all (similar to the Required property of a field) with the DISALLOW NULL clause:

CREATE INDEX CompanyIndex
ON Customers (CompanyName)
WITH DISALLOW NULL

Finally, you can specify that an index be the primary key of the table (of course, this will only work if the table does not already have a primary key):

CREATE INDEX CompanyIndex
ON Customers (CompanyName)
WITH PRIMARY

The ALTER TABLE statement is used to alter the design of an existing table. You can perform several different operations with ALTER TABLE. First, you can add a column to a table:

ALTER TABLE Customers
ADD COLUMN NewColumn TEXT(50)

You can only add one column in a single ALTER TABLE statement. You can also add a constraint (see Smart Access Extra 3.4 for a discussion of the CONSTRAINT clause):

ALTER TABLE Customers
ADD CONSTRAINT NewColumn UNIQUE (NewColumn)

If there's a constraint you don't need any longer, you can also use ALTER TABLE to get rid of it:

ALTER TABLE Customers
DROP CONSTRAINT NewColumn

And as you might expect, you can also drop a column:

ALTER TABLE Customers
DROP COLUMN NewColumn

The final DDL statement that's supported by Jet is the DROP statement. This comes in two forms. DROP TABLE is the inverse of CREATE TABLE:

DROP TABLE Table1

And of course DROP INDEX is the inverse of CREATE INDEX:

DROP INDEX CompanyIndex ON Customers

Next time I'll talk about some of the additional DDL commands that are supported by the Jet 4.0 ANSI-92 extensions.

---------------------------------------------------------
ON THE WEB
---------------------------------------------------------

) has nothing to do with computers. But boy, does it look like fun. Just don't tell my kids that other dads are this cool.

---------------------------------------------------------

SQL SERVER SECURITY ROLLUP

---------------------------------------------------------

Microsoft has issued another cumulative security patch for SQL Server. There are versions for SQL Server 7.0, MSDE 1.0, SQL Server 2000, and MSDE 2000.

Quite a few security holes have turned up in SQL Server in recent months. These range from ways to crash the server to ways for any guest user to hijack the Windows account under which SQL Server runs. Microsoft rates this patch as "moderate" severity, but my own feeling is that if you have a SQL Server or MSDE installation connected to the Internet (or another network where you don't trust anyone), you should definitely apply this patch.

Be sure to read the readme file. SQL Server patches are not as easy to use as some other product patches; you'll need to stop the server and manually copy some files around. Pay attention when it tells you to make a backup, too, just in case.

---------------------------------------------------------
KB ARTICLE: UNREPLICATE A DATABASE
---------------------------------------------------------

Ever turn on replication in a database and then wish you hadn't? Replicating a database makes all sorts of changes to it. Well, if you need to reverse the changes, you can refer to KB article Q290052, "ACC2002: How to Make a Replicated Database a Regular Database". This article will give you the basic steps to get back to your starting point, which can be very welcome when you need it. The article also links to an Access 2000 version.

---------------------------------------------------------
TIP: KEYBOARD SHORTCUTS
---------------------------------------------------------

If you watch experienced developers, you'll often see that they leave the mouse sitting right where it is. That's because there are keyboard shortcuts for almost everything, and after a while you get to know them. Access has a raft of special keys designed to help you work more efficiently. Try these shortcuts in form design view, for example:

F4 or Alt-Enter - Switches the focus to the property sheet
F5 - Switches from design view to form view
F7 - Opens the Choose Builder dialog box
F8 - Opens the Field List
Shift+F7 - Switches the focus from the property sheet back to the form
Alt-F11 - Switches to the Visual Basic Editor

---------------------------------------------------------
READER MAIL
---------------------------------------------------------

Nic Smith writes:

I've just finished programming a small routine when I noticed what I was actually typing.

I'd created a boolean variable which I store the Enabled value of controls. Using 3 characters (bol) to dstinguish it as a boolean field I noticed that that the variable name was bolLock. I was typing bollocks....Has anybody else created unusual or funny variable names?

---------------------------------------------------------
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 newsletter.

Vendors & authors: If you've got an Access-related book or product to promote, I'd be more than happy to take a look at a review copy. Just drop me a line.

Mike Gunderloy, Editor

ACCESS DEVELOPER NEWS

This eNewsletter is brought to you compliments of Lark Group, Inc.
Copyright(c) 2002. All rights reserved.