Sunday, November 29, 2009

Now, Where Did THAT Come From?

I recently started thinking about setting application properties a little differently than I had been. Well, actually, it's been a while since I really gave it much thought at all, to be honest. A long time ago, I created a module with default constants like "MBTitle" and "AppTitle", and started importing it into new accdbs or mdbs. Then I'd edit the values for the new database. It works, but it never really made me completely happy. Something about hard-coding values like the support phone number seemed less than appropriate. But I couldn't really think of a better approach, and just stopped thinking about it.

Well, for some reason, it recently occurred to me that it might be better to make this a table-driven function. Actually, I'd thought of that once before, but my implementation wasn't all that creative at the time and I had let it slide again.

This time, though, it dawned on me that I could create a Hidden User System Table for my application parameters and retrieve them as needed with a simple little function. If I distributed the app as an mde it would be easier to update values, such as Application Version, when necessary, with having to redo a module. There is still no guarantee a knowledgeable user won't figure out how to get to my hidden table, of course. But if you’re going to work with Access-built applications, you have to learn to live with that.

So, here’s a screen shot of my USysValueList table with some typical properties and values.

Here's the function that retrieves values from the Hidden User System Table:

Public Function UTV(strField As String) As String
'Retrieves values for various database properties from a User System Table
On Error GoTo errHandler
UTV = DLookup("UsysValue", "USysValueList", "USysValueList.UsysField ='" & strField & "'")
Exit Function
MsgBox "Error in UTV():" & Err.Number & ":" & Err.Description
Resume ExitProc
End Function

Here's an example of a VBA procedure that would use properties from the table to populate controls on the Main Menu of the application when it opens:

Private Sub Form_Open()
Me.Caption = UTV("AppTitle") & " - [Main Menu]"
Me![lblTitle] = UTV("AppTitle")
Me![lblVersion].Caption = UTV("AppVersion") & " D" 'D for "demo"
End Sub

The next step is to create a new function so that I can start saving user preferences into this table. I'm thinking of things like storing preferred date formats ("mm/dd/yyyy" or "dd/mm/yyyy"), for example.

Here's a query that displays a list of dates in two formats, using a stored date format preference value:

SELECT tblWork.WorkDate, Format([workdate],utv("DateFormat")) AS YYYYMMDDFormatted
FROM tblWork;

To support it, we added a new row to USysValueList:

I'm starting now to develop a start up form in which we'll be able to save one or more user specified values for properties that the database will then call up as needed to personalize the database.

Fortune Cookie Blues

Family, Good Friends, Old Friends, New Friends . The cookie knows. All you have to do is find the right cookie. I think maybe we've got it this time. Thanks, YoYo. It was a very good Thanksgiving, all in all.

Sunday, November 8, 2009

Access on SharePoint

Are We There Yet?

Actually, I think so. I really do. We still have to unload the car and carry a few bags and boxes into the house, but we're certainly sitting in the driveway.

I've done up a small demo with MS Access 2010 (beta) running on the SharePoint 2010 (beta) and shown it to a few folks. It runs on my employer's site, so I can't publish the URL publically, unfortunately. Still waiting for a few "spare minutes" to float by so I can set up my own SharePoint site. However, from first responses, I'd say this is the real thing.

What Have You Done for Me Lately?

Here's a scenario where I think this new Access/SharePoint technology makes great sense (and dollars, if you'll pardon the pun). Consider a centralized sales organization which operates a Home Office in a large West Coast City. They have a few local agents in several other states (Michigan, Florida, Tennessee). Each agent is exclusively assigned to follow up on leads that originate in his or her territory. This organization obtains leads via a very sophisticated Affinity Program direct mail program. The Access database (with a SQL Server backend) which manages this program sits on a workstation PC in their Home Office. All potential customers, mailings, leads, assigned agents, and sales are tracked "cradle to grave" in this database.

One factor has always been a challenge for them: lead distribution and reporting is mostly manual.

Someone in the Home Office has to rubber-band a stack of mailed-in lead cards and stuff them in an envelope to resend to the assigned agent. Sometimes they photocopy and email them as PDFs. Getting results back from Agents? Well, yes, they are good about reporting results, especially sales, but leads can, and do, end up in a local agent's hands for quite some time before they are eventually disposed of.

Feedback from agents is hardest to manage. Management wants to know what's happening with all those "In Process" leads.

Sometimes a Great Notion

So, tracking leads and lead status is where this new Access/SharePoint capability comes in. Here's how I picture it:

The lead card comes in and the Office Manager updates that mailing record to indicate a response was received. Then, he assigns that lead to the agent nearest to the customer's address by selecting from a drop down list of agents. All of this happens now, by the way. The next step is where Access on SharePoint can really improve their process. Instead of getting up to photocopy the lead card, or sticking it on a pile to mail to the agent at the end of the day (or tomorrow if she gets too busy), the Office Manager pushes a button on the screen that says "Post to Lead Tracker". Access uploads that new lead record, along with any others that have been added recently, to the SharePoint site and sends an email to the assigned agent to notify them a new lead is ready.

