In this article we describe the general approach which MUST+Web uses when converting an Access Form that uses screen parameters to a .net web form.

To explain how this is achieved, we look at a typical method that Access developers use when constructing applications and see how to build a strategy to convert this to .net.

Below we have an unbound form into which a user would type a product code, the user then presses the button to display the results in a second form which is bound to data.

This design approach also works very well when we have migrated an Access application to SQL Server; where we do not want users performing a Ctrl-F on a form to locate a record in a table containing thousands of records, because this causes serious performance problems.

Below we see the second form which displays the selected data.

Below we see that the recordsource of the second form has the ProductID field tied to be filtered by the value the user entered in the other form. This method is often called Query By Form ( see the following article for more information on this technique (link to Query By Form)

Top of Page

The first step to converting this application to .net is to upsize all the data to SQL Server.

Once this is completed we need to convert the SQL used by the Products form to a SQL Server View, that we can be linked into .net.

The challenge is how to handle the parameter that contains the reference to ProductID.

Because an Access application can have a fairly large number of these parameters, it makes sense to create a table in SQL Server which can hold the values entered by the user, and then just like in Access we will filter the View (query) for the users choices

Below we can see a list of all the parameters in the application identified by MUST.

Next MUST creates a table in SQL Server to hold the parameters.

Because we want this to work in a multi-user application, MUST has added a special field called Parameters_UserNameFilter. This field uses a SQL Server function that can pick-up the users identity.

And below we see in the SQL Server table how the users identity is written to the table when they enter data.

When we use this table, we want to make sure that we are only using the active users record, so MUST constructs a view which filters the table of many rows for many users, to show just a single row for the current user.

MUST creates a view as shown below with a Check option to prevent any injection by a user of data into another users row.

CREATE VIEW [Reporting].[vw_tblParameters] AS SELECT * FROM [Reporting].[tblParameters_M] WHERE [Parameters_UsernameFilter] =SUSER_NAME() WITH CHECK OPTION GO

Both the parameter table and the parameter view are created on a special schema called Reporting; the reason for this is we may want to have read-only users with permissions to have read-write on the table of parameters; this is a good solid approach to building in security. The synonym that MUST creates means that we can refer to the view without qualifying it with the name of the schema; this simplifies our SQL.

CREATE SYNONYM [dbo].[vw_tblParameters] FOR [Reporting].[vw_tblParameters] GO

If you execute SELECT * FROM vw_tblParameters then this will return a single row for the active user.

Below we see the SQL that MUST has converted to SQL Server; this can now be used to display the recordsource that the Access form was using.

CREATE VIEW [dbo].[vw_Form_Products] AS SELECT [Products].* FROM vw_tblParameters p CROSS JOIN [Products] WHERE (((Products.[ProductID])=p.[_param_txtProductId])) WITH CHECK OPTION GO

The CROSS JOIN syntax says project each row in the vw_tblParameters table/view against the [Products] table. And because we are using a view which filters by the user we only ever get one row.

This means that our equivalent procedure to the Access form would be:-

• Write the value ProductID into the _param_txtProductId field through the view vw_tblParameters.
• Open the view vw_Form_Products to see the result.

At this point if you were using an Access front-end to SQL Server you could change the unbound form to be bound to our parameters view and continue using Access.

A word of warning; we are not suggesting that when continuing to use an Access front-end it is appropriate to convert all recordsources to views without checking to see that there are no design implications for your Access forms. We are converting all the recordsources to views in this example because we are going to move everything to .net.

Top of Page

What we show below is the results of MUST+Web converting the Access form to a .net web form.

Notice how the original Access screen control has been added to the filter bar to allow a user to type in an appropriate value and then refresh the page.

Initially when we open the web form without having supplied a value for the product id, we see that no records are returned.

Then once we enter a value for the ProductID we receive the matching data record.

Rather than have the above search bar that allows for the entry of the required form parameter, we could easily remove this content area to another form. However we have found that because you need more searching options in webforms having a content area that can display both the required choices and other optional selections for filtering data is a useful application feature.

MUST+Web uses a technique where the optional parameters operate independently, so you can fill any combination of the additional optional parameters.

Top of Page

The only remaining task is to ensure that you have a mechanism to populate a row in the parameters table for a user, this can easily be achieved in several ways, we could pre-load the table for all users, or we could add some code either in a master page when the page loads or during a logon process to create the record.

Read the following article which explains how to handle this situation.

Working with vw_tblParameters

Top of Page