A question was asked: A form was created in access, a parameter was input in the text box in the form, and a result set was returned to generate a report during the Stored Procedure passed to sqlserver. it is known that the stored procedure of SQL Execution can be queried through access transfer. Q: How to pass the parameters entered in the form into dynamic query?
I used to work in a software company and never used access and sqlserver in such a strange way. however, it is found that there are still a lot of applications that seem uncomfortable in the enterprise. no idea, so I went to csdn to ask.
The following code is provided by the access user wangtiecheng on csdn:
'Change the SQL statement passed in the query
Public sub changesql (strpassthroughqueryname as string, strpassthroughsql as string)
'1. Initialization
Dim cat as new ADOX. Catalog
Dim pro as ADOX. Procedure
Dim CMM as new ADODB. Command
'2. determine the validity of input parameters
If strpassthroughsql = "" or Len (strpassthroughsql) = 0 Then exit sub
Set cat. activeconnection = currentproject. Connection 'use local connection currentproject. Connection
Set pro = cat. Procedures (strpassthroughqueryname)
Set CMM = pro. Command
CMM. commandtext = strpassthroughsql
'Cmm. commandtimeout = 300
Set pro. Command = CMM
3. passthrough string modified successfully
Set cat = nothing
Set pro = nothing
Set CMM = nothing
End sub
Create a new module in access (the module name cannot be the same as the sub name.
VBA editor window, tool-reference, Microsoft ADO Ext. 2.8 for DLL and security check.
Then, you can call the changesql subfunction in the code before executing the stored procedure to modify the SQL statement that passes the query.