Tech Friday: Bento database - First Look

Well, although I've managed to not worry about a database for several months, it finally happened and I need to keep track of my "opportunity matrix", that is, a list of grants, their deadlines and status, the responsible contact person, partners, and whether I've created all the necessary collateral: prospectus, project summary, grant application, etc.

Typically this would be done in Access on a Windows machine, and I've got Access 2007 installed in my copy of Parallels so that I could run this up pretty quickly.

But, since I want to stay native on the Mac, I poked around at an old favorite, Filemaker Pro. One thing I've always thought about FMP is that is relatively expensive, even in an academic edition, especially if you want to share the data using a server. But FileMaker now offers a "home" version called Bento for about $50.00, and this looks promising for my app.

I've downloaded the 30 day trial, and installed without fuss. Installation consists of dragging the the file to the applications folder. I started playing with one of the templates, and after ten minutes or so, I've ended up with the following data entry screen:



Points of Interest:

• Bento integrates with iCal, Mail and the Address book. You can eMail from a field which is designated an email field.
  
• One to many relationships are supported. For example, you can have a task list for a project, with multiple tasks displayed for a single project. Some relations are already connected; for example the tasks list from iCal can be embedded into a Bento form
  
• What one would consider to be a "database" in Access, or, loosely, a "group of tables" in another database program is called a "library" in Bento.
  
• What might be called a "recordset" in Access, or a "cursor" in an SQL database is called a "collection" in Bento. Collections are much like playlists in iTunes, they are a subset of records from the entire library.
  

You can create your own drop down list, so I've attempted to capture the workflow in a "status" field which currently contains the following:

Seeking Partner: Since virtually all my projects are with others, this is the first step in any application project.

Developing Project

Application Submitted

Awaiting Feedback from Funder (may be redundant with the previous step)

Under Revision

Revised Submitted

Awarded

Rejected

I was curious about the name, but I think it refers to a Japanese bento box, which are the compartmented dishes for serving Japanese food.

Here's a review of Bento in MacWorld. They point out a couple of limitations. For one thing, there is no way to export data in anything other than a comma delimited ASCII format.�

Another limitation is that the Bento data libraries are strictly single-user data files for a single machine. Anything larger needs to go into something like Filemaker. So, is is inadvisable to think that we could run a multi-user grant flow application using Bento. That's OK. For $50.00 we can play with Bento for awhile and work out the data that we need to keep track of. We'll be that much farther ahead when we're looking to move up.

Tech Friday: Amazon's Web Services - Database

Every so often somebody makes a prediction which at the time seems plausible, but maybe somewhat out in front of things. They always with start with the word "Someday..." For example:

• Someday, you will be able to go to a machine and withdraw money from your checking account.
  
• Someday, there will be a little box that knows where it is at all times.
  
• Someday we'll all buy our computing power just like we buy electricity.
  

So it was interesting to see an announcement by Amazon yesterday about the Amazon SimpleDB database, a sort of do-it-yourself pennies per hour Oracle database. Well, maybe not Oracle, but a substantial database back end that can be used to host a major application. Actually, the SimpleDB appears to be primarily a querying component; for hosting a large dataset, Amazon offers S3, the Simple Storage Service.

Just a quick browse around shows support for C# and Ruby-on-Rails, among other development languages. In addition, Red Hat is offering Red Hat Enterprise servers as part of the Amazon offering. Their FAQ about "cloud computing" is located here.

This is something to keep an eye on, perhaps the next logical step after virtualization of existing servers in your machine room. Why have any servers at all? Why have a machine room?

Looking Back: Database Development with Microsoft

Many careers require you to update or reinvent yourself on a regular basis. Expert programmers turn into beginners again every three years or so as their software tools, methodologies, and paradigms change.

So I certainly wasn't surprised when after several months away from the Microsoft Visual FoxPro page I ran across an announcement from (April 2007 no less):

We are announcing today that there will be no VFP 10. VFP 9 will continue to be supported according to our existing policy with support through 2015. We will be releasing SP2 for Visual FoxPro 9 this summer as planned, providing fixes and additional support for Windows Vista.

The oddly-named FoxPro has had a good run. I started using it I think around 1988 when I first took a job at the University of Vermont in their Continuing Education division, setting up Novell networks and maintaing a couple of FoxPro applications that had been written over the previous couple of months. FoxPro really started out as a complier for dBase code. DBase was one of the first, if not the first relational database programs created to be used with desktop computers. DBase, an interpreted language, was slow and quirky, but if I recall, I actually got a couple applications going with it. Some years after dBase was created, Clipper came out. Clipper could compile dBase code into machine language which could then be run natively on the computer without an interpreter. Clipper had no user interface to speak of, you still had to do the development in dBase, then take the dBase files and run them through Clipper by running batch files.

