Use VBA to dynamically modify the SQL string passed for query in access

Source: Internet
Author: User
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.