The agent gets the email, goes to the URL for his or her leads and sees a list of "In Process" leads in his lead bank, with the new one sitting at the top. A good agent won't even be waiting for that email--he'll be checking his Lead Bank a couple of times a day anyway.

And when the agent disposes of leads from her lead bank, all she needs to do is pop open her Lead Bank form on the SharePoint site and select the new status from a drop-down list. If it's a sale, of course, the paper work now has to be completed, the same as it is done now. The main difference, though, is that the Office Manager back in the Home Office knows, by opening her desktop Access FE, that the lead has been disposed of, and she can set a flag to watch for the paperwork from the agent if necessary. (There is one unfortunate consequence for the procrastinators among us: "It's in the mail" is no longer a valid excuse.)

No more snail mail. Not no more photocopies. And everyone knows what is happening at both ends of the process in real time.

Wanna Buy a Good Buggy Whip? Cheap?

Access on SharePoint just might help to make the US Postal Service even less relevant than it already is. There's still email though.


Sunday, October 11, 2009

And Now For Something (Almost) Completely Different

I have a guest blogger today. Ben Clothier has agreed to let me post this article he recently wrote. Ben is an MS Access MVP as well as being a some-time colleague and full-time friend.

You'll find more of his excellent posts at Utter Access and at Access Word Forums.

Ben writes on the use of Parameterized Queries to achieve the results I've recently described with other techniques involving dynamic SQL. I'll leave it to you to compare the two approaches and figure out how best to adapt each to your needs. I'm aware of advantages in both approaches, as well as limitations.



We have an employee table from which we want to retrieve a personnel record derived from a given Primary key. Access provides us with a very convenient way of parameterizing the query by supporting references to controls on forms such as this:

FROM tblEmployees
WHERE empID = Forms!frmEmployees!empID;

This works out very well when the query is intended to live and die with this specific form and this approach is very commonly used in defining a recordsource for a report.

But references are only good when there's actually something to reference. Thus, the above query will definitely fail should the form frmEmployees not be open at time the query is called. We also run into problems of reusability. Surely the need to reference a employee record based on the primary key can be used in more than one places. Should we then write each query for each possible form reference? That would be a maintenance problem. Thus we may want to consider using parameters in more general forms.

Consider this syntax, found in a Parameterized Query:

FROM tblEmployees
WHERE empID = [lempID];

Now the query is disassociated from any references to any other objects and can run on its own two feet. Of course when you run it, Access will, as usual, present a little parameter input dialog. Therefore, sometimes people like to do something like this:

PARAMETERS [Enter an Employee ID];

This will work okay for simple use, but sometime we don't want to enter the paramter via a simple dialog; perhaps we already have a combobox on a form and want to make use of it. No problem; we can use VBA to fill in this for us.
With CurrentDb.QueryDefs("qryEmployee")
.Parameters("lempID") = Me.MyComboBox
Set frm.Recordset = .OpenRecordset(dbOpenDynaset)
End With

With this code snippet we can make use of a complete pop up form or subform that is filtered by the selection made in MyCombobox.

We certainly can make use of the techniques for action queries:

DELETE FROM tblEmployees
WHERE empID = [lempID];

With CurrentDb.QueryDefs("qdlEmployee")
.Parameters("lempID") = Me.MyCombobox
.Execute dbFailOnError
End With

As can be seen, using parameters is much more straightforward with any action queries and provides an convenient means for reusing the same query for several different parameters that may be chosen by user at runtime.

String Sanitizing
One more benefit is that there's no need to perform any string sanitizing as is required when writing a dynamic query. Suppose, for example, an user entered this value to pass into the query as a parameter:
Mike's and Al's "Good" Cars
In a dynamic query, we would be facing lot of headache in ensuring that the quotes are correctly delimited and escaped, with one of several variants:

'Mike''s and Al''s "Good Cars'
"Mike's And Al's ""Good Cars"""
Chr(34) & "Mike's And Al's " & Chr(34) & "Good" & Chr(34) & " Cars"

But in the code based on a parameterized query:

Parameters("sParam") = Me.MyTextbox

it's always parsed correctly with no help from us!

One more technique. This isn't strictly related to the parameter query but pertains to maintainability. I like to put parameter queries in a function or property procedure....
Something like this:
Property GetPersonnelRecord(EmpID As Long) As DAO.Recordset
Static q As DAO.QueryDef
If q Is Nothing Then
Set q = CurrentDb.QueryDefs("qryEmployees")
End If
With q
.Parameters("EmpID") = EmpID
Set GetPersonnelRecord = .OpenRecordset (dbOpenSnapshot)
End With
End Property