FoxPro was developed by David Fulton as an improvement over Clipper. It included a user interface for development and allowed you to create one for the end-user. It was less expensive than dBase or Clipper and had terrific performance. I started with version 2.0 right after it had come out, and by the time they were up to version 3.0 they had a program to create sophisticated user interfaces with overlapping windows. The programs would work in both Windows and Unix, and at one point there was support for the Macintosh.

More on this ancient history is available on the FoxPro Wiki.

[pause to take unsolicited spam phone call in heavily accented English from Ravi via what must be a bad VoIP connection to solicit IT services]

Fox Software was bought by Microsoft in 1992. For awhile they maintained a DOS version, but they were keen on developing a version for Windows. This appeared to be before Access or SQL-Server had any major marketing traction. There were other desktop databases, and Microsoft may have felt that they needed to have a dog (er, fox) in that particlar fight. In particular, one competitor was Borland Paradox, which had a terrific user interface and query system. Borland was also competing with development tools and languages.

FoxPro-for-Windows, renamed Visual FoxPro, became a major development system for deploying desktop database applications. Paradox never made an effective transfer from DOS to Windows, although it still exists in the WordPerfect suite.

FoxPro isn't dead though. There is a conference happening in October, and as the announcement says, there will be support until 2015. Version 9.0 will receive some updates to help it integrate well with the dominant Microsoft dot-net technologies. For interactive querying and data manipulation, it remains a wonderful tool.

Access 2007 Deployment File Formats

Notes from the Access 2007 help file:

There are four standard file formats for Access 2007 deployed files:

1. .accdb standard file format for Access 2007
   
2. .accde compiled binary file. This strips the VBA source code from the file
   
3. .accdc combined version of Access application file, and a digital signature associated with the file
   
4. .accdr format for running an application in runtime mode.
   

More on signing and creating the .accdc file:

I love this:

Note: Although this feature is also known as "packaging," it does not accomplish the same tasks as the Package Solution Wizard of the Access 2007 Developer Extensions. The feature described in this section packages an Access 2007 file and applies a digital signature to the package that helps indicate to users that the file is trustworthy.

So if I've got this right, I can use the Packaging Wizard (discussion from yesterday) to package and deploy an .accdc file which is a signed version of my access workstation file. Whew!

Access 2007 Packaging an Application

Now that the Access 2007 runtime is available, it is time to start working with Access 2007 again for client deployments. The first order of business was to start up the Developer Extensions.

The Microsoft Access 2007 Developer Extensions are available as a free download from Microsoft.

By default these are installed in C:\Program Files\Microsoft Office\. I changed this to add a folder Acc2007DE so I could find them, but even then couldn't figure out what was supposed to be happening.

Turns out that the Developer Extensions are are a COM add-in, consisting of:

Package Solution Wizard - this is similar to the older package wizard with Access 2003.

Save as Template - Allows you to save an existing database installaton as a template. Note that this is not the same as saving a database design as a template!

These appear under the round Microsoft Button in the upper left-hand corner of the Access Window.

(my screen shot programs don't appear to be able to deal with the dropped-down menu, so you'll need to use your imagination. )

In the following screens shots, click on the image to see a larger version.

If the Developer option does not appear under the button, do the following:
1. Click the button
2. Choose Access Options
3. Choose Add-ins
4. At the bottom of the screen choose Manage COM add-ins, then GO
5. You should get a screen showing the currently available add-ins. Like this:

Ok, so let's try the Package Solution wizard. Here is the first screen.

I changed the destination folder to F:\Access Install Packages.

The Package Solution Wizard asks for several parameters. Since I usually install an Access application off of the C:\ root, I chose System Drive (All Users) for the location plus the folder name in the Root install folder, and thent the actual folder name uner the Install Folder. The actual install location will show up in the Example install location field.

You can use the third option under t he section regarding the Pre-installation requirements if you want to install the Access runtime on a computer which does not contain Access 2007. To include this in your setup, you'll need to point to to a locall copy of Accessruntime.exe which you can download from the Microsoft web site.

Note under the Example Install Location, that the installed version of the program will have an .ACCDR extension, as opposed to the normal Access 2007 extension of .ACCDB.

