Constructing a SQL Parameter Add-in |
  
|
Constructing a SQL Parameter Add-in |
|
Russell Sinclair Gold Collection
In this article, Russell Sinclair brings a missing feature to Access Data Project development and shows how you can create your own builder add-ins.
One of the things I really like about Microsoft products is that almost all of them are designed to be extensible. If you find a feature that's missing from a Microsoft application, there's often a way in which you can implement that feature yourself.
Lately, most of the work I've been doing with Access has involved using ADPs with a SQL Server back end. The application my company is currently working with is a large interface database (more than 200 form and report objects) with an equally large back-end database. The entire application was migrated from an MDB with an MDB back end to an ADP with a SQL Server back end over a period of six months. When you work this closely with ADPs, you quickly learn that there are a lot of features missing from Access for these types of applications.
When I develop applications based on SQL Server back ends, I try to make as much use of stored procedures as I can. If it weren't for the idiosyncrasies surrounding how Access handles binding of forms, I'd use stored procedures at all times (trust me, Access doesn't handle binding stored procedures correctly). However, I do use stored procedures enough, especially in reports, that I really needed a tool that would allow me to set the parameters for the record source of the object with as little trouble as possible.
Analyzing the problem
Reports and forms in ADPs have a property called InputParameters that defines the parameters and their values when a stored procedure, user-defined function, or parameterized SQL statement is used as the RecordSource for the form. When the RecordSource is set to a stored procedure or user-defined function, the syntax for setting this property is:
[param type = value [, param2 type = value …]]
So, if you have three parameters @intOrderID, @nvcCustomer, and @bitDiscount, your InputParameters property should look something like this:
@intOrderID int = 12,
@nvcCustomer nvarchar(100) LIKE 'A%',
@bitDiscount bit = 0
The syntax for the InputParameters property is slightly different if you're using the parameterized SQL statement. As an example, assume that you're using a parameterized SQL statement as the RecordSource such as this:
SELECT *
FROM OrderDetails
WHERE OrderID = ? AND ProductID = ?
In this example, the question marks are used as placeholders for parameters. To use this statement, you need only provide a value for each parameter separated by commas, as this setting for the InputParameters would do:
12, 16
What would be most convenient for me would be to have a builder on the InputParameters property that brought up a dialog that would figure out what the parameters are for the RecordSource and automatically allow me to set their values. Visual Studio has this feature; why shouldn't Access? Of course, I could also build it myself by creating a Property Wizard add-in.
Property Wizard
Creating a Property Wizard add-in for Access is actually quite simple: It involves just four steps. The first step is to create a new, blank MDB file for the add-in and then change the extension on the file to MDA. Renaming the extension isn't strictly required, but it does make it easier to identify the file as an Access add-in in Explorer and it makes it easier to select only MDA files when you install your add-in.
The second step is to create or import a USysRegInfo table into your database and add the appropriate entries to the table. Whenever I create an add-in, I start by importing this table from another add-in database rather than rebuilding it each time. I won't spend any time rehashing a subject that really needs a few pages to explain. You can find documentation on how to create this table and the data it should contain in the Microsoft Knowledge Base in Article ID 201735. Pay particular attention to the section "Procedures to Create USysRegInfo Records for Control Wizards, Property Wizards, or OLE Custom Control Wizards."
The entries I needed for my application are shown in Figure 1. The first field has been cut off to save space in the figure (its full value is HKEY_CURRENT_ACCESS_PROFILE\Wizards\Property Wizards\InputParameters\InputParameterBuilder in all rows).
Figure 1
The third step is to create a method with the correct signature for the add-in to call. In my case, the "Function" value in the USysRegInfo table specifies that this function will be called "ParameterBuilder." This function must be Public in scope. I wrote the function to accept three string arguments and return a string. The first argument will receive the name of the selected form or report. The second argument will contain the name of the selected control. In this case, this will be "Form" or "Report," as the properties are top-level properties for those objects and don't apply to controls. The final argument is the current value of the property. If the InputParameters property is set, the value will be passed to my add-in here. The ParameterBuilder procedure can be found in basMain in the sample database.
The final step in the add-in development process is to develop the code that your add-in executes. This code should be designed to determine what the parameters are for the stored procedure, what values are currently set, and provide an interface for the users to enter their own values for the parameters.
Coding the add-in
The first step in handling the code for the add-in was to add code to the Property Wizard procedure. This procedure determines whether the object we're working with is a form or report, and then retrieves the qualified RecordSource from that item. It then passes the code through the three main functions in the add-in that handle what is passed back to the user: PromptParameters, FillParameters, and CurrentParameterValue.
FillParameters is the real workhorse in this application. It's the procedure that determines what the parameters are and how to parse the parameter statement. There are a number of ways that you can determine what parameters a stored procedure has. The ADOX and SQLDMO libraries both provide interfaces for enumerating parameters. However, I didn't want to add any unnecessary references to my project. ADO command objects allow you to directly retrieve the parameters in a procedure by calling the Parameters.Refresh method. Although this is an option, I like to use the OpenSchema method of the connection object. The main reason I like to do this is that the OpenSchema method always returns a recordset object, an object I'm more familiar with than the Parameter objects created by the Refresh method.
The OpenSchema method is a method on the ADO connection object that allows you to analyze the design of your database. It takes three parameters that allow you to specify what data to return and what filters to use. The first parameter is an enumeration specifying what data to return. In my case, I wanted a list of parameters so I used adSchemaProcedureParameters. The second argument is an array of arguments for the schema. The number and type of arguments changes, depending on what's being requested (you can find complete documentation in the OpenSchema topic in Help). I simply passed the necessary parameters to limit the procedure to the source object of the form and owner to the RecordSourceQualifier (if set) along with the unused parameters. The resulting recordset is then appended into a table called tblParameters. I manipulate the data in that table to get the proper declarations for the parameter datatype.
Once the recordset has been populated, frmParameters opens with tblParameters as the recordsource for the subform, as shown in Figure 2.
Figure 2
As you can see, on this form a drop-down list is conveniently provided to set the parameter to the default value (if there is one) or NULL, just like the standard parameter prompt in Access. You can also type your parameter value in the Value column. Once you've entered your parameters, clicking OK triggers the rest of the code in PromptParameters to create the text string for the InputParameters property. This value is passed back by the procedures to the initial function and Access handles, setting the property to this string.
When I was developing the solution, I contemplated taking some time to properly parse the values that you might enter for parameters. This would involve putting quotes around text, properly formatting dates, and handling any type of value appropriately. I decided not to do this only because I regularly pass form values for parameters (for example, "[Forms]![frmMyForm]![txtMyCompany]"). Any rules I would have tried to create might have interfered with this ability, so I decided to leave that feature out. You wouldn't like the add-in much if it kept changing the values you entered to match what it thought was right.
Something's missing
If you've read a few of my articles, you may have noticed that I like to leave little challenges for you to complete on your own. This add-in is no different.
This add-in doesn't handle explicit SQL statements. This is because I couldn't determine, in a reasonable amount of time, a way to enumerate the parameters that a user defines when a parameterized SQL statement is used. There may be a way to handle this, but the methods that I expected to handle it (mainly, Parameters.Refresh) didn't act as I hoped. If you figure out an easy way to do this, I'd love to hear it.
You may also notice that there's a convenience missing from the parameter value setting. I'd hoped that I might be able to use the Access Expression Builder to allow users to build an expression for the value. Unfortunately, Access doesn't provide a way to access the Expression Builder from code and retrieve the return value. If you really want to implement this functionality, I suggest that you check out Michael Kaplan's site at www.trigeminal.com/lang/1033/utility.asp. On that site, he has a DLL that you can download that allows you to access the Expression Builder. It's a nice tool that might make your life easier for other projects as well.
I always like to say that one of the most valuable character traits of a good developer is laziness. The easier I can make my or a user's job, the better. I hope this add-in saves you the time it has saved me in my work with Access Data Projects.
One final note that might interest you: When I was working on the code for this article, I needed to check the Access Help for some information on the InputParameters property. I was a bit shocked to find that the Help references a builder for this property. Apparently, I'm not the first person to think of this. It seems that Microsoft planned or implemented this feature in a beta version of Access but it never made it to primetime. Microsoft, please add this to your list of feature requests for Access 2006.
Sidebar: Installing Add-ins
One of the problems you might run into in add-in development is how Access treats add-ins when you install them. Installing an add-in is simple using the Add-In Manager. The problem is that Access will copy the file to another location on your computer in your personal profile directory. If you aren't aware of this fact, it can cause you some problems in your development.
The original file you choose when you click Add New... in the Add-In Manager isn't used when you call the add-in. Instead, the copy of the add-in database is used. If you make changes to your original add-in, you'll need to re-add it using the Add-In Manager (you don't need to remove the old one first, though).
Your download file is called 503SINCLAIR.ZIP in the file SA2005-03down.zip
This is found in the Gold Collection on this page
Other Pages That You Might Want To Read
Access Interfaces: Handling Price Ranges (parameter query samples)
Access Answers: Client/Server Issues
Client/Server Development with Access
Migrating Access (and Outlook Data) to SQL Server