The reason for doing it this way is to provide an interface for VBA into individual queries and make the code maintainable especially in regards to queries that are reused in many different places.
But here's a more important reason. Suppose that after years of doing the above, we had to change the requirement and partition the employee table between active employees and past employees due to data storage restrictions and performance reasons. With a consistent interface as given above, it's a snap to add a 2nd parameter to identify whether the employee is active or not, update the property procedure to enter the 2nd parameter. Hit the compile and the compiler will find missing arguments in all places, which is far more accurate than using Find & Replace to track down all uses of the same queries all over in the code base.

Thus, parameter queries have lot to offer in terms of code maintainability and documentation in exchange for a little investment in some extra VBA coding that otherwise wouldn't be necessary with control references or dynamic queries.

Of course, it goes without saying that they do have their limitations. Many people working on various RDBMS have had tripped over this innocuous looking parameter (adapted into Jet SQL):

FROM aTable
WHERE SomeData IN([MyParam]);

The query will compile and save OK without any errors and you can even run it. If the first parameter you put in was just '''Apple''' (of which does exist in the table), it'd work as expected. Great. Now try this
'''Apple'', ''Pear'', ''Grapes'''
and you get nothing in return. What happened? Well, IN() actually does not mean 'parse this string at the runtime', but rather a compile shortcut that translates into X = A OR X = B OR X = C and thus must be hard-coded.
In Jet SQL, a dynamic query is probably most appropriate for this type of parameter.

Optional Parameters
Another situation where it becomes an problem is when we desire optional parameters. Some developers may try to work around it like this:

WHERE SomeData = [MyParam] OR SomeData IS NULL

which unfortunately negates any index optimization Jet can perform. Here, dynamic queries makes more sense as it's cheaper to create a new execution plan and use index than to suffer the performance penalty.

Thursday, August 6, 2009

The Cheese Stands Alone

In a previous blog I talked about the usefulness of dynamically parameterized queries in generating small recordsets for forms and reports, a technique that is particularly good for client-server applications with potentially large recordsets in the back end.

One of the additional benefits I found in working with this technique is that I can construct forms that work equally well as subforms and as stand-alone forms, even though their recordsource is a parameterized query that would traditionally have had to be written like this:

Sunday, July 26, 2009

Data Macros. How Hard Could It Be?

The last week has been a lot of fun. Like many of the people who have access to the Technical Preview (TP) release for Access 2010, I lost a few hours of sleep the first two or three days of the week.

However, over the last couple of days, it's begun to feel more like "business as usual". And I think that's a good thing. A very good thing.

Sunday, July 12, 2009

Blue Skies Smiling at Me

...Nothing but blue skies from now on. Would that it were true.

Another blue weekend and not much Access work got done over here at GPG Manor.

It's funny how languages work (I'm thinking of English at the moment, of course, but every language has it's own quirks.)

For example, I was listening to Willie Nelson a bit ago

Sunday, July 5, 2009

"Inappropriate Language" in MS Access 2007 Templates

Okay, so I've been trying to figure out how to submit additional Access 2007 templates to the MS Access Template Site. For some time now, the MS Access team has been encouraging MVP's to participate in the template program.

Well, I tried, I really tried. A little over a year ago, I submitted a dead simple template that consists of the table schema and two forms. That one was accepted.

Saturday, June 27, 2009

Where in the World is Common Parameter?

One thing I really like about the dynamic SQL approach to creating saved queries is that it allows me to re-use some base queries without having to parameterize them.

With a forms reference, the query is tightly linked to the form itself. That means one query for each form where you want to use that same syntax--with variable input for the form.

Sunday, June 21, 2009

Dude, Where's My Clause?

Lately I've been experimenting a lot with writing dynamic SQL for queries. Even though I'm starting to see some limitations, I'm pretty sure I like this approach a good deal . Today I'll just sketch out some basics and come back in future blogs to talk about the details, including the places where I think it makes a lot of sense--and where it looks like it might not ever really work.

Sunday, June 7, 2009

SQL Saturday

Oh Come All Ye Faithful.

I spent the day yesterday (June 6th) at the Portland SQLSaturday event.

Way cool, but, of course, I ran into the predictible "Access? I'm sorry" bias. (More on that later.)

I sat through sessions on:

Friday, May 8, 2009

I Am Still Around

I need a better calendar. This one is telling me it's been 3 months since I blogged. There is only one good explanation for that: the darn Outlook calendar has a bug in it. Gotta get my Outlook guy to look at it.

Truthfully, though,

Saturday, February 7, 2009

One to Many

Some of my friends are starting to think I'm maybe a bit crazy; some of them maybe think I’m a lot crazy. I can’t blame ‘em; I do, too, at least sometimes. One of the symptoms of my unsettled disposition is the kind of music I listen to.

My three favorite male singers are Willie Nelson, Oscar D'Leon, and Frank Sinatra.

My three favorite female singers are