The Acccess 2007 help files include a subset of subjects, called "Developer Reference"

Salesforce for nonprofits - Database Alternative

Salesforce is one of the most popular web-enabled databases, and it's gaining adherants among nonprofits. Accessible from any browser, customizable in myriad ways -- and available to nonprofits for no fee through the Salesforce.com Foundation (up to 10 seats) -- it's a powerful tool. But how can a database with a name like Salesforce be used by the nonprofit sector? This webinar will explore the functionality and community of Salesforce. We'll look at how several nonprofits, from a group of more than 1500, use Salesforce to cultivate and recruit donors, manage their electronic communications, and more. If you are looking for a new CRM solution, or just want to know more about Salesforce, this webinar will be a great starting point.
Presented by Rob Jordan, Idealist Consulting

Register now at http://nten.org/webinars.

Access Runtime is Back!

The Access 2007 runtime is available again. I had some problems installing this over the previous version. If the installation seems to hang up try deleting the existing version and then reinstall.

Access 2007 file formats are different, and new, and not entirely backward compatible.
Here is a help file which explains how they work.

Access Runtime: Never Mind!

Never Mind. Here is the post on Erik Rucker's blog.

Microsoft Access 2007 Runtime is now available

The Microsoft Access 2007 runtime is now available at this link. Hooray!

You also need to download and install the Developer Extensions to allow you to build an Access 2007 solution that includes the Access runtime.

After installing these, I was a little nonplussed, as I couldn't seem to find any of files to start the Packaging Wizard...which is the wizard to step you through the creation of a set of installation files for your Access application. This is now tucked under the "Developer" tab within Access 2007. There is no separate menu item off the Windows start menu.

Tangled up in Macros - Errors when opening Access Files

Eliminate the Macro Security messages when opening Access projects.

Here is a fix for a perennial problem, which appears to work.

I'll spare you the rant.

You need to run this on every workstation which uses your application, so in a networked situation, this needs to be part of the installation routine on each workstation.

Access 2007 Runtime ships May 15th

From Clint's blog... the runtime will be available as a free download on May 15th. Hooray!

Trackrecords: Client Outcomes Software Database

A few days ago I wrote about potential holes in non-profit record-keeping systems, specifically the problem of tracking program outcomes or client outcomes. Today I started looking around and with a quick Google search I quickly found a discussion of just this problem at TechSoup. Several people commented on the article, and gave examples of the systems they use. A quick click and I found myself at Track Records Software. This package, Track Records CM (client manager?) was designed for a service provider who provides counseling and training and assistive technology for clients recovering from brain injuries.

This is an unreview, I didn't actually run the software. Instead I walked through the online screencasts which give a pretty good idea of what the package can do. Some impressions:

• This is a web-based system. The screens and reports are pretty much plain-vanilla html-type forms. Reports are basic html tables.
  
• The system is client-centered.
  
• Staff members have a password and can be restricted to seeing "their" clients.
  
• You can "attach" another staff member's name to the client record. This allows the staff person to access that particular client record.
  
• You can make unrestricted log entries with a date and staff person who worked with that client.
  
• Monthly reports are available which is pulled for all transations per months.
  
• You can track goals and instructional data, and keep case notes.
  
• You can schedule a client, and record whether they kept the appointment or not
  
• You can schedule recurring appointments ("every week, Thursday at 10:00AM").
  
• There is a "document repository" which allows you to upload documents created or scanned from outside the system. These are held in a secure database which is subject to the same restrictions as the client records.
  
• They mentioned donations and pledge tracking, however, this wasn't demonstrated in the online screencast.
  
• There is a "lending library" function which allows you track materials on loan to clients.
  
• In keeping with the "outcomes" theme, there are fields and reports which track the placing of clients in job programs.
  
• On-screen reports have embedded links to allow drilling down for more detail.
  
• There is a very nice client record report which shows a summary of all activity related to the client on a single screen.
  

Things I'd like to know...

1. What is the back-end database, and what are the hardware requirements?
   
2. What is the cost of the system?
   
3. Is the source code available, or is it possible to make modifications, add fields, etc? There is a simple and more complicated query/report writer available within the system already which may be sufficient for end-users.
4. Is the system currently being enhanced?
   

The same vendor also has a payroll/staffing package.

Non-profit Databases

Some months ago I created a four page introduction to databases, Database 101 which explains basic database terminology. I found this was useful as background for discussion. But I think it is perhaps even more useful to take a longer view and think about the number and role of databases within an organization.

