Port Access to SQL Server

To SQL ServerAccessDevelopers are faced with a lot of difficulties, but the most difficult problem is the dynamic SQL.In access, it is quite common to specify the row of the control as a string. For example, the form contains many selectors (salesgroup, state/province/canton, beginning date, and ending date). Access developers generally check these controls to see which ones are null, then write SQL statements to delete these null values.

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

All processing is completed on the client, rather than on the server.

Parse these controls and process sqlq statementsCodeIt seems to be lengthy.

This method can easily cause injection attacks ).

Access developers eager to expand to SQL Server must solve two problems:

1. Identify all records and row data sources that are not based on name queries.

2. Use name query to replace all queries.

 

For dynamic query processing, you can use the following code:

Select *
From sometables
Where columnofinterest = forms ("myform"). controlofinterest
And column2ofinterest = forms ("myform"). control2ofinterest

 

For simplicity, we assume that the access form contains only two controls. The access method parses the control value in the code, and then processes each dynamic SQL query using a non-zero value.

This is a method without a wizard. What you need is a storageProgramThe storage program is parameterized to receive all data from controls on the form. For example, if the given two controls are triggered by the stored program's own data, if the value is null, the stored program will be intelligently executed.

Here is a simple technique for implementing this process:

Select *
From sometables
Where columnofinterest = 12345 or columnofinterest is null


For access, you call the storage program and then pass the values of all related controls, regardless of whether these values are null:


Exec mysproc value1, value2,... valuen

 

Then, the storage program processes these null values as shown above. As you can imagine, the storage program selects the value provided by the user, passes a value (such as 12345), returns to a row, and then the or statement is called. The value of all rows is returned because the parameter is null.

This article describes how to use this technique.Example of the northwind database. In this example, all orders are listed before a specific date is passed:

Create procedure DBO. orderslistsproc1
(@ Orderdatedatetime)
As select DBO. Orders .*
From DBO. Orders
Where (orderdate <convert (datetime, '2017-08-01 00:00:00 ', 1996 ))
Or (@ orderdate is null)

 

Although the storage program cannot always replace dynamic SQL in this way, this method can usually improve the performance of the program.

 

Author: Arthur Fuller has been engaged in database application development for more than 20 years. He is proficient in access ADPs, Microsoft SQL 2000, MySQL, And. net.