porting access to SQL Server

Access developers to SQL Server face many difficulties, but the most intractable problem is the processing of dynamic SQL. In Access, it is fairly common to designate the row of a control as a string. For example, a form contains many selectors (salesgroup,state/province/canton,beginning date and ending date, and so on) that the access developer typically checks to see which are null. Then write the SQL statements to remove these null values.

Connecting to SQL Server in this way has the following disadvantages:

All processing is done on the client, not in the server.

Parsing the controls and processing the code for the SQLQ statement is lengthy.

This approach can easily lead to SQL mixing attacks (injection attacks).

Access developers who are eager to expand to SQL Server must address two aspects of the problem:

1. Identify the data source for all records and rows that are not based on name queries.

2. Use name queries to replace all queries.

For dynamic processing of queries, you can handle the code as follows:

SELECT *

From Sometables

WHERE columnofinterest = Forms ("MyForm"). Controlofinterest

and column2ofinterest = Forms ("MyForm"). Control2ofinterest

For simplicity's sake, let's assume that an Access form contains only two controls. The access method resolves the value of the control in code, and then processes each dynamic SQL query by using a method other than 0 values.

This is a way of not having a wizard. All you need is a stored program that is parameterized to receive all the data from the controls on the form. For example, assuming the two controls given above, the stored program is triggered by its own data, and if the value is null, the stored program is "intelligently" executed.

Here is a simple technique for implementing this process:

SELECT *

From Sometables

WHERE columnofinterest = 12345 OR columnofinterest is NULL