Perhaps we should even dispense with the word "database", and replace it with something a little less nerdy like "knowledge". We could ask several questions:

1. What are all the nuggets of information that we need on a daily basis to run the organization?
   
2. Who needs to know these things internally? (managers, clerical staff, service providers, your clients)
   
3. Who needs to know these things externally (funders, state and federal agencies, auditors, your accountant)
   
4. How are we going to get this knowledge on an ongoing basis?
   
5. How are we going to disseminate this knowledge?
   
6. How are we going to deal with confidentiality and "need-to-know"?
   

Data systems are usually built with one of two aims, either to report statistics from existing data, or to give real-time assistance in the daily running of a business (management information). Although funders and regulators have a myriad of reporting requirements, these may not be useful or helpful in the day-to-day running of your agency. Management information is not the same as statistical reporting. And yet the data system is often driven by the regulators and funders to the detriment of management information.

Three kinds of data

There are at least three kinds of data which are useful in managing an organization:

Financial: Income and expense accounts, grant and fund accounting, payroll, and purchasing

Donor and Constituent Management: These systems track friends and contributors outside the organization. Of course donations eventually are fed back into the accounting system...somehow...automatically, one would hope.

Service: Information on your service delivery is vital to understand and track the evidence of your effectiveness as an agency. This area is the weakest in terms of available low-cost and open source systems, partly because of the diversity of agencies, practices, and clients. While you may choose from several offerings that cover both the financial and fundraising areas, a service database may be more difficult to locate.

Subsets of the above

Do you provide training? Then you will want to have a training database which covers courses or seminars and which allows you to track instructors and students.You may need a registration function which tracks payments, and can accept credit cards.
Do you sell merchandise? Perhaps you need a web storefront.
Do you provide health care or counseling? You'll need a patient management and case management system.
Do you host events? You'll need to register participants, set up workshops, create "packages" which include combinations of paid and free sessions, prints schedules and account for the VIP luncheons, the gala banquet, and everyone's dietary restrictions and their hotel assignments.
Do you have equipment? You may need an inventory system which allows you to lend equipment to staff or clients.
Do you provide affordable housing? You may need to track housing projects, local grantors, real-estate and land transactions, easements abatements, and federal and funds which is being channeled through your organization to your grantors and builders.

Most of these ideas feed back into the service classification above, but all of them will have funds attached, and so they eventually feed into the financial system.

Since donor management and accounting systems are well represented by commercial offerings, we'll take a look at a few service applications in the upcoming weeks.

Books for Microsoft Access 2007 and earlier

