The Access developers of SQL Server face a lot of difficulties, but the most tricky problem is to process dynamic SQL. In Access, the row of the control is specified as a string is quite common. For example, the form contains a lot of selectors (Salesgroup, State / Province / Canton, Beginning Date, and Ending Date, etc.), Access developers generally check these controls to see which is null, then write SQL statements to delete these NULL value.
Connecting to SQL Server has the following shortcomings by this method:
All processes are completed in the client instead of completing in the server. Resolving these controls and handling the code for the SQLQ statement looks dragging lengthy. This method is prone to the SQL mixing attack (Injection Attacks). The Access developers who are eager to extend to SQL Server must solve two aspects:
1. Identify all data sources of the records and rows of the name query.
2. Use the name query to replace all queries.
For dynamically urging queries, you can process the code shown below:
Select * from sometableswhere columnofinterest = form ("myform"). ControlOfinterest = Forms ("MyForm"). Control2Ofinterest
For the sake of simplicity, we assume that only two controls are included on the Access form. The Access method will parse the value of the control in the code, and then process each dynamic SQL query by using a non-zero value.
This is a way without a wizard. What you need is a storage program that is parameterized to receive all data from the control. For example, suppose the two controls given above, the storage program is triggered by its own data, and if the value is null, the storage program will "intelligently" execute.
Here is a simple trick to achieve this process:
Select * from sometableswhere columnofinterest = 12345 or columnofinterest is null
For Access, you call the storage program, then pass the value of all related controls, regardless of whether these values are null: exec nameproc value1, value2 ,.. .VALUEN then, the storage program processes these NULL values as shown above. . It can be imagined that the store program selects the value provided by the user and then transmits a value (such as 12345) and returns to a row, then the OR statement is called. Because the parameter is NULL, the value of all rows is returned. Here is the Northwind database exempted example of this technique using the process. In this example, we list all the orders before the specific date is passed: create procedure dbo.orderslistsProc1 (@orderdatedatetime) as select dbo.orders. * From dbo.orderswhere (ORDERDATE Author: Arthur Fuller in database application development has reached 20 years old, he was proficient Access ADPs, Microsoft SQL 2000, MySQL, and .NET.