I was going to do a list of my favorite Access books...but Erik Rucker already posted one. His blog, "What's new in Access 2007" has been going since late 2005. Erik is the program manager for Access 12.0 (aka 2007) at Microsoft. I expect when the runtime is announced it will appear here first. (To paraphrase the late-nite tele-marketing shill.."Programmers Are Standing By!)

Erik's annotation consists only of the publisher's blurb, not any kind of comments or review. Several of the books have publishing dates for April and later.

My own favorite books, which cover Access 2003 include:

Grover Park George On Access This book is especially good for those with limited database experience. GPG is a regular on the Utter Access forum, and even answers eMail questions.

Fixing Access Annoyances I wrote about this book previously, it is full of workarounds to pesky problems in Access 2003 and earlier.

Access Hacks

Microsoft Access Data Analysis

Finally, the other day I picked up VBA for Dummies, hoping for a little more detail about Visual Basic for Applications, the version of BASIC which is used when programming the Microsoft Office programs. The 5th edition actually deals with Office 2007, (i.e. The Ribbon), and I found several good tips.

VBA works for Outlook, Word and Excel, too, of course, and is especially helpful when you want to glue the applications together, by sending eMail from Access for example or using Word to create reports from Access.

Simulate Access 2007 runtime

Clint Covington has a hint to allow us to get a preview of what a project will look like when running under the Access 2007 runtime:

I know, the runtime hasn�t shipped yet. If you are looking for a simple way to share a database with co-workers and you don�t want them messing around with things� Try renaming the file to ACCDR. This is the equivalent of running the database with the /runtime switch. Basically the ribbon and nav pane get turned off.

I found a comment on a German blog quoting somebody who was quoting somebody that the runtime would ship "sometime in the March timeframe". Let's hope so.

Access 2007 Runtime

Well, I wish I was pointing to the Access 2007 runtime, but I'm not, however, this Microsoft page, discusses several points of interest:

1. The runtime will be available "shortly after the release to the general public of Microsoft Office Access 2007
   
2. The runtime and developer extensions will be free downloads.
   
3. The Extensions will include a packaging wizard, similar to the one for 2003, which optionally includes the runtime files, and any other files, necessary to create an MSI
   
4. The Developer Extensions will include hooks for Source Code Control
   
5. The Extensions will not include the Property Scanner or Customer Startup Wizard that were previously available in earlier versions.
   
6. Links to the download locations will be posted on Office Online and the Access Developer Portal on MSDN.
   

SQL Command Box for Microsoft Access

Among the many things I miss in Access from Foxpro is the ability to enter SQL statements on-the-fly into the command box. So, as a first cut, I created a form with a textbox and a button. The textbox holds the SQL code, and a button that calls a subroutine to stuff the code into a scratch query defined in the .MDB Queries collection.

There are lots of possible refinements, error checking, parsing of other commands, etc, but already I�m taken with this as it eliminates half the clicking around when doing SQL queries. Thanks to Martin Green's Office tips for most of the code. Here is the VBA code for the command button.

Private Sub cmdExecute_Click()
' The following code processes an on-the-fly SQL command
' entered in the text box. The command requires a "scratch"
' query be included in the database query collection. The code
' takes the SQL string, passes it to the scratch query, and
' then executes it.
' Code cribbed from Martin Green's Office Tips at
' http://www.fontstuff.com

Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

strSQL = Trim(Me.txtQuery.value)

'Here is the code which will apply the SQL statement to the query:

Set db = CurrentDb
Set qdf = db.QueryDefs("qryScratch")
qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing

DoCmd.OpenQuery "qryScratch"
End Sub

Look and Stuff in Microsoft Access

I think any database analyst or programmer has heard this common client request when doing mailing lists:

I want to be able to look up the organization data if the person is a member of an organization, and copy all of the relevant information into my mailing list. But I also want to be able to type in (or over) any existing organization information. And if the person isn't a member of an organization, or if the organization doesn't exist in the table, then I want to be able to add it right then...

So we stand in really odd positions to make this work, trying to relate the person table to an organization table and put together some kind of logic that works like QuickBooks (which no doubt cost $2.45 million to develop...)

This recently came up again, and because it turns out that the Organzation table gets replaced periodically, I gave up on the idea of relating the two tables, and just decided to duplicate the organization's fields in the person table. So shoot me.

If you press the "Look and Stuff" button, it brings up a subform which contains a single control, a combo-box pick list which picks an organization from the organization table.



The code behind this takes the field contents for the chosen Organization record and copies them to equivalent fields in the person table. The code:

Private Sub cmdFill_Click()
On Error GoTo Err_cmdFill_Click
Dim dbs As DAO.Database
Dim strSQL As String
Dim rstTemp As Recordset
Set dbs = CurrentDb

'Only do the following if the user has chosen a company 
If Me.cboCompany.Value > 0 Then
   strSQL = _
     "Select * FROM Company Where Company_id =" & Me.cboCompany.Value
   Set rstTemp = _
      CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

   'Stuff the results into the Mailing List Form
   Form_Maillist.txtOrganization = rstTemp![Organization]
   Form_Maillist.txtAddress = rstTemp![Address]
   Form_Maillist.txtCity = rstTemp![City]
   Form_Maillist.txtZip = rstTemp![Zip]
   Form_Maillist.txtState = rstTemp![State]

End If
'Close the chooser form    
DoCmd.Close
Exit_cmdFill_Click:
    Exit Sub
Err_cmdFill_Click:
    MsgBox Err.Description
    Resume Exit_cmdFill_Click  
End Sub

Find Non-Profit Buddies

And while we're on the subject of on-line databases...you can find non-profit organizations at TaxExemptWorld.Org
You can search by name, zip code, county or city. Full results of your search with enhanced data can be downloaded into a spreadsheet for $10.00.

One Book: Fixing Microsoft Access Anoyances

If I was allowed only one book about Microsoft Access, This is the one, Fixing Access Annoyances by Phil Michell and Evan Callahan. Just their discussion about Access' workgroup security is worth the price of the book. They cover mostly Access 2000-2003, with a few notes regarding Access 97. The book includes a fair amount of VBA code, which can also be downloaded from their web site.

The book covers a lot of ground and includes internet resources. One of the best of these remains the community web site, UtterAccess.